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.

Libro de Excel 2019Pincha en la imagen para ver más detalles sobre este libro

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.

Ejecutar consultas MySQL desde una macro excel con ODBC - Descarga conector MySQL ODBC

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:

Ejecutar consultas MySQL desde una macro excel con ODBC - Configuracion ODBC 1

Nos vamos a la pestaña DSN de usuario y Agregamos una nueva entrada que hemos llamado en este caso "mysql":

Ejecutar consultas MySQL desde una macro excel con ODBC - Configuracion ODBC 2

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.

Ejecutar consultas MySQL desde una macro excel con ODBC - Configuracion ODBC 3

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.

Ejecutar consultas MySQL desde una macro excel con ODBC - 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:

Ejecutar consultas MySQL desde una macro excel con ODBC - Configuracion ODBC 4

Aquí debemos activar Microsoft ActiveX Data Objects 2.8 Library para poder establecer conexión ODBC con MySQL:

Ejecutar consultas MySQL desde una macro excel con ODBC - Configuracion ODBC 5

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 consultas MySQL desde una macro excel con ODBC - Ejecutar macro excel 2

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 consultas MySQL desde una macro excel con ODBC - Ejecutar macro excel 1

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í:

Ejecutar consultas MySQL desde una macro excel con ODBC - Resultado

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.

Ejecutar consultas MySQL desde una macro excel con ODBC - Error

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

Ejecutar consultas MySQL desde una macro excel con ODBC - Error 2

Libro de Excel 2019Pincha 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.

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.

ACEPTAR
Aviso de cookies