Llevo varias semanas con ganas de escribir una actualización relacionada con la conexión de bases de datos entre varios archivos de Excel. No es la primera vez que realizo un post relacionado, el último sobre la posibilidad de conectar una base de datos de Access con Excel mediante la herramienta OLEBD, la entrada la podéis ver aquí.

Sin embargo, después de publicarla, algunas consultas de los lectores me pedían una solución similar para realizar la conexión entre dos archivos Excel. El motivo es que la solución que ofrece la cinta de opciones en la pestaña “Datos” no supone una solución tan robusta y en caso de tener que utilizar el archivo en otro equipo, pierde la conexión ODBC creada, puedes leer sobre este tipo de conexiones aquí.

Para realizar el siguiente ejercicio, utilizaré el objeto OLEBD y también echaré mano de un poco de programación SQL.

Como siempre, utilizaré un ejemplo práctico, vamos a suponer que tenemos una base de datos en Excel con la información de los empleados de unos grandes almacenes (para componer nombres os recomiendo un post relacionado, (ver aquí). Toda esta información está registrada en un archivo que denominaremos «EJEMPLO_IMPORTAR» y en la pestaña “DATOS”. Esta es la base de datos:

CONECTAR BASE DE DATOS DE EXCEL_1

Pues bien,  ahora supongamos que acabamos de abrir un nuevo archivo y que queremos importar la base de datos que se encuentra en “EJEMPLO_IMPORTAR”  a la primera hoja de nuestro nuevo archivo, que vamos a denominar “IMPORT”.

Para poder hacerlo utilizaremos el siguiente código, (lo pongo completo y comento aquellos fragmentos más importantes):

Sub CONECTAR_BBDD_EXCEL()
'Definimos las variables
Dim i As Double
Dim dfecha As Variant
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim bBien As Boolean
'Limpiamos información de la hoja "DATOS"
Actualizar = Application.CountA(Worksheets("IMPORT").Range("a:a"))
If Actualizar > 0 Then
Worksheets("IMPORT").Range("A1:U" & Actualizar).ClearContents
End If
'Si se produce un error en el proceso de importación, la macro finalizará y mostrará mensaje
On Error GoTo ControlError
bBien = True
'Iniciamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE= C:\Users\Segu\Documents\EJEMPLO_IMPORTAR.xls"
.Properties("Extended Properties") = "Excel 12.0; HDR=YES"
.Open
End With
'Indicamos los parámetros de la consulta SQL para importar la tabla completa
obSQL = "SELECT [DATOS$].*" & "FROM [DATOS$] "
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Grabamos los cabeceros de cada columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("IMPORT").Cells(1, i + 1) = dfecha
Next
'Copiamos los datos de la consulta
With Worksheets("IMPORT").Select
Worksheets("IMPORT").Cells(2, 1).CopyFromRecordset Dataread
End With
With Cells
'Ajustamos ancho de columnas
.EntireColumn.AutoFit
'Alineamos al centro los datos.
.HorizontalAlignment = xlLeft
End With
Salir:
On Error Resume Next
'Si existe un error de conexión mostraremos el siguiente mensaje
If Not bBien Then
MsgBox "NO SE HA PODIDO ACTUALIZAR LA BASE DE DATOS, INTÉNTALO MÁS TARDE."
End If
Exit Sub
'Salimos del proceso
ControlError:
bBien = False
Resume Salir
End Sub

Una vez hemos visto el código, cabe resaltar los siguientes procedimientos. En primer lugar los parámetros de la conexión:

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE= C:\Users\Segu\Documents\EJEMPLO_IMPORTAR.xls"
.Properties("Extended Properties") = "Excel 12.0; HDR=YES"
.Open

Aquí debéis tener en cuenta el la fuente de datos que pongo (en rojo) es la ruta que existe en mi equipo, en el vuestro deberéis cambiarla según la ubicación del archivo.

Una vez que establecemos la conexión, debemos indicar los parámetros de la consulta, y es aquí donde jugaremos con la consulta SQL:

obSQL = "SELECT [DATOS$].*" & "FROM [DATOS$] "

Donde indicaremos que los datos que necesitamos son los que se encuentran en la pestaña “Datos” del archivo de referencia. Con esta sentencia importamos todos los datos incluidos en la hoja (excepto los datos de cabecera).

Para incluir los datos de cabecera, realizaremos otro pequeño proceso con un bucle For Next:

For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("IMPORT").Cells(1, i + 1) = dfecha
Next

Para el ejemplo os dejo una carpeta con dos archivos, uno es en el que tenemos los datos que queremos importar (EJEMPLO_IMPORTAR) y otro (CONECTAR BASE DE DATOS DE EXCEL UTILIZANDO HERRAMIENTA OLEBD Y SQL) que es donde tenemos la macro y desde donde realizaremos la consulta.

No olvidéis indicar la ruta correcta del archivo “EJEMPLO_IMPORTAR”, de lo contrario os saltará el error «NO SE HA PODIDO ACTUALIZAR LA BASE DE DATOS, INTÉNTALO MÁS TARDE.»

Y para finalizar, si utilizáis otra hoja, no olvidéis indicar las referencias a las bibliotecas que utilizaremos:

CONECTAR BASE DE DATOS DE EXCEL_2

Y esto ha sido el ejercicio de hoy, un archivo que tenía muchas ganas de publicar. Es un recurso muy interesante para cuando trabajamos importando bases de datos de otros archivos de Excel.

El archivo de referencia, al estar en una carpeta lo he tenido que subir a Drive. Cuando pinchéis el enlace veréis los dos archivos, un con datos (que es la base que vamos a importar) y otro en blando, que es donde está la macro que debemos ejecutar.

Para finalizar me gustaría comentar que esta entrada la tuve que editar, el motivo es que inicialmente había utilizado el siguiente código para realizar la conexión:

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE= C:\Users\Segu\Documents\EJEMPLO_IMPORTAR.xls"
.Properties("Extended Properties") = "Excel 8.0; HDR=YES"
.Open

Evidentemente funciona correctamente, pero lo hace cuando estamos trabajando con Excel 2003 y con Excel 2010 (en modo compatibilidad) y ambos archivos son .xls. El motivo es que normalmente trabajo con excel 2010 en modo compatibilidad, y lo lógico es publicar un código que también contemple archivos .xlsx o .xlsm, tanto el archivo que se importa como el archivo en el que tenemos la macro.

Lo dicho, el proveedor Microsoft.Jet.OLEDB.4.0 funcionará en Excel 2003 o Excel 2010 en modo compatibilidad y siempre que sean ambos archivos .xls.

Dar las gracias al lector adoxcel, cuyos comentarios al probar el archivo me hicieron pensar en la necesidad de actualizar el código por uno más útil y con más posibilidades.

Descarga el archivo de ejemplo pulsando en: CONECTAR BASE DE DATOS DE EXCEL UTILIZANDO HERRAMIENTA OLEBD Y SQL

 

Comparte este post