Procedimientos almacenados de Mysql desde Excel Con VBA y ADO (macros)

Siguiendo con la tónica de las últimas entregas, ahora vamos a ejecutar un procedimiento almacenado desde Excel. Para ello haremos uso de ADO y el lenguaje de programación VBA o “Visual Basic for applications”. VBA es el lenguaje que ha integrado Microsoft en la mayoría de los programas que conforman su suite de ofimática. Esta característica es conocida por muchos simplemente como macros.

Para este artículo he usado Microsoft Excel 2010, Mysql 5.1 y el proveedor ODBC que descargamos e instalamos en la entrada: “Ejecutar Stored procedures de Mysql con Delphi y ADO“. Si requieres ayuda para instalar el controlador ODBC y crear el DSN necesario para este ejercicio te recomiendo que leas el artículo que he enlazado.  Es importante comentar que este artículo aplica también para Excel 2000,2003, etc.

Si deseas descargar la hoja de cálculo de este artículo puedes hacerlo en este enlace.

El objetivo de este artículo será, a modo de ejemplo, obtener una lista de países a través de un procedimiento almacenado y copiar esa lista a Excel, por lo que antes de comenzar será necesario crear la Tabla de países

DROP TABLE IF EXISTS pruebas.lista_paises;
CREATE TABLE `lista_paises` (
  `id` int(3) unsigned NOT NULL DEFAULT '0',
  `opcion` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

y el procedimiento almacenado:

DROP PROCEDURE IF EXISTS SP_DAMEPaises;
CREATE PROCEDURE SP_DAMEPaises(
     PV_OPCION VARCHAR(10)
    )
Begin
  IF PV_OPCION = 'DAMETODOS' THEN 
     SELECT ID, OPCION
        FROM PRUEBAS.LISTA_PAISES;
  END IF;

  IF PV_OPCION = 'DAMECINCO' THEN
     SELECT ID, OPCION
        FROM PRUEBAS.LISTA_PAISES
        LIMIT 0,5;
  END IF;

End;

Ahora, crearemos una nueva hoja de cálculo en Excel y una vez creada seleccionaremos la pestaña “Programador” y en ella daremos click en el botón “Insertar” para desplegar los diferentes controles que podemos insertar en nuestras hojas de cálculo. De estas opciones daremos click en “Botón (control de formulario)”:

 

Al seleccionar el control, daremos click en nuestro formulario entre la columna “C” y la fila 3. En ese momento aparecerá un cuadro de diálogo solicitándonos definir el nombre de nuestra macro:

El nombre que vamos a definir aquí es: “Btn_EjecutaSpMysql” y después presionaremos el botón “Nuevo”. Esto nos creará el botón en nuestra hoja de cálculo y además nos abrirá el editor de VBA para programar nuestra macro. Por el momento volveremos a nuestra hoja de cálculo. Seleccionamos el botón que hemos creado y daremos click con el botón derecho del mouse sobre él y del menú contextual seleccionaremos “Modificar texto”.  De esta manera podremos redefinir su etiqueta a “Ejecuta Sp”.

Ya que hemos establecido la etiqueta de nuestro botón comenzaremos a programar nuestra macro.

Si has programado en Vb o Vb.Net, la sintaxis de las macros te parecerá sumamente familiar por lo que no tendrás ningún problema para comenzar a codificar en este lenguaje. Si no estás familiarizado con Visual Basic no te preocupes, las estructuras de control y bloques procedurales son muy sencillos de aprender.

Para comenzar a programar nuestra macro volveremos a nuestro editor de Visual Basic para aplicaciones, si recuerdas, es la ventana que se mostró una vez que creamos el botón en nuestro formulario. Al regresar al editor lo primero que podrás observar es el siguiente bloque procedural :

Sub Btn_EjecutaSpMysql()

End Sub

La siguiente imagen nos muestra la pantalla que deberemos estar visualizando:

El código que ejecutará nuestro botón ha sido creado por Excel, dentro del bloque sub end sub colocaremos el código necesario para ejecutar nuestro procedimiento almacenado y devolver el resultado a la hoja de cálculo. Como había comentado al inicio, este artículo versará sobre la ejecución de procedimientos almacenados haciendo uso de ADO. En excel, no es posible usar ADO por defecto, primero debe crearse la referencia a su Activex. Para ello en nuestro editor de aplicaciones seleccionaremos el menú “Herramientas / Referencias” y se mostrará un cuadro de diálogo con una lista de controles que podemos referenciar para poder ser usados en nuestras hojas de cálculo. En esta lista buscaremos  “Microsoft Activex Data Objects 2.8 library” y lo habilitaremos como se muestra en la pantalla siguiente:

Presionamos aceptar y de esta manera podremos ahora si usar ADO para conectarnos a Mysql y ejecutar un procedimiento almacenado.

Una vez que hemos habilitado ADO en Excel comenzaremos a programar nuestra macro o rutina Vba. Pero primero creamos el procedimiento almacenado que ejecutaremos:

Creado nuestro procedimiento almacenado comenzaremos a programar: lo primero por hacer será declarar los objetos que nos permitirán conectarnos a mysql, ejecutar el procedimiento almacenado y obtener el Rescordset resultante.

  Dim CMDStoredProc As ADODB.Command  'Objeto para ejecutar el procedimiento almacenado
  Dim CnnConexion As ADODB.Connection 'Objecto conexión
  Dim RcsDatos As ADODB.Recordset 'Objeto que recibirá el RcsDatos resultante

Luego vamos a definir las variables que almacenaran la cadena de conexión (esta es igual a la usada en Delphi), el número de fila y el número de filas afectadas por el procedimiento almacenado:

  Dim CadConexion As String  'Cadena de conexión
  Dim Row As Integer 'contador de fila en excell
  Dim RecordsAffected As Long  'Filas afectadas

Definimos nuestra cadena de conexión:

CadConexion = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MyLocalDb"

Creamos las instancias de nuestros objetos ADO:

  Set CnnConexion = New ADODB.Connection
  Set RcsDatos = New ADODB.Recordset
  Set CMDStoredProc = New ADODB.Command

Establecemos comunicación con nuestro servidor, Enlazamos nuestros objetos y definimos el procedimiento almacenado a ejecutar

CMDStoredProc.CommandType = adCmdStoredProc
Call CnnConexion.Open(CadConexion)
Set CMDStoredProc.ActiveConnection = CnnConexion
CMDStoredProc.CommandText = "PRUEBAS.SP_DAMEPaises"

Creamos los parámetros de entrada del procedimiento almacenado y además definimos su valor. Hecho esto ejecutamos y obtenemos el Recordset resultante:

  Call CMDStoredProc.Parameters.Append(CMDStoredProc.CreateParameter("PV_OPCION", _
DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 10))
  CMDStoredProc.Parameters("PV_OPCION").Value = "DAMETODOS"
  Set RcsDatos = CMDStoredProc.Execute(RecordsAffected, , ExecuteOptionEnum.adAsyncFetch)

En este punto hemos obtenido un Recordset conteniendo una lista de paises. Ahora lo recorreremos y copiaremos los valores a Excel:

  Row = 1
  Do While Not RcsDatos.EOF
    Cells(Row, 2).Value = RcsDatos.Fields(1).Value
    Row = Row + 1
    RcsDatos.MoveNext
  Loop

Y con esto, tendremos una macro que ejecuta un procedimiento almacenado de Mysql y copia el resultado a Excel. Las posibilidades de esto son infinitas pues podrían enriquecer sus hojas de cálculo con listas o Select Box conteniendo valores extraídos de las tablas de Mysql o todo aquello que se les ocurra.

Como pueden ver en la siguiente imagen, al presionar el botón “Ejecuta sp” se copiaran a Excel los valores contenidos en la tabla “Lista_paises”


y por último aquí tienen el bloque completo de la macro:

Sub Btn_EjecutaSpMysql()
  Dim CMDStoredProc As ADODB.Command  'Objeto para ejecutar el procedimiento almacenado
  Dim CnnConexion As ADODB.Connection 'Objecto conexión
  Dim RcsDatos As ADODB.Recordset 'Objeto que recibirá el RcsDatos resultante

  Dim CadConexion As String  'Cadena de conexión
  Dim Row As Integer 'contador de fila en excell
  Dim RecordsAffected As Long  'Filas afectadas

  'establecemos nuestra cadena de conexión
  CadConexion = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MyLocalDb"
  ' creamos las instancias de nuestros objetos
  Set CnnConexion = New ADODB.Connection
  Set RcsDatos = New ADODB.Recordset
  Set CMDStoredProc = New ADODB.Command

  'Establecemos comunicación con nuestro servidor Mysql y la asignamos al objeto CMDStoredProc
  Call CnnConexion.Open(CadConexion)

  'Enlazamos nuestros objetos y definimos el procedimiento almacenado a ejecutar
  CMDStoredProc.CommandType = adCmdStoredProc
  Set CMDStoredProc.ActiveConnection = CnnConexion
  CMDStoredProc.CommandText = "PRUEBAS.SP_DAMEPaises"

  'Creamos el parámetro del procedimiento almacenado
  Call CMDStoredProc.Parameters.Append(CMDStoredProc.CreateParameter("PV_OPCION", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 10))
  'establecemos el valor del parámetro
  CMDStoredProc.Parameters("PV_OPCION").Value = "DAMETODOS"
  'ejecutamos el procedimiento almanceado y establecemos el RcsDatos resultante
  Set RcsDatos = CMDStoredProc.Execute(RecordsAffected, , ExecuteOptionEnum.adAsyncFetch)
  'recorremos el recordset resultante y copiamos el contenido a excel
  Row = 1
  Do While Not RcsDatos.EOF
    Cells(Row, 2).Value = RcsDatos.Fields(1).Value
    Row = Row + 1
    RcsDatos.MoveNext
  Loop

End Sub

Espero sea de utilidad. Sus comentarios sérán apreciados

6 Comments

  1. Estimado Amigo
    Antes que nada agradecere por la deferencia en la publicacion de tan valioso archivo, si bien aca muestras en detalle la conexion para MySql, tengo la inquietud de ejecutar un sp desde excel para insertar filas a una tabla de SQL, reccorrer las filas, es decir
    Columna A=FICHA
    Columna B=Nombre
    Columna C=Periodo
    Columna D=Monto
    El SP lo tengo creado y esta operativo, solo me falta dilusidar como lo ejecuto en excel, teniendo encuenta que los datos los tengo desde la fila 1 a la n y debo insertar las columnas A,C,D , la B no porque es solo referencial.
    el SP queda asi exec PA_ANTICIPOS ‘8955464’,’20130228′,’1000000′
    Agradezco infinitamente tu ayuda

    Saludos

    Carlos

    • Hola carlos,

      El procedimiento descrito en esta entrada aplica también para guardar información usando Sps. si observas el bloque:

      ‘establecemos el valor del parámetro
      CMDStoredProc.Parameters(“PV_OPCION”).Value = “DAMETODOS”

      puedes establecer el valor a enviar de la manera:

      CMDStoredProc.Parameters(“PV_OPCION”).Value = Range(“ANumFila”).Value

      y si en tu caso el procedimiento no devuelve un recordset puedes ejecutarlo de la siguiente manera:
      Call CMDStoredProc.Execute(RecordsAffected, , ExecuteOptionEnum.adExecuteNoRecords)

1 Trackback / Pingback

  1. » Procedimientos almacenados en PostgreSql

Deja un comentario