CONECTAR BASE DE DATOS DE ACCESS (ACCDB) CON EXCEL UTILIZANDO HERRAMIENTA OLEBD

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, tratar y mostrar la información.

De hecho, en el ejercicio que voy a exponer como ejemplo, trataré de explicar como se puede vincular una tabla de Access a Excel y que, mediante una macro, la podamos actualizar en nuestra hoja desde los datos que tenemos en Access.

Vamos allá 🙂

Vamos a suponer que tenemos en una tabla de Access los datos por poblaciones del número de hombre y mujeres que hay en España. (los datos que muestro los he tomado del INE, están publicados y disponibles en su web). Vamos a suponer que esos datos se actualizan cado año de forma automática y dado que tenemos una aplicación que hemos desarrollado en Excel y que necesita disponer de los datos que se recogen en esa base de datos de Access (ACCDB), resultaría conveniente que pulsando un solo botón, nuestra información se actualizase al momento, sin necesidad de importar o copiar y pegar la información de forma manual.

Para ello he creado una base de datos a la que llamaré «DATABASE» y que recoge la tabla con la información de las provincias, de esta forma:

Una vez que tenemos la base de datos bien definida y completa, procedemos a abrir la hoja Excel donde vamos a escribir la macro que nos importará y actualizará la información desde Access. Antes de entrar en el programador vamos a colocar un botón para ejecutar la macro. Así:

Como siempre, debéis ir a la pestaña Programador > Insertar> Controles de formulario y pulsáis en «Botón (Control de formulario)». Aprovechando que estoy en la hoja y dado que la macro lo que va a traer es solo la información y no el formato, con la herramienta de relleno coloreo de gris y pongo en formato de letra «negrita» el cabecero de la tabla.

Ahora vamos a escribir la macro, para ello abrimos el programador de VBA y creamos un módulo nuevo:

Y entonces escribimos el siguiente código:

Sub actualizar_datos()
'ACTUALIZAMOS BASE DE DATOS
Dim path_Bd As String
Dim cnn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim strDB, strSQL As String
Dim strTabla As String
Dim lngCampos As Long
Dim i As Long
Dim bBien As Boolean
On Error GoTo ControlError
bBien = True
'CONECTAMOS CON LA BASE DE DATOS DE ACCESS Y ABRIMOS CONSULTA
path_Bd = "G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb"
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Properties("Data Source") = path_Bd
'EN CASO QUE LA BASE DE DATOS TENGA UNA CONTRASEÑA, SE DEBE PONER AQUÍ
cnn.Properties("Jet OLEDB:Database Password") = ""
cnn.Open
strTabla = "BASE"
strSQL = "SELECT * FROM " & strTabla & " "
recSet.Open strSQL, cnn
'COPIAR LOS DATOS A LA HOJA
Worksheets("Hoja1").Select
'LIPIAMOS DATOS DE EXCEL ANTES DE ACTUALIZAR
limpiardatos = Sheets("Hoja1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Hoja1").Range("A2:z" & limpiardatos).ClearContents
'GRABAMOS NUEVA BASE DE DATOS DE ACCESS
Sheets("Hoja1").Cells(2, 1).CopyFromRecordset recSet
'COPIAMOS RÓTULOS
lngCampos = recSet.Fields.Count
For i = 0 To lngCampos - 1
Sheets("Hoja1").Cells(1, i + 1).Value = recSet.Fields(i).Name
Next
'DESCONECTAMOS
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
Sheets("Hoja1").Select
MsgBox "LA BASE DE DATOS HA SIDO ACTUALIZADA."
Salir:
On Error Resume Next
If Not bBien Then
MsgBox "NO SE HA PODIDO ACTUALIZAR LA BASE DE DATOS, INTÉNTALO MÁS TARDE."
End If
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
ControlError:
bBien = False
Resume Salir
End Sub

En cada uno de los pasos os he ido comentando (en verde) qué es lo que hace la macro en cada momento. Antes de asignar la macro al botón de la hoja, debemos hacer los siguientes cambios en las referencias del proyecto. Como muestro en la siguiente imagen, pulsamos en Herramientas y luego en Referencias:

Una vez que pulsamos en Referencias, se nos abre un cuadro de opciones y debemos marcar las mismas que os muestro, de lo contrario, la macro no funcionará:

Una vez que tenemos hemos hecho esto la macro ya está totalmente operativa y la hoja funcionará perfectamente.
Un dato importante, de la macro que os acabo de mostrar es la ruta de acceso a la base de datos, veamos que es lo que aparece:

'CONECTAMOS CON LA BASE DE DATOS DE ACCESS Y ABRIMOS CONSULTA
path_Bd = "G:\PRUEBAS ACCESS\CONEXION ACCESS Y EXCEL. OLEBD\DATABASE.accdb"
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Properties("Data Source") = path_Bd
cnn.Properties("Jet OLEDB:Database Password") = ""
cnn.Open
strTabla = "BASE"
strSQL = "SELECT * FROM " & strTabla & " "
recSet.Open strSQL, cnn

La ruta que aparece en rojo la debéis modificar antes de probar la macro, en mi PC dispongo de unidad G (como disco externo), y el ejemplo lo tengo en una carpeta llamada PRUEBAS ACCESS y a su vez en otra carpeta denominada CONEXIÓN ACCESS Y EXCEL, dentro está la base de datos «DATABASE.accdb»

Es importante que lo recordéis, debéis modificar la ruta y especificando donde se encuentra la base de datos de Access en vuestro equipo, de lo contrario, no funcionará.

Tan solo queda asignar la macro al botón y listo, ya tenéis el proceso de actualización de datos totalmente preparado. Así es como funcionaría:

Los ejemplos de este ejercicio están basados en Excel 2010 y Access 2010, en versiones anteriores puede dar problemas, para Access 2003 (archivos .mdb) abría que modificar la macro donde pone:

cnn.Provider = «Microsoft.ACE.OLEDB.12.0» se debe poner: cnn.Provider = «Microsoft.Jet.OLEDB.4.0»

Os dejo el archivo de ejemplo, lo subo con un archivo .RAR con la carpeta entera y desde Google Drive dada la extensión de la Access que no está permitida en WordPress.

Descarga el archivo pulsando en: CONECTAR BASE DATOS CON OLEDB