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

12 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

  3. Buenas noches, tengo un problema con una actualización de un archivo BASE en excel con muchos datos el cual se actualiza diariamente desde otro archivo excel que me manda mi cliente. El tema es que al archivo BASE sólo le quiero actualizar los registros donde los datos de 3 campos difieren del archivo de mi cliente. Actualmente tengo una macro vba pero al ser muchos registros tarda muchísimo tiempo por eso quería reducir los datos a actualizar filtrando antes sólo lo que necesito mediante SQL, pero no logro conectar los 2 archivos para poder hacer el select de sql y cargar el recordset .
    Basicamente puedo entablar la conexión del archivo de mi cliente, pero no se como abrir la conexión del archivo BASE para poder hacer el select ya que la macro está precisamente en el archivo BASE . Seguramente es sencillo, pero soy novato en esto de integrar SQL y vba en Excel.

    Ah, trabajo con excel 2007

    Desde ya muchísimas gracias a quien me pueda dar una mano.

    Me gusta

    • Hola Andrés:

      En el ejemplo que indico está programado para poder realizar la conexión en excel 2003 y 2010 y por lo tanto debería poder realizarlo en excel 2007.

      ¿no te funciona el ejemplo de descarga del post?, para poder ayudarte necesitaría ver un ejemplo de tus dos archivos, el Excel y el Access.

      De todas formas, sigue las instrucciones del post, descarga los archivos indica las rutas y prueba a ver si funciona.

      Saludos.

      Me gusta

      • Buenas tardes Segu, quizás no quedo claro mi consulta. Yo tengo un archivo BASE en Excel 2007 [ xlsm ] el cual utiliza mi equipo de trabajo diariamente. Pero a este archivo BASE lo actualizo diariamente a partir de otro ARCHIVO Excel 2007 [ xlsx ] mediante una macro en vba. El tema es que el proceso de actualización empezó a tardar cada vez más debido a la cantidad de registros y a las validaciones que realiza la macro para cargar o no la actualización de cada registro. Por eso quería introducir sentencias SQL para agilizar el proceso de actualización, pero me encuentro con que no se como hacer la consulta y conexión de ambos libros ( sí encontre material para crear una consulta que lee de dos hojas del mismo archivo, pero nada cuando se deben abrir dos archivos diferentes ).
        El tema es así :
        La macro corre desde el archivo BASE.xlsm desde el cual, primero debo filtrar aquellos registros que cumplen una cierta condición y en base a este lote acotado de registros del archivo BASE, debo buscar en el ARCHIVO de actualización, sólo aquellos registros cuyos campos difieren entre sí y con este resultado aplicar las actualizaciones en el archivo BASE.

        sería algo así

        select * from ARCHIVO
        left join [ select * from BASE where condicion “” ]
        on ARCHIVO.clave = BASE.clave
        where ARCHIVO.campoA BASE.campoA
        order by ARCHIVO.clave

        El problema es que como en Excel no puedo hacer los SELECT de ambos archivos en tablas temporales y luego cruzarlas, vi que se puede abrir una conexión a cada archivo como si fuera una tabla y plantear la consulta anterior como un join de tablas, el problema es que no se como hacer la consulta para conectar ambas tablas .

        Desde ya que NO puedo utilizar ACCESS ni MySQL ni nada por el estilo porque la empresa no te lo permite, por eso lo tengo todo armado con macros en Excel 2007.

        El tema es que hoy esta actualización la hago, pero recorriendo por cada registros del ARCHIVO de actualización por todos los registros de la BASE, por eso dije al principio que demora mucho. Esto es el motivo por el cual quería introducir sentencias de SQL para manipular los registros y agilizar considerablemente los tiempos al filtrar sólo los casos que realmente se deben actualizar en la BASE.

        Si me puedes dar una mano te lo agradezco, porque la verdad es que estoy leyendo foros y manuales de vba, pero todo está planteado para conectar Excel a ACCESS o a SQL SERVER, pero nada habla de conectar dos archivos excel entre sí.

        Me gusta

¿Te ha gustado?, Realiza 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 )

Google+ photo

Estás comentando usando tu cuenta de Google+. 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 )

Conectando a %s