Una manera de optimizar nuestro trabajo y simplificarlo aun más es poder lanzar consultas de bases de datos desde nuestros excel para poder automatizar lo más posible aquellos informes que necesitemos realizar. Para ello en esta ocasión he estado preparando un ejemplo de cómo sería ejecutar consultas MySQL desde una macro excel con ODBC.
Pincha en la imagen para ver más detalles sobre este libro
Tabla de contenidos
Situación de partida
Voy a detallar la situación de partida del entorno en el que he conseguido realizar la prueba, ya que cualquier cosa que no coincida puede ser motivo de que no funcione como esperemos.
El ejemplo que voy a mostrar es recuperar todos los tickets de un Redmine que tengo instalado en un Xampp sobre Windows 7. Tanto Redmine como su base de datos están sobre ese Xampp.
- Windows 7
- Xampp
- Redmine
- Conector MySQL-ODBC de 32 bits
- Conexión de ODBC configurada en Windows
Una cosa que hay que tener en cuenta es que el conector de ODBC con MySQL a mi no me funcionó el de 64 bits. Solo me ha funcionado con el conector de 32 bits. Podéis encontrar el conector en el siguiente enlace y necesitáis instalarlo en vuestro Windows.
Configurar conexión ODBC para MySQL
Antes que nada debéis instalar el conector ODBC para MySQL que os indico en el paso anterior. Una vez instalado hay que configurar la conexión que vamos a establecer con nuestra base de datos introduciendo los datos de nuestro servidor, usuario y contraseña. Desde esa misma ventana podemos pulsar sobre un botón de Test para verificar que tenemos conectividad con la base de datos.
Para hacer todo esto nos vamos al Panel de Control de Windows y buscamos ODBC:
Nos vamos a la pestaña DSN de usuario y Agregamos una nueva entrada que hemos llamado en este caso "mysql":
En esta nueva entrada le configuramos todos los datos de nuestro servidor de base de datos de MySQL. Como lo tenemos instalado en Xampp en local, ponemos localhost y los datos de nuestra base de datos.
Si pulsamos sobre el botón Test debe mostrarnos un mensaje de conexión establecida correctamente.
Preparar nuestro informe excel
Ahora vamos a preparar nuestro informe excel. Para ello, creamos un archivo Excel compatible para ejecutar Macros de Visual Basic. Para ello creamos un nuevo excel y lo guardamos como XLSM.
Abrimos la ventana de edición de macros y pegamos nuestra macro.
Cosas que podemos personalizar de esta macro de ejemplo:
Acordaros de editar la variable ConnectionStringMySQL con los datos de vuestra BBDD y vuestro servidor para que se pueda conectar correctamente. Por algún motivo, parece que hay que ponerlo en los dos sitios, aquí en el DSN de la configuración OBDC de Windows.
En la variable SQL tendremos que poner la SQL que queremos lanzar sobre nuestra base de datos.
Al final de la macro podéis ver que vamos a volcar el resultado de la macro sobre la hoja 1 de nuestro excel y en la celda A2. Estos datos los podemos ajustar a nuestras necesidades.
Aquí os dejo el código para que podáis reutilizarlo:
Sub Ejecutar_SQL_en_MySQL()
'Inicialización de variables
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ConnectionString As String
Dim sql As String
'Cadena de conexión con una BBDD de MySQL
ConnectionStringMySQL = "DSN=mysql;" & _
"DRIVER={MySQL ODBC 5.3 ANSI Driver (32 bit)};" & _
"SERVER=localhost;" & _
"DATABASE=redmine3;" & _
"USER=root;" & _
"PASSWORD=root;" & _
"OPTION=3;"
'Abrir conexión con la BBDD
con.Open ConnectionStringMySQL
'Timeout en segundos para ejecutar la SQL completa antes de reportar un error
con.CommandTimeout = 900
'Esta es la SQL que queremos consultar
sql = "SELECT * FROM issues"
'Lanzamos la SQL
rs.Open sql, con
'Copiamos los resultados de la SQL sobre la primera hoja del Excel en la celda A2
Sheets(1).Range("A2").CopyFromRecordset rs
'Cerramos las conexiones
rs.Close
con.Close
End Sub
Ahora tenemos que configurar nuestro excel para que pueda establecer conexión ODBC con nuestra base de datos MySQL. Desde el editor de Visual Basic nos vamos a Herramientas > Referencias:
Aquí debemos activar Microsoft ActiveX Data Objects 2.8 Library para poder establecer conexión ODBC con MySQL:
Pulsamos OK y con esto ya debería de funcionar todo.
Ejecutar informe
Una vez que ya tenemos la conexión ODBC configurada, las propiedades de nuestra base de datos con nuestro servidor, SID, usuario y contraseña establecidos para conectarnos a la BBDD desde la macro, nuestro Xampp levantado y nuestro redmine también, ya podemos ejecutar la macro para que acceda a los datos de nuestra aplicación y los vuelque en nuestro excel.
No es obligatorio que sea un Redmine. Yo he usado un Redmine por coger un ejemplo cualquiera. Podemos atacar a cualquier base de datos que tengamos y lanzar la SQL que queramos.
Para lanzar la macro podemos hacerlo de dos maneras:
- Pulsando el botón Play que aparece en el editor de Macros:
Ejecutar la macro pulsando el botón PLAY
- Pulsando el botón Run que aparece en la ventana de procedimientos de Visual Basic de nuestro Excel:
Ejecutar la macro pulsando el botón RUN
Al ejecutar la macro todo debería funcionar correctamente y debería mostraros los datos de la SQL sobre la celda A2 de la primera hoja de nuestro excel como podemos ver aquí:
Si no hubiera funcionado y nos aparece alguno de los errores siguientes es porque debéis revisar los datos de conexión con la base de datos o porque no habéis instalado correctamente el conector de 32 bits. El conector de 64 bits a mi no me funcionó y fue uno de los principales problemas que tuve.
Posibles errores
Este mensaje de error nos indica que hemos indicado mal la DSN que establece la conexión con el servidor de BBDD. Hay que revisar la variable ConnectionStringMySQL.
No se encuentra el nombre del origen de datos y no se especificó ningún controlador predeterminado.
En este otro error nos indica que nuestro conector de MySQL no es de la misma arquitectura que la configuración de ODBC realizada. Eso es porque instalé el conector de 64 bits sabiendo que mi Windows era de 64 bits, pero que al parecer el ODBC no se entiende muy bien con los 64 bits y lo solucioné instalando la versión de 32 bits.
The specified DSN contains an architecture mismatch between the Driver and Application
Pincha en la imagen para ver más detalles sobre este libro
Conclusiones
Bueno! Pues esta es una manera de poder lanzar consultas de MySQL desde un macro Excel para poder generar informes más complejos o automatizar ciertas partes del informe para no tener que actualizarlas manualmente si se tratara de informes mensuales o periódicos.
Recuerda compartir este contenido para ampliar el conocimiento de Excel entre toda la comunidad y que así aprendamos a sacarle beneficio a esta estupenda herramienta de Microsoft.
¡Hola! Soy Javier. Soy informático y me gusta documentar todo lo que aprendo. En mi blog te enseño todo lo que sé y cómo puedes aprender a crear tu propia web, mejorar el SEO y aplicar técnicas de Marketing Digital para conseguir mayor visibilidad. Suscríbete para no perderte ninguna de mis guías sobre WordPress, posicionamiento SEO o Marketing Digital.
Muy bueno. Lo implementé con postgresql por si te sirve:
1. instalar driver postgresql odbc (en mi caso uso x86 y Unicode)
2. crear la conexion odbc (cree un usuario que solo puede consultar una vista, por seguridad)
3. Le añadí un inputBox a tu codigo para pasarle el parametro del where a la vista.
4. referencia a la hoja con el resultset obtenido, desde otras hojas.
Todo esto para ingresar datos de la bd a un libro de excel ya creado con calculos y varias sheets.
Lo intenté con Apache POI pero resulto un dolor de cabeza el manejo de xlsx
Saludos!
Muchas gracias David. Saludos!
Muchas gracias, Javier. Llevaba muchísimo tiempo queriendo hacer esto, y al leer tu post funcionó.
Muchas gracias, Javier.
Genial! Saludos 😉
buen dato.
pregunta necesito hacer esto en una mac,
crees que se pueda?
Saludos!
Hola Antonino. No te sé decir porque no he trabajado nunca con MAC. Un saludo, Javier.
Muchas gracias por compartir tu conocimiento.
Me ha servido mucho.
Muchas gracias por tu feedback 😉
Saludos, es correcto, funciona!!!, pero.... si quiero consultar más de una tabla? 😀
Antonio
Gracias por tu testimonio Antonio. Para consultar varias tablas, escribe otra SQL y la lanzas de nuevo y ya la tratas como quieras. Saludos!
Hola Javier,
Tú post me ayuda bastante, pero, ¿cómo puedo hacer para considerar también las columnas o campos?
¡Gracias!
Hola Daniel. No entiendo muy bien tu pregunta. En la macro puedes modificar la SQL e indicar las columnas que quieres recuperar. Pero no sé si te refieres a eso. Un saludo.
Buenos días Javier. He implementado tu procedimiento y funciona.
He accedido a una BBDD de WP en un servidor WEB remoto y también funciona.
Pero tengo un problemilla, no me recupera todas las columnas , ¿alguna idea de por qué?
Gracias.
Un saludo Eduardo
Hola Eduardo. Pon en la sql todos los campos que quieres recuperar, a ver si te sale así. Un saludo.
El mejor post que he visto, fue de gran ayuda.
Gracias
Muy bueno, era lo que estaba buscando. Muchas gracias
Hola Javier, muy interesante tutorial.
Tengo una pregunta esto se puede hacer para subirlo a un sitio web.
Buenos días. Tendría que ejecutarse la macro en el servidor y después que se descargara el excel con los datos ya obtenidos. Yo lo he visto en una aplicación en la que trabajé hace unos años que hacía eso, pero no sé cómo estaba implementado realmente porque esa parte del código estaba cerrada (formaba parte de una aplicación comercial). En teoría se debe de poder hacer, pero no sé cómo. Un saludo.
Hola Javier, me gustó mucho tu tutorial. Sin embargo, me preguntaba si hay una manera de hacer un sub o function para cada tipo de query, es decir, uno para eliminar, otro para actualizar y otro para insertar.
De ser así, ¿cómo hago para ahorrarme las lineas de codigo de la conexión con el fin de no volverlas a poner en cada sub?
Saludos
Sí María. Tu puedes modular el código todo lo que quieras. Modular significa crearte funciones específicas, una para conectarse a BBDD, otra para consultar datos, otra para insertar datos, etc. Desde la función principal tu defines el hilo de tu programa y vas llamando a las funciones según te interese. Solo tienes que crear un SUB nuevo debajo de ese con un nuevo nombre, y desde el sub principal lo llamas. Un saludo.
Sí, he intentando varias maneras, pero no logró efectuarlo. Por favor ¿podrías tomar este mismo ejemplo y dividir el query de selección en otro sub?
¡Te lo agradecería mucho!
No crees que por seguridad no debería ir el usuario y password en la cadena de conexión?
Es un ejemplo práctico para que conozcáis como funciona. En un proyecto de verdad, como bien dices, el usuario y contraseña iría en un fichero properties al que solo tendría acceso el administrador de sistemas. Un saludo.
Hola de nuevo, lo siento otra vez por el repost, pero no sé cómo eliminar mi anterior comentario, solo quería decirte que ya lo logré hacer.
¡muchísimas gracias!
Hola Javier, te comento que implementé tu solución y funcionó muy bien inclusive con selects de varias tablas, para que sea completa la solución, ¿sabes como desplegar los nombres de los campos en el Excel?, esto para no tener que estar manipulando la data desplegada.
Muchas gracias
Es algo que yo suelo echar en falta, pero siempre me da problemas porque nunca recuerdo cómo se hace. Sé que se meten los valores en una hoja a parte, y ahí se crea el vínculo, pero después nunca me ha funcionado bien. No te sé decir cómo se hace porque la verdad es que no lo llego a usar. Un saludo.
Hola,,
Gracias por el tutorial, muy bueno.
Tengo una pregunta...¿De qué manera puedo hacer quede el usuario y la contraseña y la ip de mi hosting dentro del código vba?
¿para qué configuro la conexión odbc en Windows si también debo colocar los datos dentro de vba?
De hecho,, tengo un aplicativo en donde no necesito configurar el odbc en windows,,, la macro corre sin problema.
Me gustaría colocar ese nombre de la conexión dentro del vba y que con ese nombre la macro se conecte exitósamente al hosting.
Muchas gracias!
No te puedo decir mucho más. Esto es una prueba que hice hace muchos años y no he vuelto a usarlo. Un saludo.