En nuestros trabajos muchas veces dependemos de diferentes tecnologías para obtener un determinado resultado, Excel nos es la única herramienta con la que tenemos que trabajar, un ejemplo es el uso de SQL o de Access a la hora de Extraer, Registrar, Modificar, Eliminar o mostrar la información detalladamente.

De hecho, en este Aplicativo, trataré de explicar los tipos de Conexión una tabla de Access a Excel y que, mediante de código VBA podamos actualizar la información desde un UserForm (Formulario) en Excel con los datos que tenemos en Access.

Una Base de Datos es una entidad en la cual se pueden almacenar datos de manera estructurada, con la menor redundancia posible. Diferentes programas y diferentes usuarios deben poder utilizar estos datos. La principal ventaja de utilizar bases de datos es que múltiples usuarios pueden acceder a la información al mismo tiempo, claro con niveles de privilegios a distintas áreas y que se encuentren en red.

Primeramente para poder conectar Excel con Access se debe habilitar ciertas referencias en Excel y para eso te debes ir al Editor del Código VBA, luego te diriges al menú “Herramientas” y das click en “Referencias” te aparece un cuadro de dialogo aparecen unas librerías ya tildadas esas son las que debes habilitar en tu archivo, te quedara como ves en la siguiente imagen o algo similar; si este fuese el caso de que uses en tu pc Office 2013 de lo contrario si estas usando Office 2016 te aparecerá así,  “Microsoft Office 16.0 Object Library”

Te sugiero que cuando descargues este aplicativo, y si sabes Código VBA ve a la codificación para un mejor entendimiento, el archivo que se descargará es un fichero comprimido .zip; se debe descomprimir en cualquier dirección de la PC, la única condición para que este aplicativo te funcione es que ambos archivos, es decir el Excel que contiene la Macro y el de Base de Datos de Access, deben estar en el misma carpeta o ruta. Ya hecho esto tus archivos te quedaran así, si tu abres la Base de Datos, en este caso es en Access y lleva por nombre “DATAPROV”, veras la Tabla llamada “Proveedores” ya que en este caso nos enfocaremos en los Registros de datos de Proveedores si ves la siguiente imagen ;

Ahora te explicare cómo funciona el Aplicativo ya realizado lo anterior y más adelante te explico parte del código y su función:

Abrimos el Aplicativo el cual tiene por nombre “Registrar Datos en Access + Consultas a ListBox”, das doble click y te aparecerá esta ventana;

Donde a simple vista veras un botón que tiene por nombre “Registrar y Consultas”, donde allí tu tendrás el control total de la base de datos y podrás hacer las consultas, aun lado te explica algunas de las demás funciones que te trae este Aplicativo, seguidamente das click en el botón, te aparecerá el formulario para gestionar los datos de los Proveedores como ven en la Imagen anterior.

Automáticamente te aparece el primer registro que tienes en la base de datos, aun lado veras los botones los cuales te auxiliaran a realizar las siguientes funciones:

Botón “Nuevo Reg.”: limpiara todos los campos donde tú registraras un nuevo dato.

Botón “Registrar.”: Este botón se activará una vez hayas dado click en el botón “Nuevo Reg.”, ya que tienes tus datos en los campos solicitados, das click en registrar y automáticamente tus datos se registrarán en la base de datos de Access.

Botón “Modificar”: Este botón guardara la información actualizada de los registros de proveedores.

Botón “Eliminar”: primero elige un proveedor con los botones de desplazamiento y con este botón al darle click automáticamente lo borraras de la base de datos de Access.

Botón “Salir”: Solo con este botón podrás salir del formulario del Menú.

Botón “Consultas”: al dar click te abre el Formulario para consultar los datos de los proveedores registrados.

Mas debajo de los campos a registrar tienes los botones de desplazamientos, los cuales son “Primero, Anterior, Siguiente y Ultimo”, estos te ayudaran a buscar un registro de algún proveedor más dinámicamente y de fácil acceso, y para que realices las siguientes opciones de “Modificar, Eliminar”.

Luego en el botón “Consultas”, das click al botón y te aparecerá un cuadro de lista, ver imagen de abajo;

Desde este formulario tienes la opción de consultar datos externos según sea el criterio a buscar ya que en este caso será “Buscar Proveedor por Nombre”, donde tú solo tendrás que digitar el nombre del Proveedor según como lo hayas guardado en la base.

Ya por ultimo algo más interesante y práctico el Aplicativo trae la Macro para poder Exportar los datos desde la Tabla de Access a Hoja de Excel, como lo ves en esta imagen;

Acá, tú solo debes dar click en el botón que dice “Exportar Proveedores”, y automáticamente los datos que tengas en Access estos pasaran a Excel con un solo click.

Ahora te explicare parte del Código y cuál será su función, en el módulo llamado “TimeConecExporCons”, encontraras el código de conexión a la base de datos de Access al igual la macro para Exportar de Access a Excel. Lo Primero es declarar ciertas variables para hacer la conexión y guardar los datos de Excel a Access:

Esta Variable es para hacer la conexión entre el formulario de registrar datos y modificar a Access.

Public miConexion As New ADODB.Connection

Esta Variable es para poder guardar los datos de desde Excel a Access.

Public Rs As New ADODB.Recordset

Las siguientes variables son para hacer la conexión con el formulario de consulta y la tabla Proveedores de Access y hacer la referencia a la columna la cual buscaremos como criterio en el campo de consulta.

Public Cnn As New ADODB.Connection

Public Rsd As New ADODB.Recordset

Public Sql As String

Esta otra Variable esta para hacer que funcione la fecha y hora en la barra de título del formulario del Menú y que sea al iniciar el Formulario.

Global onOff2 As Boolean

Este Código es para que funcione la Fecha y Hora en el Caption del UserForm:

Sub MostrarHoras()

On Error Resume Next

IngresaProveedor.Caption = “Día: [ ” & Format(Now, “dddd dd-mm-yyyy”) & ” ] Hora: [ ” & Format(Now, “hh:mm:ss”) & “]     Milton Aguirre”

If onOff2 = True Then

Application.OnTime Now + TimeValue(“00:00:01”), “MostrarHoras”

Else

Application.OnTime 0, “”

End If

End Sub

El siguiente código es para hacer la conexión entre la base de datos de Access a Excel ya que nos servirá en el Formulario de Registro, Modificar, y Eliminar el registro del Proveedor.

Sub Conectar()

Set miConexion = New ADODB.Connection

With miConexion

.Provider = “Microsoft.ACE.OLEDB.15.0”

.ConnectionString = “Data Source=” & ThisWorkbook.Path & “\DATAPROV.accdb”

.Open

End With

End Sub

En este código se hace la conexión entre Access y Excel, en el Formulario de Consultas.

Sub Conexion()

Set Cnn = New ADODB.Connection

With Cnn

.Provider = “Microsoft.ACE.OLEDB.15.0”

.ConnectionString = “Data Source=” & ThisWorkbook.Path & “\DATAPROV.accdb”

.Open

End With

End Sub

Ya por ultimo tenemos el código o Macro para que funcione el poder Exportar los datos desde la Tabla “Proveedores” a la hoja de Excel. Te explicare parte por parte:

‘La macro llevar por nombre “ExportarProveedores”.

Sub ExportarProveedores()

‘Actualizaremos la Base de Datos de Access a Excel con siguientes variables.

Dim path_Bd As String

Dim Cnnc As New ADODB.Connection

Dim recSet As New ADODB.Recordset

Dim strDB, strSQL As String

Dim strTabla As String

Dim Encabz As Long

Dim i As Long

Dim Correc As Boolean

On Error GoTo ControlError

Correc = True

‘Conecta con la Base de Datos Access y abre Consulta para Excel. Recuerda que tienes que cambiar la Ruta donde se encuentra la base de datos.

path_Bd = ” E:\Registrar Datos en Access + Consultas a Listbox\DATAPROV.accdb”

‘Acá debes cambiar el Tipo de Proveedor en este caso estoy usando Office 2013 es por eso que tiene “15”.

Cnnc.Provider = “Microsoft.ACE.OLEDB.15.0”

Cnnc.Properties(“Data Source”) = path_Bd

‘En dado caso de que tenga contraseña la base de datos de Access, puedes poner la Contraseña entre las comillas (“”).

Cnnc.Properties(“Jet OLEDB:Database Password”) = “”

‘Aquí se conecta con la Tabla “Proveedores”, de la base de datos de Access.

Cnnc.Open

strTabla = “Proveedores”

strSQL = “SELECT * FROM ” & strTabla & ” “

recSet.Open strSQL, Cnnc

‘Copia los Datos de Access a la Hoja llamada “Exporta”.

Worksheets(“Exportar”).Select

‘Limpia la Consulta Anterior o Datos que Exista en la Hoja “Exporta”.

limpiardatos = Sheets(“Exportar”).Range(“A” & Rows.Count).End(xlUp).Row

Sheets(“Exportar”).Range(“A2:G” & limpiardatos).ClearContents

‘Guarda nueva base de datos de Access a Excel.

Sheets(“Exportar”).Cells(2, 1).CopyFromRecordset recSet

‘Copia los Encabezados que tenga la Tabla “Proveedores”.

Encabz = recSet.Fields.Count

For i = 0 To Encabz – 1

Sheets(“Exportar”).Cells(1, i + 1).Value = recSet.Fields(i).Name

Next

‘Desconecta la Base de Datos

recSet.Close: Set recSet = Nothing

Cnnc.Close: Set Cnnc = Nothing

Sheets(“Exportar”).Select

MsgBox “Los Datos de los Proveedores se han Actualizado y Exportado con Éxito.”

Salir:

On Error Resume Next

If Not Correc Then

MsgBox “No se ha Podido Encontrar la Ruta, de la Base de Datos de los Proveedores.”

End If

recSet.Close: Set recSet = Nothing

Cnnc.Close: Set Cnnc = Nothing

Exit Sub

ControlError:

Correc = False

Resume Salir

End Sub

Algo muy importante y que debes de tener en cuenta es con el tipo de Proveedor que estás trabajando con Microsoft Office.

Si en tu caso usas MS Office Excel 2013 debes usar este:

“Microsoft.ACE.OLEDB.15.0”

Pero si al contrario estés usando MS Office Excel 2016 usaras este otro:

“Microsoft.ACE.OLEDB.16.0”

Pero en todos los casos siempre será para Office Excel:

“Microsoft.ACE.OLEDB.12.0”

Este aplicativo se ha Ejecutado hasta el momento en:

Microsoft Office Excel 2013 y Microsoft Office Access 2013

Microsoft Office Excel 2016 y Microsoft Office Access 2016

También en Sistemas Operativos de:

Windows 7 de 32bits.

Windows 10 de 64bits.

Y funciona perfectamente sin ningún problema, solo sigues los pasos ya antes mencionado y este Aplicativo funcionara correctamente en tu Pc.

Con este Ejemplo (Proyecto o Aplicativo), lo pueden utilizar para implementarlo en sus proyectos, seguirlo mejorando o para aprender ya que contiene códigos muy simples y fácil de aprender. Al igual en este Blog de MacroExcelVBA-Ag encontraras muchos ejemplos los cuales contienen Access, Excel y ListBox.


Comparte y sigamos creciendo, al final te estaré dejando el link para que te descargues este Ejemplo y comiences a aprender e Implementes a tus proyectos.

  1. Comparte esta Publicación.
  2. Invita a visitar esta Pagina a tus Amigos/as, Compañeros/as y personas que usan Excel.
  3. Pulsa el Botón Descargar Gratis, para descargar gratuitamente el Archivo.

No te olvides de Compartir este contenido ya que estarás ayudando a MacroExcelVBA a que te comparta más material y proyectos de apoyo para tu aprendizaje o a finalizar tus proyectos.


Registrar Datos en Access desde Excel y Consultar a ListBox GRATIS
Etiquetado en:

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Gira ahora para obtener un premio increíble.

  • No hay trampas
  • 1 giro por correo electrónico
  • Sólo tienes que introducir el correo electrónico y girar
Prueba tu Suerte
WhatsApp Escríbenos a WhatsApp