Aprender a crear funciones en Excel utilizando Mysql

En esta oportunidad detallaremos paso a paso como crear una función en Excel utilizando Mysql como motor de base de datos.

Antes de comenzar sino tienen conocimientos en Mysql les recomiendo leer el tutorial anterior Importar datos desde Mysql con Excel 2010

Comenzaremos diciendo que es necesario poseer una conexión ODBC, ya que la utilizaremos para realizar nuestras funciones.

Estas conexiones las podemos encontrar en Panel De Control -> Herramientas Administrativas -> Orígenes de datos ODBC (32 bits)

odbc mysql

 

Es importante que posean un ODBC de 32 Bits ya que en caso contrario no funcionará. Si no saben como crear un ODBC recuerden ver el tutorial Importar datos desde Mysql con Excel 2010,

Por el momento solo es importante que sepan donde encontrar esta pantalla.

Iniciamos Microsoft Excel

Ya solo nos falta un paso para comenzar a programar nuestras funciones usando Mysql, y este paso es crear la conexión a Mysql desde VBA (Visual Basic Aplication), básicamente es un programador simplificado.

Para acceder a este programador podemos presionar las teclas Alt +F11. 

alt_f11

Ya estando en el Editor VBA, veremos un panel al costado izquierdo donde debemos abrir VBAProject(Libro1)  -> Presionar Clic Derecho -> insertar -> Módulo. 

Insertar_modulo

Esta pantalla que vemos es el editor VBA, en el cual podemos comenzar a programar nuestras Funciones.

Crear nuestra Función de Conexión a Mysql

Declaramos las variables publicas que utilizaremos

Public Conn As ADODB.Connection ' Permite conectarnos a la base de datos
Public Rs As ADODB.Recordset 'Almacena el resultado de nuestras consultas

Agregamos siguientes Referencias al proyecto.

 Microsoft ActiveX Data Objects 2.0 Library

Microsoft DAO 3.6 Object Library 

Para agregar ir a Herramientras- > Referencias y buscar en la Lista.

referencias_mysql_Excel

Copiamos la siguiente Función modificando nuestros datos de Conexión a Mysql

Private Function Conectar() As Boolean
On Error GoTo Ver

Private Function Conectar() As Boolean
On Error GoTo Ver

Set Conn = New ADODB.Connection
Conn.ConnectionString = "driver={MySQL ODBC 5.2 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Database=quality;" & _
"User=root;" & _
"Password=1234;" & _
"Option=3;"

Conn.Open

Conectar = True

Exit Function
Ver:
Err.Clear
Conectar = False
End Function

Los datos a modificar son los datos que vimos al comienzo de nuestro ODBC :

driver = MySQL ODBC 5.2 ANSI Driver (Modificar en caso de que utilicen otra versión del driver)

Server = localhost (IP o nombre de vuestro Servidor de Mysql)

Port = 3306 (cambiar solo si modificaron el puerto de Mysql )

Database = quality (nombre de la base de datos a la cual quieren conectarse por defecto)

User = root (poner el usuario con permisos creado en Mysql)

Password= 1234 (Poner la contraseña del usuario anterior)

Option = 3 (No modificar)

 

Probar Función de Conexión a Mysql

Con el fin de saber el paso anterior esta correcto

Copiamos el siguiente método

Private Sub PruebaConexionMysql()

If (Conectar) Then

MsgBox "Conexión Exitosa"

Else

MsgBox "Conexión Fallida"
End If

End Sub

Para comprobar el resultado hacemos clic sobre el método  y presionamos F5 o clic sobre el botón de Ejecutar.

prueba_Conexion

 

conexion_exitosa

 

En el caso de les aparezca el mensaje de Conexión Fallida, deberán revisar los datos de la conexión.

 

Crear Función para ser utilizada desde Excel

Crearemos una función que dado el Rut de un cliente retorne el Nombre del Cliente. 

Para este ejemplo he creado una tabla en Mysql llamada Cliente y le he agregado solo 3 Clientes.

tabla_clientes_mysql

Creando nuestra Función paso a paso …

Public Function ObtenerNombreCliente(ByVal rut As String) As String

Public = para poder ser vista desde Excel

Function = indica que es una función por lo tanto debemos retornar un valor

ObtenerNombreCliente = Es el nombre que hemos asignado a nuestra Función

(ByVal rut As String)  = Indica que esperamos recibir un string(texto) por parametro

As String = Tipo de valor que devolverá la función

On Error GoTo NoEncontrado

Esta sentencia nos permite controlar cualquier error que ocurra en nuestra función, en caso de ocurrir un error automáticamente se dirigirá hasta la sentencia NoEncontrado : sentencia que encontraremos más abajo en el código.

Dim nombre As String  = variable utilizada para almacenar el nombre del cliente o el mensaje que devolveremos.
Dim query As String = variable utilizada para la consulta hacia la base de datos.

nombre = «No Encontrado» = Asignamos un valor por defecto al nombre para devolver en caso de ocurrir cualquier error.

If (Conectar) Then = Llama a nuestra función conectar y pregunta si el resultado es True.

query = «SELECT nombre FROM cliente WHERE » & rut & » LIMIT 1″  = Asignamos nuestra consulta Sql a la variable y le agregamos concatenamos la variable rut enviado por parámetros. Además agregamos LIMIT 1 ya que solo nos interesa retornar la primera coincidencia.

Set Rs= New ADODB.Recordset = Limpiamos la variable Rs incializandola nuevamente con la sentencia SET

Rs.Open query, Conn, adOpenStatic, adLockOptimistic = Esta sentencia nos permite Ejecutar nuestras consultas Sql y almacenar el resultado en la variable Rs.

nombre = Rs.Fields(0) =  Asignamos el primer valor devuelto por la consulta.

ObtenerNombreCliente = nombre  = Devolvemos el resultado asignando el valor al nombre de la función

End If = Cerramos la sentencia If.

NoEncontrado: = Esta sentencia indica el punto de salto del programa en caso de cualquier error. Esto quiere decir que si la función falla en cualquier punto antes de esta sentencia automáticamente sera redireccionado hasta este punto ejecutando todo lo que continua bajo ella.

ObtenerNombreCliente = «No Encontrado»  = Devolvemos la palabra No Encontrado ya que ocurrió alguna falla durante la función.

End Function = Por ultimo finalizamos la Función

Siguiendo los pasos anteriores deveriamos tener lo siguiente:

Public Function ObtenerNombreCliente(ByVal rut As String) As String

On Error GoTo NoEncontrado
Dim nombre As String
Dim query As String

nombre = "No Encontrado"

If (Conectar) Then

query = "SELECT nombre FROM cliente WHERE rut = '" & rut & "' LIMIT 1"
Set Rs= New ADODB.Recordset

Rs.Open query, Conn, adOpenStatic, adLockOptimistic
nombre = Rs.Fields(0)

ObtenerNombreCliente = nombre

End If

NoEncontrado:

ObtenerNombreCliente = "No Encontrado"

End Function

 

Probando nuestra Función desde Excel

verificar_funcion1

verificar_funcion2

verificar_funcion3

Saludos a todos, esperamos que les haya quedado claro con este tutorial.

También te podría gustar...

20 Respuestas

  1. Jorge Armando dice:

    Hola Victor, ya hice varias pruebas, primero adaptandolas a mis tablas, pero al ver que no funcionaba creé todo como lo comentas (base de datos con la tabla cliente y todo), funciona casí todo, compruebo la conexión y funciona, pero después de ejecutar el query en la línea
    rs.Open query, oConn, adOpenStatic, adLockOptimistic
    encuentra algún error (que no sé como verlo) y se brinca a NoEncontrado:
    Lo que sí es que no sé si por la versión del Excel «2013» no me accepta el Conn.ConnectionString y en su lugar utilizo el
    Set rs = New ADODB.Recordset
    Set Conn = New ADODB.Connection
    Conn.Open «DRIVER={MySQL ODBC 5.1 Driver};» & _
    «SERVER=sr00003;» & _
    «DATABASE=test;» & _
    «USER=agonzalez;» & _
    «PASSWORD=Hr43Lk9y;» & _
    «Option=3»

    Podrías apoyarme, tengo días y no puedo resolverlo.
    Incluso tengo otros SUB donde hago inserts en la base de datos sin problema, sólo quiero antes de hacer el insert verificar que registro no exista, y saber que no pudo insertarlo.

    Saludos y gracias de antemano.

    • Jorge buenas tardes, si ya logras conectarte a la base de datos y fallan las consultas lo más provable es que tengas errores de sintaxis, generalemente ocurre que no se respetan los espacios en las palabras claves, pero vba en su interfaz muestra que esta correcto, te aconsejo imprimir tu query en una celda antes de ejecutarla y revisar la sintaxis. Si el problema persiste puedes mandarme el código y te ayudo a resolverlo.

  2. Wilmer dice:

    Si pudieras ayudarme te lo agradezco

    • Bueno lo que sucede es que el echo de hacer tablas dinámicas va a depender 100% de la librería que utilices, voy a revisar que libreria se acomoda mas a tu requerimiento y te comento.

      Saludos

      • Wilmer dice:

        Vale, estaré pendiente

        • Estuve leyendo algo al respecto y por mas que busque información todos coinciden en que php no tiene buenos complementos para trabajar con excel, podrías investigar con phpexcel, lo lamento pero este tutorial fue echo para trabajar directamente desde excel con macros.

  3. Wilmer dice:

    Lo digo por que vi tu tutorial sobre como importar de mysql a excel 2010 y pues bueno estoy pensando implementar ese si aun sirve pero quiero poder hacer todo lo que te digo

  4. Wilmer dice:

    Aun no he comenzado a usar ninguna libreria pero he leido sobre phpExcel, es como debo cerrar el programa que estoy realizando, o sea aun no comienzo a desarrollar con él; Pero quiero estar preparado. Me refiero a una tabla dinamica en excel, O sea por ejemplo en una hoja con una tabla en donde estan todos los productos para una venta y los que estan acargo, esta es una tabla dinamica donde puedo elegir como quiere que se ordene dependiendo de la fila o columna que se elija y en la otra hoja esta el resumen solo con el nombre del responsable en una tabla dinamica donde puedo chulear que informacion quiero mostrar de cada vendedor pues. espero que me hayas entendido y de antemano gracias

  5. Wilmer dice:

    Victor lo que necesito hacer es:
    1 importar un excel a mysql (opcional ya que podrian ingresar los datos directamente a mysql, aun no lo decido si es o no necesario este punto)
    2 desde mysql con php crear un excel con los datos de la semana que quiera elegir mediante consultas, donde me muestre en una sola tabla de excel la informacion de diferenes tablas de la base de datos.
    3 y finalmente que estas tablas que exporte al excel sean dinamicas, y pueda desde el excel ordenarlo segun lo requiera.
    Si me puedes ayudar en cualquiera de los puntos que debo hacer te lo agradeceria inmensamente, gracias.

    • Que herramientas estas usando para generar el excel? (phpExcel, phpOffice, etc…). cuando hablas de dinámica, te refieres a una tabla dinámica de Excel o más bien a que la tabla puede variar?

  6. Wilmer dice:

    Victor, necesito generar archivos excel desde mysql, que ese excel tenga varias hojas y que todas sus tablas sean dinamicas. Como puedo generar los archivos excel con tablas dinamicas?

  7. oscar dice:

    Buenas! gracias por el tutorial, me ha servido de gran gran gran ayuda.
    Sin embargo me gustaria preguntar como se guarda el proyecto xk en mi caso aunque de a guardar, cada vez que abro excel tengo que volver a crear modulo, abrir librerias, copiar el codigo y demas, vamos que no se queda nada guardado.

    Saludo!!

    • Tienes que crear tus funciones y exportarlas en un proyecto tipo complemento. Revisa esta página http://exceltotal.com/como-crear-un-complemento-de-excel/
      Saludos

      • oscar dice:

        Gracias man! ya he solucionado el problema

        Saludos!

      • victor ahora cuando escribe la funcion creada en excel y te devuelve una resultado #valor! el tipo de dato de rut tiene que ser tipo int necesariamente si fuera varchar trae problemas

        • La verdad no tendrías que tener problemas mientras realices correctamente la función, si gustas puedes copiarme tu código con error para poder ayudarte de mejor manera.

          saludos

          • Estimado te paso el codigo, puedes darme tu correo, el set rs lo puse antes del select, por otro la funcion creado tengo que poner el intervalo de numeros necesariamente,
            On Error GoTo NoEncontrado
            Dim tas_area As String
            Dim query As String

            tas_area = «No Encontrado»

            If (Conectar) Then
            Set rs = New ADODB.Recordset
            query = «SELECT tas_area FROM tipo_area_servicio where» & tas_tiposerv & «limit 1»
            rs.Open query, conn, adOpenStatic, adLockOptimistic
            tas_area = rs.Fields(0)

            ObtenerNombreCliente = tas_area

            End If

            NoEncontrado:

            ObtenerNombreCliente = «No Encontrado»

            End Function

          • Reemplaza : «SELECT tas_area FROM tipo_area_servicio where» & tas_tiposerv & «limit 1» Por «SELECT tas_area FROM tipo_area_servicio where tas_tiposerv = ‘» & tas_tiposerv & «‘ limit 1».

            Disculpa por las molestias.

  1. noviembre 15, 2013
  2. noviembre 29, 2013

    […] Para este ejemplo utilizare las funciones creadas en el post anterior Aprender a crear funciones en Excel utilizando Mysql. […]