Conectar Excel con Access - PROGRAMAR EN VBA MACROS DE EXCEL

Conectar Excel con Access, te voy a mostrar una macro que enseña como conectar Excel con Access, es decir toma los datos de una hoja de Excel y los guarda en una Tabla de Access, este es la primer entrega de una zaga de varios artículos dedicados a la conexión Excel con Access, luego mostraré como modificar datos, eliminar datos, traer los datos a un formulario, suscribe al blog o YouTube para recibir las actualizaciones, en el post Excel y SQL expongo las principales sentencias SQL que son útiles cuando se conecta Excel con Access .

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

Para poder conectar Excel con Access se debe habilitar una referencia que es «Microsoft ActiveX Data Objects 2.5 Library», no obstante como posteriormente vamos a seguir trabajando con este mismo ejemplo te recomiendo habilites todas las referencias que están en la figura siguiente:
.

Para habilitar las referencias debes ingresar a Excel, iniciar el editor de VBA con Alt + F11, luego debes ir al menú Herramientas y luego al sub menú Referencias, se abre el cuadro de diálogo igual al mostrado en la imagen anterior, debiendo hacer click en las casillas para activar las referencias, busca las que salen tildadas en la imagen y tilda en tu archivo, para que quede en forma idéntica, habilitadas las referencias ya no tendrás problemas con ellas ya que de lo contrario te daría erro ciertas lineas de código.

Sugiero descargar el archivo de ejemplo y ver la codificación para un mejor entendimiento, el archivo que se descargará es un fichero comprimido .rar; se debe descomprimir en cualquier dirección de la PC, la única condición para que el ejemplo 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 mismo directorio.

Lo primero que se va a realizar es declarar las variables para conectar Excel con Access estas son las variables necesarias:
Dim cn As ADODB.Connection, rs As ADODB.Recordset

Luego de ello se crea un objeto conexión y un objeto recordset de esta manera:

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

Posteriormente se abre la conexión de esta forma:

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «171 ProgramarExcel.accdb;»

Si estás usando una base de datos .mdb es decir 2003 o anterior se debe usar el siguiente proveedor de conexión, «microsoft.jet.oledb.4.0» en vez de «Microsoft.ACE.OLEDB.12.0» que es para la versión de Access 2007 o superior, esto es algo importante a tener en cuenta ya que de lo contrario daría un error.

Si leemos el código presentado y encargado de abrir la conexión con Access; se debe colocar la ruta exacta de donde está la base de datos, en este ejemplo se supone que la base de datos está en el mismo directorio que el archivo de Excel que contiene la macro por eso se usa «ThisWorkbook.Path» y el nombre del archivo, es decir estamos extrayendo la dirección o path del archivo de Excel y agregando el nombre de la base de datos al final, con ello obtenemos la ruta completa, es igualmente válido si se escribe directamente la ruta del archivo de base de datos.

Antes de continuar se debe dejar claro que es un objeto recorset, éste se puede definir como objetos que se usan para manipular datos de una base de datos en el nivel de registro; toos los objetos recorset se construyen con registros (filas) y campos (columnas).

Luego debemos abrir el objeto recordset de la tabla que contiene va a contener los datos, esto se hace con el siguiente código:

rs.Open «Clientes», cn, adOpenKeyset, adLockOptimistic, adCmdTable

Posteriormente vamos a realizar un bucle recorriendo las filas hasta la última con datos, esto se hace con:

While a.Cells(fila,»A»)<> empty

…

Wend

Luego haremos que cada campo del objeto recordset sea igual a la celda que contiene el registro respectivo, esto se hará con todos los campos de la tabla Clientes de la base de datos, estableciendo que se debe agregar un nuevo registro al campo (Fields), existe otra forma de agregar registros a través de sentencias SQL que la veremos en otra entrega; se utiliza el siguiente código:

With rs

.AddNew

.Fields(«Id») = Cells(fila, «A»)

.Fields(«Nombre») = Cells(fila, «B»)

.Fields(«Telefono») = Cells(fila, «C»)

.Fields(«Direccion») = Cells(fila, «D»)

.Fields(«Mail») = Cells(fila, «E»)

.Fields(«Credito») = Cells(fila, «F»)

.Update

End With

En la figura que sigue se muestra el libro de Excel que contiene los datos a exportar y en la siguiente los resultados obtenidos luego de haber exportado los datos, como se podrá observar, todos los datos de Excel se exportaron a la base de datos de Access.

Por último se debe cerrar las variables y vaciarlas, ya que de lo contrario consumiría muchos recursos, si no realiza esta operación, en el ejemplo no afecta en nada, porque son muy pocos registros, pero cuando se tiene 70.000, 100.000 registros o más, consume muchísimos recursos y se haría evidente en la ralentización de la PC; el vaciado de las variables se realiza con los códigos siguientes;

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

Desde el link final podrás descargar el ejemplo totalmente GRATUITO denominado Conectar Excel con Access, recordando que si este o alguno de los cientos de ejemplos publicados te fueron de utilidad y puedes invitarme un café (desde final del post) y de esta manera aportar a seguir manteniendo esta página

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlas, buscar en listbox mientras escribes en textbox, ordenar hojas libro excel por su nombre, conectar Excel con Access y muchos ejemplos más.

if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

Código que se inserta en un módulo

Sub CopiaDatos()
Dim fila As Long, uf As Long, conta As Long
Dim cn As ADODB.Connection, rs As ADODB.Recordset
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set a = ActiveSheet
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «171 ProgramarExcel.accdb;»
rs.Open «Clientes», cn, adOpenKeyset, adLockOptimistic, adCmdTable
fila = 2
conta = 0
While a.Cells(fila, «A») <> Empty
With rs
.AddNew
.Fields(«Id») = Cells(fila, «A»)
.Fields(«Nombre») = Cells(fila, «B»)
.Fields(«Telefono») = Cells(fila, «C»)
.Fields(«Direccion») = Cells(fila, «D»)
.Fields(«Mail») = Cells(fila, «E»)
.Fields(«Credito») = Cells(fila, «F»)
.Update
End With
conta = conta + 1
fila = fila + 1
Wend
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox («Se procesaron » & conta & » registros con éxito, se omitieron duplicados»), vbInformation, «AVISO»
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends