obtener datos desde otro archivo de excel, con ADO

Ya hemos visto en repetidas oportunidades las consultas a fuentes de datos externas, con el objetivo de migrar hacia Excel registros que se encuentran almacenados fuera de dicha aplicación. Siempre la tarea se llevó a cabo mediante las opciones del menú "datos / obtener datos externos / " y sus respectivos items:

Aprendimos que el origen de esos registros pueden provenir de tablas en la web, Access, otro archivo de Excel.... hasta la forma de conectarnos con Access "colgado" de un servidor. Para repasar algunos de estos concepto, pueden hacer click aquí, que los llevará a la sección "datos" del blog.

[+/-] Ver el resto / Ocultar

Pero existen otras formas de realizar esas conexiones, muy poderosas. Si bien para "hacerlas desde cero" será casi indispensable conocer sobre programación, un buen ejemplo nos evitará ese requisito, ya que luego será sencillo modificar el código y adaptarlo a nuestros proyectos. Y a eso apunto con esta entrada.

En esta oportunidad veremos la forma de leer datos de otro libro de Excel y traerlo al actual, sin necesidad de abrirlo, con la utilización del siempre amado y nunca bien ponderado ADO.

Todo bien, pero ¿que es ADO? Empecemos por el principio. ADO = ActiveX Data Object, es decir, un objeto ActiveX para el control de datos. Con el nos resultará posible conectarnos y manejar con total libertad una gran cantidad de bases de datos, como Sql, Access, el propio Excel o archivos de texto.

Su poderío es muy grande: haremos altas / bajas / modificaciones de registros, crear o destruir tablas, realizar consultas con distintos criterios, etc, etc, etc. Si, todo eso lo hace un solo objeto.

ADO nos servirá de intermediario. Nuestro Excel no "puede ver" los datos almacenados en un archivo mdb o dbf, entonces le pedirá que lo haga por el y, como buen amigo, cumplirá. El esquema sería:

Desde Excel controlaremos ADO (con sus propiedades, métodos y eventos, como cualquier otro objeto) y este último se hará cargo de conectarse y manejar (de acuerdo a nuestras instrucciones) la gran mayoría de las bases de datos existentes. ¿Ventajas? Bueno, podríamos tener una base en MySql (que soporta millones y millones de registros, cosa que Excel no soporta) y manipular sus datos y/o estructura con total libertad, llevando luego a nuestro libro los datos que nos interesen analizar.

Empezaremos manejando datos que se encuentran en otro libro de Excel, para no complicar las cosas. Crearemos una macro que: ubique un determinado xls en la PC, nos conectaremos a el con ADO, haremos una consulta sobre una tabla y devolveremos los datos de esa consulta a nuestro libro actual. Lo mejor es que ese segundo libro (que actúa como base de datos) no será necesario abrirlo. Como contrapartida será necesario ser ordenados en la estructura de los libros "bases de datos", pero estimo que no será un problema mayor, ya que si o si debemos comportarnos de esa forma al trabajar con grandes cantidades de registros, utilicemos o no ADO.

Basta de charla y empecemos con el trabajo. Abrimos un nuevo libro y con ALT + F11 nos vamos a VBA, menú "herramientas / referencias", para buscar y tildar la opción: "Microsoft ActiveX Data Objects 2.0 Library", que en buen cristiano sería la librería que contiene todas las funciones necesarias para acceder a ADO.

Ahora crearemos mediante código a ADO. Veamos las declaraciones de variables y objetos:

Dim Conexion As ADODB.Connection
Dim Record_Set As ADODB.Recordset
Dim Campo As ADODB.Field
Dim RutaArchivo, Sql As String
Dim i As Long

Definimos la conexión (a la cual llamo, muy originalmente, "Conexion"), un Recordset y un Campo. ¿Que es un Recordset? Sencillamente es un conjunto de registros: en el se almacenarán los resultados de nuestra consulta, misma que volcaremos a Excel. Y a Campo lo defino para manejar los campos de la tabla de la base de datos.

RutaArchivo es para definir en que lugar del disco estará el libro de Excel que intentamos leer y Sql será la variable en donde definiremos la consulta Sql para acceder a los datos. (Sql es un lenguaje creado por IBM en los 70', estandarizado a nivel mundial, que posee una sintaxis propia para el manejo de datos)
Parece mucho lío, pero no le es. Nos encontramos en el "paso a paso" y quizás pueda resultar confuso, pero veremos que un punto fuerte de todo esto es la sencillez, a medida que desarrollemos el ejercicio.

Ya definimos la variables en juego. Comencemos a codificar la "acción":

'defino la ruta en donde se encuentra nuestro archivo:
RutaArchivo = "E:\base.xls"
'creo la conexión:
Set Conexion = New ADODB.Connection
'creo el recordset
Set Record_Set = New ADODB.Recordset
'y abro la conexión: vean que después del método Open concateno la dirección
'del archivo "base". Esta sintaxis puede cambiar de acuerdo al origen de los
'datos (excel, access, mysql, txt, etc, etc, etc)
Conexion.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & RutaArchivo

Estimo que lo mostrado en el párrafo anterior no merece mayores detalles, todo se encuentra comentado.
Seguimos:

'aquí está el lenguaje de IBM: Sql (structured query language o lenguaje estructurado de consulta): aqui le 'estoy diciendo: "seleccioname todos ( * ) los registros de la tabla1
Sql = "SELECT * FROM [tabla1]"

Vale una aclaración: "tabla1" es el nombre de un rango dentro del libro "base.xls" (ya lo veremos mas adelante). En estos primeros pasos Sql es muy sencillo me entender: la sentencia de arriba dice claramente que quiero todos los registros de la tabla1. Pero a no confundirse: existen muy pocas personas capaz de manejar Sql profesionalmente, para consultas complejas se vuelve algo realmente difícil.

Seguimos:

'ahora abro el recordset, pasando como primer argumento la instrucción Sql.
'por el momento no ahondaremos en los otros dos parámetros.
Record_Set.Open Sql, Conexion, adOpenStatic

'creo una nueva hoja, en donde colocaré los resultados:
Sheets.Add
ActiveSheet.Cells(2, 1).CopyFromRecordset Record_Set

'con un blucle recorro cada uno de los campos de la tabla
'para tomar su nombre y colocarlo en la nueva hoja
i = 1
For Each Campo In Record_Set.Fields
 ActiveSheet.Cells(1, i) = Campo.Name: i = i + 1
Next Campo

'cierro el recordset y la conexion, es ese orden:
Record_Set.Close
Conexion.Close

'destruyo los objetos, para liberar recursos
Set Record_Set = Nothing
Set Conexion = Nothing


Eso es todo, al final del post colocaré todo el código junto, mas el archivo para la descarga. En este último tramo vemos la forma de obtener los datos (abriendo el recordset), insertar una hoja, copiar los registros obtenidos en la nueva hoja y recorrer los campos de la tabla, para así colocar sus respectivos nombres en el encabezado. Por último cerramos el recordset y la conexión, para luego destruir los objetos.

Volvamos al principio. El libro "base.xls" contiene información sobre ventas realizadas en una empresa, como se muestra en la siguiente imagen:

Seleccionamos toda la tabla y desde el cuadro de nombres le definimos uno, a saber: "tabla1", que es el rango que pasamos dentro de la instrucción Sql que mas arriba les mostré. Si no quisiéramos manejar nombres de rangos deberíamos poner:
Sql = "SELECT * FROM [Hoja1!$A$1:$Z$10000]"
especificando así la hoja y el rango desde los cuales leeremos los datos. Usemos los nombres convencionales o no, siempre deberemos encerrarlos entre llaves [ ].

Pero hilemos mas fino. Si aplico el código tal cual está solo conseguiré traer todos los datos existentes al nuevo libro, lo cual no tiene mucha lógica para una consulta. Mejor sería indicarle a Sql: brindame las ventas de evelyn, lo cual se logra haciendo una modificación:
Sql = "SELECT * FROM [tabla1] WHERE [vendedor]='evelyn'"
Aquí agregamos una cláusula a la consulta WHERE, lo que traducido sería: seleccioname todos los registros de la tabla1, cuyo vendedor sea igual a evelyn. (anoten que 'evelyn' está entre dos apóstrofes: si los omitimos, simplemente no funciona)
Ejecuto todo el código y listo:

todas las ventas de evelyn en mi nuevo libro, leídos desde su origen sin la necesidad de abrir el archivo "base.xls"

Podemos ir mas lejos con solo "retocar un poco" la consulta Sql:
Sql = "SELECT * FROM [tabla1] WHERE [vendedor]='evelyn' AND [monto] >=500"
Aquí incorporo a AND ( y ) para especificar que necesito las ventas de evelyn, cuyos montos sean mayores o iguales a 500 (aquí ese 500 no tiene apostrofes, por que es numérico. Ojo con eso.)
Los resultados:

tal cual: todas las ventas de evelyn, superiores o iguales a 500. si comparamos esta imagen con la anterior veremos que todo funciona correctamente.

Ya estarán viendo que esto es interminable, los límites simplemente los ponen nuestra imaginación o las necesidades del proyecto. O ambas.
Veamos una imagen mas, en la cual cambié la sentencia Sql:
Sql = "SELECT * FROM [tabla1] WHERE [vendedor]='barbara' AND [producto]='micro'"
Para obtener:

Si tenemos varios libros desde donde extraer información, podríamos generar un bucle que recorra un directorio determinado y: abra, tome los datos, los vuelque a nuestro archivo, cierre y así continúe con el próximo. Es una excelente herramienta al momento de manejar grandes cantidades de datos.

Hablando de cantidad. El objeto Recordset tiene una propiedad muy útil: RecordCount, que nos devuelve la cantidad de registros obtenidos con la consulta. Agrego este código al proyecto:

If Record_Set.RecordCount > 0 Then
    'creo una nueva hoja, en donde colocaré los resultados:
    Sheets.Add
    ActiveSheet.Cells(2, 1).CopyFromRecordset Record_Set
Else
    MsgBox "No se encontraron resultados", vbExclamation
End If

¿Que hice? Si el RecordCount es mayor a 0 (es decir, existen resultados) creo la hoja y copio los datos, caso contrario aviso que la búsqueda, con los parámetros ingresados, fue infructuosa.
Aquí les dejo el código completo:

Sub Excel2Excel_ConADO()
Dim Conexion As ADODB.Connection
Dim Record_Set As ADODB.Recordset
Dim Campo As ADODB.Field
Dim RutaArchivo, Sql As String
Dim i As Long

'defino la ruta en donde se encuentra nuestro archivo:
RutaArchivo = "E:\base.xls"
'creo la conexión:
Set Conexion = New ADODB.Connection
'creo el recordset
Set Record_Set = New ADODB.Recordset
'y abro la conexión: vean que después del método Open concateno la dirección
'del archivo "base". Esta sintaxis puede cambiar de acuerdo al origen de los
'datos (excel, access, mysql, txt, etc, etc, etc)
Conexion.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & RutaArchivo

'aquí está el lenguaje de IBM: Sql (structured query language o lenguaje estructu
'rado de consulta): aqui le estoy diciendo:
Sql = "SELECT * FROM [tabla1] WHERE [vendedor]='barbara' AND [producto]='micro'"

'ahora abro el recordset, pasando como primer argumento la instrucción Sql.
'por el momento no ahondaremos en los otros dos parámetros.
Record_Set.Open Sql, Conexion, adOpenStatic

If Record_Set.RecordCount > 0 Then
    'creo una nueva hoja, en donde colocaré los resultados:
    Sheets.Add
    ActiveSheet.Cells(2, 1).CopyFromRecordset Record_Set
Else
    MsgBox "No se encontraron resultados", vbExclamation
    GoTo salida
End If
'con un blucle recorro cada uno de los campos de la tabla
'para tomar su nombre y colocarlo en la nueva hoja
i = 1
For Each Campo In Record_Set.Fields
 ActiveSheet.Cells(1, i) = Campo.Name: i = i + 1
Next Campo

'cierro el recordset y la conexion, es ese orden:
Record_Set.Close
Conexion.Close
salida:
'destruyo los objetos, para liberar recursos
Set Record_Set = Nothing
Set Conexion = Nothing
End Sub

Y el link a ambos archivos, para que tengan un buen inicio y puedan practicar por su cuenta, modificando la consulta Sql y así ejercitar con esta forma de trabajo.
La próxima entrada estará destinada a perfeccionar este proyecto, mediante la incorporación de un UserForm, permitiendo que el usuario defina los parámetros de búsqueda.