consulta de datos externos: access --> excel con macros

Para implementar el ejemplo que aquí comentaré, primero deberán leer mi post anterior sobre el tema.

Esta entrada tratará sobre grabar una macro mientras importamos datos, y luego modificar manualmente el código generado por Excel para potenciar futuras consultas.

Son practicamente los mismos pasos, salvo que antes de comenzar con la ejecución de msquery, deberemos ir a Herramientas / Macro / Grabar nueva macro, ingresando un nombre descriptivo:

[+/-] Ver el resto / Ocultar

Y seguir los siguientes pasos, una vez que inició el asistente:

1) buscar la base de datos

2) volcar solamente la tabla "entradas" en la ventana de la derecha:

3) en los dos formularios que siguen (seleccionar criterios de consulta y ordenamiento) dejamos todo como está, solo presionamos "siguiente":

4) en el último paso, seleccionamos la opción de volcar los datos a Excel:

5) ahora elegimos A1 como celda de destino de la consulta:

6) ya tenemos la consulta en excel, ahora detenemos la grabación:

7) luego de todos estos pasos, con Alt + F11 abrimos el editor de Visual Basic, haciendo  doble click sobre el módulo en donde se generó la macro:

en mi caso, la macro se guardó en el "Modulo2", por que ya tenía un módulo creado con anterioridad

8) Ahora llega la parte mas interesante. Veremos que VBA escribió lo siguiente... si, son un par de lineas ininteligibles, pero ya las vamos a acomodar. Existen dos o tres puntos de este código que son fundamentales y modificaremos, los cuales resalto y mas abajo explico.

Sub Access2Excel_Consulta1()
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\1_damian\DiseñoWeb\SitiosLevantados\blogexcel\archivos\base_blog.mdb;DefaultDir=C:\1_damian\Diseñ" _
        ), Array( _
        "oWeb\SitiosLevantados\blogexcel\archivos;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT entradas.Id, entradas.id_etiqueta, entradas.fecha, entradas.comentario" & Chr(13) & "" & Chr(10) & "FROM entradas entradas" _
        )
        .Name = "Consulta desde MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Ruta de acceso al archivo de access: como está nos sirve... pero lo mejor sería colocar allí una variable y concatenarla, para que el día de mañana podamos cambiar esa ruta de acuerdo a nuestras necesidades.

Consulta Sql: es el corazón de todo. Este "lenguaje estructurado de consultas"(creado por IBM en los años setenta) es el que "dice" que información se incluye en la consulta. La sintaxis, en este ámbito, es sencilla: "SELECCIONAR losCamposQueAquiIndico DESDE la tabla entradas ". Fácil.

Si bien soy uno de los que defienden la frase "todo en esta vida se aprende", no crean que Sql es tan sencillo. De hecho, pocas personas pueden jactarse de escribirlo correctamente (entre las cuales no estoy incluido); no olvidemos que esta consulta es muy sencilla y al solo efecto de facilitar el apredizaje.

El código del primer cuadro es el original. Vamos a ver como quedó luego de unos "retoques". Primero creamos una hoja y la llamamos "Panel". En el rango B2 de esa hoja introducimos manualmente la ruta de acceso al archivo mdb

Sub Access2Excel_Consulta1()
'variables de uso interno, para almacenar la dirección del archivo mdb y la consulta sql:
Dim Ruta1 As String
Dim Sql As String

'a la ruta de acceso al archivo mdb la tomo de una celda de excel:
Ruta1 = Trim(Sheets("Panel").Range("b2").Value)
'escribo la siguiente sentencia Sql: igual a la otra, quitando algunos caracteres molestos:
Sql = "SELECT entradas.Id, entradas.id_etiqueta, entradas.fecha, entradas.comentario FROM entradas entradas"

'y aqui comienza la consulta sobre access: observen que resalté cada variable que agregué,
'reemplazando las cadenas fijas y de esta forma hacer que la consulta sea dinámica:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" & Ruta1), Array( _
    ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))
    .CommandText = _
     Array(Sql)
     'nombre que llevará la consulta:
    .Name = "ConsultaDesdeAccess"
    'si muestra los nombres de campos:
    .FieldNames = True
    'numeros de fila
    .RowNumbers = False
    .FillAdjacentFormulas = False
    'si conserva el formato:
    .PreserveFormatting = True
    'si actualiza al abrir el archivo
    .RefreshOnFileOpen = False
    'si la consulta es en segundo plano
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    'si las columnas se ajustaran al tamaño del campo importado:
    .AdjustColumnWidth = True
    'cada cuantos minutos se actualizará la consulta automaticamente
    .RefreshPeriod = 60
    'mantengo la informacion de columnas:
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With
'agrego un mensaje:
MsgBox "Consulta finalizada", vbInformation
[a1].Select
End Sub

el código está bastante comentado, así que no creo que tengan mayores problemas de interpretación. Si ejecutamos la macro, obtendremos el siguiente resultado:

claro... tanto lio... y salvo el cartelito, estamos en la misma.....

9) Si, estamos en la misma. Si nos fijamos bien, salvo el msgbox, la consulta es exactamente la misma a la del punto 6. Pero... ahora viene lo mejor: modificar la variable Sql que introdujimos.

Pensemos que la consulta realizada nos devolvió miles de registros. Y solamente queremos ver todos aquellos cuyo campo "id_etiqueta" sea igual a 2. Entonces simplemente le asignamos lo siguiente a la variable Sql:

Sql = "SELECT entradas.Id, entradas.id_etiqueta, entradas.fecha, entradas.comentario FROM entradas  WHERE entradas.id_etiqueta = 2"

y como ya imaginarán, el resultado es el siguiente: solo trajo los registros cuyo id_etiqueta es = 2

también podríamos definir la siguiente consulta Sql:

Sql = "SELECT entradas.Id, entradas.id_etiqueta, entradas.fecha, entradas.comentario FROM entradas  WHERE entradas.id_etiqueta = 2 OR entradas.id_etiqueta=3 ORDER BY entradas.id_etiqueta ASC"

obteniendo el siguiente resultado:

mediante Sql le ordené: traeme todos los campos de la tabla entradas donde el campo id_etiqueta sea igual a 2 O igual a 3

nos sería factible traer solo determinados campos, al margen del criterio que incluyamos:

ahora solo incluiré los campos: id_etiqueta y comentarios, ordenandole que solo traiga aquellos registros cuyo valor de id_etiqueta sea menor a 3:

Sql = "SELECT entradas.id_etiqueta, entradas.comentario FROM entradas WHERE entradas.id_etiqueta < 3 ORDER BY entradas.id_etiqueta ASC"

y respondió tal cual era lo esperado:

A estas alturas ya estarán imaginando en el poderío de esta forma de trabajo. Como habrán observado, puedo acomodar la consulta Sql a mi antojo, dejando de depender del Asistente de MsQuery y personalizándolas al 100%.

¿Pero no les parece que falta algo? ¿No es medio "pesado" tener que modificar esa variable Sql, a mano, cada vez que necesitamos algún criterio distinto? ¿Por que no hacemos un formulario y desde allí le damos al usuario la posibilidad de ir armando la consulta y luego, presionando un botón, que aparezcan los registros?

Si, de entrada es mas trabajo, pero lograremos una automatización total del proceso.

Hacemos lo siguiente:

creen esta estructura en la hoja "Panel" (ojo, respetando siempre las celdas indicadas):

en B2 la ruta de acceso a la base de access.
en B4 una celda con validacion de datos (con los nombres de los campos)

en B6 una celda para ingresar el criterio de la consulta

Para que este formulario funcione correctamente, deberemos replantear algunas lineas de código. Se agregan un par de controles y distintas consultas Sql según el valor ingresado (texto o número), debiendo prestar especial atención a esto.

Aquí está el nuevo código:

Sub Access2Excel_Consulta1()
'variables de uso interno:
Dim Ruta1 As String
Dim Sql As String
Dim Campo As String
Dim Criterio As Variant

'ya que ahora damos la posibilidad de crear consultas, primero deberemos corroborar que los
'datos se encuentren bien ingresados por el usuario:
If Trim(Sheets("Panel").Range("b2").Value) = "" Then
    MsgBox "Ingrese la ruta de acceso", vbCritical, "Faltan datos"
    [b2].Select
    Exit Sub
ElseIf Trim(Sheets("Panel").Range("b4").Value) = "" Then
    MsgBox "Ingrese el campo en donde se buscará", vbCritical, "Faltan datos"
    [b4].Select
    Exit Sub
ElseIf Trim(Sheets("Panel").Range("b6").Value) = "" Then
    MsgBox "Ingrese el valor de criterio", vbCritical, "Faltan datos"
    [b6].Select
    Exit Sub
End If

'a la ruta de acceso al archivo mdb la tomo de una celda de excel:
Ruta1 = Trim(Sheets("Panel").Range("b2").Value)
'y tambien al campo sobre el que se efectuará el Sql
Campo = Trim(Sheets("Panel").Range("b4").Value)

'bueno, ahora tenemos que renegar un poco con los tipos de datos. en campo "comentario" es texto
'y el campo "id_etiqueta" es numérico, por cuanto la sentencia Sql tiene distinta sintaxis según
'el caso:
If IsNumeric(Sheets("Panel").Range("b6").Value) Then
    Criterio = CInt(Sheets("Panel").Range("b6").Value)
    Sql = "SELECT entradas.id_etiqueta, entradas.fecha, entradas.comentario FROM entradas WHERE " & Campo & " LIKE " & Criterio
Else
    Criterio = Trim(Sheets("Panel").Range("b6").Value)
    'el simbolo % colocado delante y detrás de la variable buscará dentro de todo el campo el criterio ingresado como lo hace la propiedad xlPart del método Find de Excel:
    Sql = "SELECT entradas.fecha, entradas.comentario FROM entradas WHERE " & Campo & " Like '%" & Criterio & "%'"
End If

'agrego una nueva hoja, para colocar allí la consulta y no "pisar" ninguna existente
Sheets.Add
'y aqui comienza la consulta sobre access: observen que resalté cada variable que agregué,
'reemplazando las cadenas fijas y de esta forma hacer que la consulta sea dinámica:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" & Ruta1), Array( _
    ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))
    .CommandText = _
     Array(Sql)
     'nombre que llevará la consulta:
    .Name = "ConsultaDesdeAccess"
    'si muestra los nombres de campos:
    .FieldNames = True
    'numeros de fila
    .RowNumbers = False
    .FillAdjacentFormulas = False
    'si conserva el formato:
    .PreserveFormatting = True
    'si actualiza al abrir el archivo
    .RefreshOnFileOpen = False
    'si la consulta es en segundo plano
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    'si las columnas se ajustaran al tamaño del campo importado:
    .AdjustColumnWidth = True
    'cada cuantos minutos se actualizará la consulta automaticamente
    .RefreshPeriod = 60
    'mantengo la informacion de columnas:
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

'puede que la consulta arroje o nó resultados positivos. para hacerla bien fácil: si existen
'resultados, entonces la celda A2 de la hoja debe tener algun valor. si no lo tiene, procedo
'de otra forma (ya que los criterios ingresados no coincidieron con los datos almacenados)
If ActiveSheet.Cells(2, 1).Value <> "" Then
    MsgBox "Consulta finalizada", vbInformation
    [a1].Select
Else
    'si la consulta no me devuelve resultados, elimino la hoja que agregué al principio:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    MsgBox "No se ubicó el valor '" & Criterio & "' dentro del campo '" & Campo & "'", vbInformation, "Atencion"
    [b2].Select
End If
End Sub

Para corroborar el correcto funcionamiento... pongamos el formulario a prueba:

aqui indico que traiga los registros del campo id_etiqueta, cuyo valor sea = 3

presionamos el botón y:

bien, creo que la imagen habla por si sola

cambiamos el valor en el formulario:

 presionamos el botón y:

nuevamente cumplió con lo ordenado, trayendo a excel todos los registros cuyo valor coinciden con el ingresado en la celda B4

Ahora seleccionemos otro campo e ingresemos un criterio distinto:

le pido que busque en el campo "comentario" aquellos registros que contengan la cadena "macr" en su interior

 presionamos el botón y:

funciona a la perfección.

por las dudas, vemos de ejecutar la consulta con otro criterio:

ahora el criterio es "exc"

 presionamos el botón y:

 creo que no quedan dudas. las consultas con macros, modificadas manualmente, funcionan.

También previmos que la consulta podía devolver un resultado sin registros, por cuanto debíamos eliminar la hoja en donde se colocaría y avisar al usuario. Primero ingresemos cualquier valor en el criterio:

el criterio es un valor inexistente en el campo "comentario"

así que obviamente el resultado será:

Hasta aquí llegamos, momentáneamente, con el tema. Podrán analizar el gran poder de esta forma de trabajo, prescindiendo completamente del entorno de Access y pudiendo a su vez personalizar al 100% las consultas.

En próximos posts veremos estructuras Sql mas complejas, que no detallo aquí para no extender aún mas la presente entrada.