#basededatosenexcel #basededatosenaccess #basededatosenexcelyaccess #excelyaccess #excellento #optimizarexcel #optimizarexcelmasrapido
Hola emprendedores y entusiastas productivos. En este artículo vamos a realizar una base de datos lo más liviana, ágil y confiable en Access con Excel y VBA. Los libros realizados en Excel se van volviendo lentos a medida que aumentan los datos y dependiendo del archivo, pueden volverse casi inoperables. Se debe enfatizar el hecho de que Excel no es una herramienta para almacenar bases de datos grandes, por ello la necesidad de utilizar Access (gestor de datos).
Entonces, lo que haremos será desarrollar un formulario en VBA y Excel, para hacer cálculos respectivos por este medio y almacenar datos en Access, de esta forma tendremos una base de datos optimizada, sin gastar recursos en Excel, y administrando los datos por este medio.
Bien, vamos detalladamente con el siguiente procedimiento:
1. Crear los encabezados de la tabla en Excel y Access
Se debe establecer todos los campos (encabezados) que formarán parte de la tabla, y se lo construye en Excel y Access.
En Excel:
Como se observa, se coloca todos los encabezados de la tabla en Excel, con un formato personalizado.
Después de ello, vamos a elaborar una tabla con los mismos encabezados en Access.
En Access:
-Abrir Access.
-Click en Base de datos del escritorio en Blanco.
-Establecer el nombre de la base de datos (ejemplo: Base de datos.accdb).
-Elegir la ruta para guardar la base de datos.
-Click en Crear.
-Click en Tabla 1.
-Click en la pestaña Campos (luego Ver y Click en Vista Diseño)
-Asignar un nombre de tabla.
- Agregar los nombres de los campos con sus respectivos tipos de datos.
Nota importante: Si los campos ingresados mediante el formulario en Excel no corresponden con la propiedad de tipo de datos generado en Access puede marcar error. Y, después de ingresado los datos en la tabla en Access, las propiedades no se pueden modificar, por lo tanto, poner especial atención a este detalle. Si no está seguro a que hacen referencia estas propiedades (ver Tipos de datos en Access).
Nota importante II: Los encabezados de la tabla no deben tener espacios en blanco!!.
Como puedes observar hay varios campos que presentan espacios en blanco en los encabezados, no se debe tener estos campos con espacios puesto que puede generar errores al actualizar los datos cuando se utiliza código VBA. Ejemplo:
* Precio Compra (mal).
* PrecioCompra (bien).
-Como se ha podido observar también, existe un nombre de campo Id, el cual se genera de forma automática por Access y con el tipo de dato en Autonumeración, esto, para generar datos de forma ordenada, por lo que es recomendable no quitarlo o editarlo, sin embargo, si es un campo que no va a hacer falta, se puede remover.
2. Elaboración de Rangos (opcional)
Muchas veces los formularios requieren de listas desplegables para asignar información acertada, si este es el caso, se puede generar una lista con rangos dinámicos (ver rangos dinámicos integrados a formularios).
3. Construcción de formulario en VBA
Vamos ahora con la elaboración de un formulario para asignar los elementos de nuestra base de datos, como sigue a continuación:
Generar estos formularios es bastante sencillo. Basta con seguir los pasos a continuación:
- Abrir Excel y abrir el entorno VBA que se encuentra en la pestaña desarrollador o Alt + F11, si no dispone esta pestaña hacer lo siguiente: *Click en archivo, opciones de Excel. * Click en personalizar cinta de opciones. Desplegar la lista y click en todas las pestañas. Buscar la pestaña Desarrollador. * Click en agregar y aceptar.
- Una vez dentro del entorno VBA.
-Click en la pestaña Insertar UserForm.
- Luego, se debe tener habilitado el cuadro de herramientas y la ventana propiedades. Para ello, click en la pestaña ver, y click en cuadro de herramientas y ventana de propiedades.
Con el cuadro de herramientas habilitado, el cual es bastante intuitivo, se puede diseñar un formulario como el observado, cambiando los nombres de los casilleros con el cuadro de propiedades en la propiedad Name; la propiedad caption es para definir de que se trata el formulario.
4. Habilitar Referencias de VBA
En el entorno VBA, dirigirse a la pestaña herramientas, click en referencias. Buscar y habilitar las que aparecen a continuación:
Si no se habilita estas referencias, los códigos VBA para almacenar datos en Access simplemente no funcionarán.
5. Codificación VBA
Bien, es hora de establecer el código que debería contener el formulario para agregar nuevos datos a la base de datos en Access. Primero, vamos a generar controladores de error para los casilleros del formulario como sigue:
Private Sub preciocompra_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Modificar el punto por la coma
If InStr(preciocompra, ".") > 0 Then
nuevo = Replace(preciocompra.Value, ".", ",")
preciocompra.Value = nuevo
End If
End Sub
Private Sub preciocompra_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Números con decimales
If InStr("0123456789.", Chr(KeyAscii)) = 0 Then
If KeyAscii <> 8 Then KeyAscii = 0
End If
End Sub
Private Sub precioventa_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Modificar el punto por la coma
If InStr(precioventa, ".") > 0 Then
nuevo = Replace(precioventa.Value, ".", ",")
precioventa.Value = nuevo
End If
End Sub
Private Sub precioventa_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Números con decimales
If InStr("0123456789.", Chr(KeyAscii)) = 0 Then
If KeyAscii <> 8 Then KeyAscii = 0
End If
End Sub
Private Sub producto_Change()
'Sólo permitir mayúsculas
producto.Text = UCase(producto.Text)
producto.SelStart = Len(producto)
End Sub
Bien, con estos códigos adicionales, es hora de mostrar el código para añadir datos del formulario a Access. Este código ha sido obtenido directamente de la web: Exceleinfo, y el crédito es totalmente de ellos.
'Asignar variables
Dim Conn As ADODB.Connection
Dim MiConexion
Dim Rs As ADODB.Recordset
Dim MiBase As String
'Control error: campos vacíos
If codigo.Text = "" Or producto.Text = "" Or proveedor.Text = "" Or preciocompra.Text = "" Or precioventa.Text = "" Then
MsgBox "Rellenar todos los campos por favor.", vbCritical
codigo.SetFocus
Exit Sub
End If
'Nombre del archivo (base de datos) de access
MiBase = "Basededatos.accdb"
'La base de datos de Access se almacena en la misma ruta donde se encuentra el archivo de Excel
Set Conn = New ADODB.Connection
MiConexion = Application.ThisWorkbook.Path & Application.PathSeparator & MiBase
'Conexión con la herramienta ADO
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MiConexion
End With
'Almacenar datos en el nombre de la tabla de Access (ej:BDProductos)
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseServer
Rs.Open Source:="BDProductos", _
ActiveConnection:=Conn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'Cargar los datos del formulario a Tabla de Access (Fields:campo de access = campo de Excel)
With Rs
.AddNew
.Fields("Codigo") = UserForm1.codigo.Text
.Fields("Nombre") = UserForm1.producto.Text
.Fields("Precio Compra") = UserForm1.preciocompra.Value
.Fields("Precio Venta") = UserForm1.precioventa.Value
.Fields("Margen de Contribución") = CDbl(((UserForm1.precioventa.Value - UserForm1.preciocompra.Value) * 100) / UserForm1.preciocompra.Value)
.Fields("Proveedor") = UserForm1.proveedor.Text
End With
Rs.Update
'Cerrar la conexión
Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
MsgBox "Actualización de datos exitosa", vbInformation
Unload UserForm1
Bien, los datos marcados con color café son aquellos que deberían modificarse para cada usuario. A continuación se muestra la ejecución del programa para el formulario en cuestión:
Ahora, para una base de datos funcional es importante tener un formulario para buscar, modificar y eliminar registros de la base de datos almacenada en Access desde Excel. Para ello, vamos a realizar lo siguiente:
6. Formulario para buscar, modificar y eliminar registros
Ahora, vamos con la elaboración de otro formulario, como se muestra a continuación:
Como se observa, se requiere delcuadro de herramientas, un cuadro de texto (textbox) para la búsqueda, un cuadro de lista (listbox) y botones para la ejecución de las diferentes macros mostradas a continuación:
7. Codificación VBA
Los códigos siguientes también han sido desarrollados por parte de Sergio Alejandro Campos de ExceInfo, por lo tanto, crédito total para el arquitecto de este código. Aquí simplemente se detallará los campos que requieren editarse por parte del usuario. Bien, vamos a ello:
Código para Buscar:
Private Sub CommandButton1_Click()
'Definir variables
Dim Conn As ADODB.Connection
Dim MiConexion
Dim Rs As ADODB.Recordset
Dim MiBase As String
Dim Query As String
Dim i, j
'Añadir la base de datos
MiBase = "Basededatos.accdb"
Set Conn = New ADODB.Connection
MiConexion = Application.ThisWorkbook.Path & Application.PathSeparator & MiBase
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MiConexion
End With
'BDProductos: Nombre de la tabla de access
'Nombre: Nombre del campo a buscar
'Textbox1: Nombre del casillero en VBA donde se colocará el nombre a buscar
Query = "SELECT * FROM BDProductos WHERE Nombre LIKE '%" & Me.TextBox1.Value & "%'"
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseServer
Rs.Open Source:=Query, _
ActiveConnection:=Conn
'Validar si la consulta devuelve resultados
If Rs.EOF And Rs.BOF Then
'Borrar la conexión al Recordset
Rs.Close
Conn.Close
'Borrar la memoria
Set Rs = Nothing
Set Conn = Nothing
MsgBox "No hay resultados para la consulta", vbInformation, "EXCELeINFO"
Me.ListBox1.Clear
Exit Sub
End If
'Asignar número de columnas
With Me.ListBox1
.ColumnCount = Rs.Fields.Count
End With
'Recorrer el Recordset
Rs.MoveFirst
i = 1
With Me.ListBox1
.Clear
'Asignar los encabezados
.AddItem
'Añadir los campos (Igual a los campos requeridos)
For j = 0 To 6
.List(0, j) = Rs.Fields(j).Name
Next j
Do
.AddItem
.List(i, 0) = Rs![id]
.List(i, 1) = Rs![codigo]
.List(i, 2) = Rs![nombre]
.List(i, 3) = Rs![preciocompra]
.List(i, 4) = Rs![precioventa]
.List(i, 5) = Rs![MargendeContribución]
.List(i, 6) = Rs![proveedor]
i = i + 1
Rs.MoveNext
Loop Until Rs.EOF
End With
'Cerrar la conexión
Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
End Sub
Código para eliminar registro:
Private Sub CommandButton3_Click()
'Definir variables
Dim Conn As ADODB.Connection
Dim MiConexion
Dim Rs As ADODB.Recordset
Dim MiBase As String
Dim Query As String
Dim i, j
Dim Cuenta As Integer
Dim Numero As Integer
Dim ValorElegido As Integer
'Recorrer el listbox y detectar el item elegido
Cuenta = Me.ListBox1.ListCount
'Validamos que haya un elemento seleccionado
For i = 0 To Cuenta - 1
If Me.ListBox1.Selected(i) = True Then
Numero = Numero + 1
End If
Next i
If Numero = 0 Then MsgBox "Debes elegir un elemento", vbExclamation, "EXCELeINFO": Exit Sub
For j = 0 To Cuenta - 1
If Me.ListBox1.Selected(j) = True Then
If Me.ListBox1.ListIndex = 0 Then MsgBox "Encabezado!", vbCritical, "EXCELeINFO": Exit Sub
ValorElegido = Me.ListBox1.List(j)
MsgBox ("Valor eliminado de la base de datos"), vbInformation
End If
Next j
'Añadir la base de datos
MiBase = "Basededatos.accdb"
Set Conn = New ADODB.Connection
MiConexion = Application.ThisWorkbook.Path & Application.PathSeparator & MiBase
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MiConexion
End With
'Eliminar el dato de la tabla de access: BDProductos
Query = "DELETE * FROM BDProductos WHERE Id = " & ValorElegido
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseServer
Rs.Open Source:=Query, _
ActiveConnection:=Conn
'Cerrar la conexión
'Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
'Ejecutamos botón de Buscar
Call CommandButton1_Click
End Sub
Código para Modificar registro
Private Sub CommandButton4_Click()
'Asignar variables
Dim i, j, Cuenta, Numero
'Recorrer el listbox y detectar el item elegido
Cuenta = Me.ListBox1.ListCount
'Validamos que haya un elemento seleccionado
For i = 0 To Cuenta - 1
If Me.ListBox1.Selected(i) = True Then
Numero = Numero + 1
End If
Next i
If Numero = 0 Then MsgBox "Debes elegir un elemento", vbExclamation, "EXCELeINFO": Exit Sub
For j = 0 To Cuenta - 1
If Me.ListBox1.Selected(j) = True Then
If Me.ListBox1.ListIndex = 0 Then MsgBox "Encabezado!", vbCritical, "EXCELeINFO": Exit Sub
End If
Next j
UserForm3.Show
'Buscar
Call CommandButton1_Click
End Sub
Como se observa el código presenta la llamada a un formulario. Que puede ser realizado como sigue:
El mismo que contiene el botón actualizar, y cuyo código, para todo el módulo del botón y el formulario es el siguiente:
Private Sub CommandButton1_Click()
'Definir variables
Dim datos As Object
Dim Conn As ADODB.Connection
Dim MiConexion
Dim Rs As ADODB.Recordset
Dim MiBase As String
Dim Query As String
Dim codigo, nombre, preciocompra, precioventa, margendecontribucion, proveedor
'Añadir la base de datos
MiBase = "Basededatos.accdb"
Set Conn = New ADODB.Connection
MiConexion = Application.ThisWorkbook.Path & Application.PathSeparator & MiBase
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MiConexion
End With
'Definir datos de los campos del formulario
valorID = Me.lblID.Caption
codigo = Me.codigo.Value
nombre = Me.producto.Value
preciocompra = Me.preciocompra.Value
precioventa = Me.precioventa.Value
margendecontribucion = CDbl(((Me.precioventa.Value - Me.preciocompra.Value) * 100) / Me.preciocompra.Value)
proveedor = Me.proveedor.Value
'Actualizar Tabla
'Nombres de los campos de la tabla de Access = nombres de los campos del formulario (nombres de arriba).
Query = "UPDATE BDProductos SET Codigo = " & "'" & codigo & "'," & _
"Nombre = " & "'" & nombre & "'," & _
"Preciocompra = " & "'" & preciocompra & "'," & _
"PrecioVenta = " & "'" & precioventa & "'," & _
"MargendeContribución = " & "'" & margendecontribucion & "'," & _
"Proveedor = " & "'" & proveedor & "'" & _
" where Id = " & valorID
Set datos = Conn.Execute(Query)
MsgBox "Datos actualizados con éxito", vbInformation
End Sub
'Créditos:
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh
'http://blogs.itpro.es/exceleinfo
'Al iniciar el formulario
Private Sub UserForm_Initialize()
'Definir variables
Dim Cuenta As Integer
Dim i As Integer
Dim valorID, codigo, nombre, preciocompra, precioventa, proveedor
Cuenta = UserForm2.ListBox1.ListCount
'Pasar nombres del list box del UserForm2 a los casilleros del Formulario de Actualización
'Los nombres no deben tener caracteres especiales ya que son referencias visuales
For i = 0 To Cuenta - 1
If UserForm2.ListBox1.Selected(i) = True Then
valorID = UserForm2.ListBox1.List(i)
Me.lblID.Caption = valorID
codigo = UserForm2.ListBox1.List(i, 1)
Me.codigo.Value = codigo
nombre = UserForm2.ListBox1.List(i, 2)
Me.producto.Value = nombre
preciocompra = UserForm2.ListBox1.List(i, 3)
Me.preciocompra.Value = preciocompra
precioventa = UserForm2.ListBox1.List(i, 4)
Me.precioventa.Value = precioventa
proveedor = UserForm2.ListBox1.List(i, 6)
Me.proveedor.Value = proveedor
End If
Next i
End Sub
Este módulo del formulario también debe contener ciertos fragmentos de código para controlar errores, como pueden ser los siguientes:
Private Sub preciocompra_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Modificar el punto por la coma
If InStr(preciocompra, ".") > 0 Then
nuevo = Replace(preciocompra.Value, ".", ",")
preciocompra.Value = nuevo
End If
End Sub
Private Sub preciocompra_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Números con decimales
If InStr("0123456789.", Chr(KeyAscii)) = 0 Then
If KeyAscii <> 8 Then KeyAscii = 0
End If
End Sub
Private Sub precioventa_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Modificar el punto por la coma
If InStr(precioventa, ".") > 0 Then
nuevo = Replace(precioventa.Value, ".", ",")
precioventa.Value = nuevo
End If
End Sub
Private Sub precioventa_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Números con decimales
If InStr("0123456789.", Chr(KeyAscii)) = 0 Then
If KeyAscii <> 8 Then KeyAscii = 0
End If
End Sub
Asimismo los colores en café representan aquellos campos a modificar por parte del usuario, para generar un formulario exactamente como el del ejemplo.
A continuación, se puede observar una muestra del programa con el código en cuestión:
Con este proceso, no habrá necesidad de almacenar datos en Excel a no ser que esos datos se requieran para otros cálculos. Si este es el caso, se podría requerir la importación de datos, generar los diferentes cálculos, guardarlos y nuevamente eliminar los datos primarios registrados en Excel. A continuación, vamos a hacer esto con el mismo archivo.
8. Código VBA para Importar datos de Access a Excel con VBA
En caso que los datos se necesiten en Excel, se presenta el siguiente código, el cual ha sido obtenido del canal de Youtube Tutoriales de Informática.
Vamos con el código a colocarse en un módulo dentro del entorno VBA, para ello click en la pestaña insertar - Módulo.
Acá se establece la siguiente macro:
Sub importardatos()
'Asignar variables
Dim conexion As Object
Dim recordset As Object
Dim consulta As String
Dim cadenaconexion As String
Dim contador As Integer
'Ruta de la base de datos
Set conexion = CreateObject("ADODB.Connection")
cadenaconexion = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\diego\Desktop\Base de datos liviana\Basededatos.accdb"
'Añadir la tabla de Access (ejemplo:BDProductos)
consulta = "SELECT * FROM BDProductos;"
conexion.Open cadenaconexion
Set recordset = conexion.Execute(consulta)
'Bucle que comienza en la hoja Productos, Fila 3, columna 2; para traer los diferentes campos de la tabla de Access
contador = 0
Do Until recordset.EOF = True
Sheets("Productos").Cells(contador + 3, 2) = recordset.Fields(1).Value 'Importar columna de Codigo
Sheets("Productos").Cells(contador + 3, 3) = recordset.Fields(2).Value 'Importar columna de Nombre
Sheets("Productos").Cells(contador + 3, 4) = recordset.Fields(3).Value 'Importar columna de Precio Compras
Sheets("Productos").Cells(contador + 3, 5) = recordset.Fields(4).Value 'Importar columna de Precio Ventas
Sheets("Productos").Cells(contador + 3, 6) = recordset.Fields(5).Value 'Importar columna de Margen de Contribución
Sheets("Productos").Cells(contador + 3, 7) = recordset.Fields(6).Value 'Importar columna de Proveedor
contador = contador + 1
recordset.MoveNext
Loop
recordset.Close
Set recordset = Nothing
conexion.Close
Set conexion = Nothing
MsgBox ("Datos importados con éxito"), vbInformation
End sub
La macro puede asignarse a un botón. Para ello podemos crear cualquier figura en Excel, asignarle un nombre y la macro (click derecho en la figura- asignar macro y escoger la macro "importardatos").
Como un último aspecto de nuestra base de datos optimizada, podemos asignar un botón para que elimine los registros importados en Excel, de esta forma no habrá ningún problema de memoria para el archivo en cuestión.
Bien, vamos con ello:
Sub borrardatos()
'Controlador de error (para evitar borrar encabezados cuando no exista datos que eliminar)
If Worksheets("Productos").Range("B3") = "" Then
MsgBox ("No hay datos para eliminar")
Exit Sub
End If
'Asignar variable
Lr = Worksheets("Productos").Cells(Rows.Count, 6).End(xlUp).Row 'ultima fila en columna G
'Seleccionar datos y eliminar
Application.ScreenUpdating = False
Worksheets("Productos").Select
Worksheets("Productos").Range("B3:G" & Lr).Select
Selection.ClearContents
Worksheets("Productos").Range("B2").Select
End Sub
Como ya se ha dicho, los colores en café representan aquellos campos a modificar por parte del usuario, para generar un formulario exactamente como el del ejemplo con los datos del usuario.
A continuación, se puede observar una muestra del programa con el código en cuestión:
Eso es todo!!... Ahora tenemos un formulario en Excel y VBA con una propia base de datos en Access. Si deseas descargar el material en cuestión puedes hacerlo en el siguiente link:
Entonces, para una base de datos muy ligera, tenemos que tener en cuenta lo siguiente:
- Excel de 64 bits es mejor a su antecesora de 32 en cuestión de memoria.
- Excel no puede almacenar una gran cantidad de datos, por lo que si esto es requerido, se necesita de una conexión con Access.
- Si es necesario manejar los datos con Excel, tratar de no guardar los datos con fórmulas.
(Una forma de hacerlo es copiar los datos con fórmulas y en el mismo sitio pegar como valores, o también es posible asignar un código VBA que lo realice de manera automática).
- No tener mucho formato. Formato excesivo puede conducir a problemas de celeridad.
- No tener imágenes, o tener imágenes de tamaño pequeño ( ver reducir imágenes con photoshop y pasar a Excel).
- De vez en cuando, eliminar filas y columnas que aunque parezcan vacías pueden no estarlo.
(seleccionar filas shift + flecha hacia abajo + end) - click derecho, eliminar.
(seleccionar filas shift + flecha hacia derecha + end) - click derecho, eliminar.
- No arrastrar formato o fórmulas hasta donde parezca que vayamos a utilizar, sino hacerlo siempre cuando ya se ha ingresado un dato.
- Descomprimir el archivo con winrar.
(1. Habilitar las extensiones. Para ello, buscar opciones del explorador de archivos, una vez abierto, click en la pestaña ver, y desmarcar la configuración Ocultar las extensiones de archivo para tipos de archivo conocidos.
2. Cambiar la extensión del archivo de Excel a .zip.
3. Click derecho y click en "Extraer en ubicación" para evitar confundir los archivos.
4. Seleccionar todos los archivos extraídos, click derecho, añadir al archivo - Formato de archivo zip y aceptar.
5. Cambiar la extensión a xlsm o xls según como se guardó el archivo).
Con estos tips se pueden elaborar bases de datos en Excel ligeras, responsivas y optimizadas. Si este artículo ha resultado ser de ayuda puedes dejar un comentario, un me gusta o si deseas armar tus propios formularios y construir tu propia base de datos a partir de nuestro código ya desarrollado puedes considerar adquirir nuestro programa de gestión y planificación de inventarios para sistemas de producción (MRP) a un costo bastante accesible. Con un único pago y licencia perpetua, el mismo te permite editar todo el código sin ningún tipo de restricción, y muchos otros beneficios y funcionalidades que puedes observar en esta web.
Por mi parte, me despido, sin antes desearles un genial día. Y, no se olviden de sonreír, para recordar que la vida sonríe siempre.
Comments