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
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
'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
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:
Para corroborar el correcto funcionamiento... pongamos el formulario a prueba:
presionamos el botón y:
cambiamos el valor en el formulario:
presionamos el botón y:
Ahora seleccionemos otro campo e ingresemos un criterio distinto:
presionamos el botón y:
por las dudas, vemos de ejecutar la consulta con otro criterio:
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 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
'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:
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.
tengo una duda sobre la consulta externa de datos, quisiera que los datos obtenidos se introduzcan directamente en un listbox de un form, sin pasar primero por una hoja. Es posible¿?
ResponderEliminarpor otro lado quisiera que me devolviera unos datos que estan vinculados con otro dato dentro de la base de datos en access, ejemplo: tengo IDusuario y otro que es IDpagador. quisiera que ingresando un IDusuario me devuelva todos los IDusuario vinculados con el mismo IDpagador, es decir que primero busque el IDpagador y luego todos los Idusuario con ese mismo IDpagador y que como te comentaba antes los resultados salgan en un listbox dentro de un form en excel, como se haria¿? soy nuevo en esto de vincular excel y otras aplicaciones
Gracias de antemano
caperu1985@yahoo.es
No sabría decirte como vincular un listbox a a este tipo de consultas. Quizas te convendría:
ResponderEliminar1 hacer la consulta
2 que se vuelque a un rango
3 cargar ese rango en el listbox
4 borrar el rango o la hoja
Y en cuanto a tu consulta sobre los Ids, estimo que deberías plantear primero la consulta desde access, aprovechando su poder relacional
O mira bien la forma en que armo la consulta sql, ahi podes modificarla a tus necesidades, para traer datos de la tabla que necesites.. Personalmente haría la consulta en access y luego la "tomaría" desde Excel.
ResponderEliminarDe verdad, muchas gracias por tus aportes me has ayudado mucho sobre todo con esto, tendré muy en cuenta tus respuestas para poder realizar mi proyecto, ojala hubiera mas gente como tu, sigue con la pagina que está perfecta y de nuevo muchas gracias por todo.
ResponderEliminarcaperu1985@yahoo.es
Gracias estimado por tus palabras. Cualquier ayuda que necesites con el codigo avisame. Generalmente armo la consulta en access y luego la "traigo" desde excel, o bien desde la "vista sql" de access tomo esas lineas para ver como se arman las consultas muy complejas, y las adapto al proyecto.
ResponderEliminar