CONECTAR BASE DE DATOS DE EXCEL UTILIZANDO HERRAMIENTA OLEBD Y SQL

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

 

Anuncios

8 pensamientos en “CONECTAR BASE DE DATOS DE EXCEL UTILIZANDO HERRAMIENTA OLEBD Y SQL

  1. Me da error al llegar a la siguiente linea
    .Properties(“Extended Properties”) = “Excel 8.0; HDR=YES”

    Utilizo Excel 2010

    Las referencias y demás esta todo correcto, y la dirección de los archivos también, alguna idea de lo que estoy haciendo mal?

    Un saludo

    Me gusta

    • Hola Adoxcel,

      Debería funcionarte correctamente. Lo he probado en 2010 y no saltaba error. Puedes enviarme a excelsignum@yahoo.es el archivo a importar? El nombre y la estructura de datos? Así descartamos problemas de ese tipo. Y también la macro adaptada?

      Intento contestarte a lo largo el día, que no estoy disponible en estos momentos.

      Saludos

      Me gusta

      • De momento no he modificado nada, son los archivos tal cual los he descarado, solamente he cambiado la dirección.
        He creado una carpeta en el escritorio para probar
        .ConnectionString = “DATA SOURCE= C:\Users\usuario\Desktop\ConexionOLEDBySQL\EJEMPLO_IMPORTAR.xls”

        Ejecuto la macro paso a paso y al llegar a la linea .Propierties …. me salta a ControlError:

        Al ponerme sobre la linea .Properties(“Extended Properties”) = “Excel 8.0; HDR=YES”
        me aparece el siguiente mensaje .Properties(“Exten…= <No se encontro el proveedor especificado. Es posibl …..

        Muchas gracias por tu interés, seguramente sera alguna referencia que tengo que activar, pero el cuadro de referencias es igual al del post.

        Me gusta

  2. Ok, haz una cosa, guarda el archivo donde esta la macro. Como libro de Excel 97 y 2003 te aparecerá en modo de compabilidad al abrirlo en 2010. Y ejecuta la macro tal cual está en el post. Son las referencias para archivos XLSX. En breve actualizare la entrada con estos casos.

    Me gusta

    • Los archivos que me baje son xls, y cuando los abro ya me aparecen en modo compabitilidad. Lo he guardado como archivo xlsm por ver si funcionaba pero nada tampoco funciona.

      Voy a ver si lo pruebo en otro ordenador, que me estoy temiendo sea un problema del mio, que este algo mal instalado o vete tu a saber que. Claro que si el problema es que le falta alguna biblioteca seria interesante saber cual, para poder exportar este archivo a otros ordenadores.

      Muchas gracias por tu interes, por tu paciencia y siento darte la “trisca” con esto, pero si que me resulta un tema muy interesante.

      Un saludo

      Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s