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)
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.
Ya estando en el Editor VBA, veremos un panel al costado izquierdo donde debemos abrir VBAProject(Libro1) -> Presionar Clic Derecho -> insertar -> Módulo.
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.
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.
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.
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
Saludos a todos, esperamos que les haya quedado claro con este tutorial.
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.
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
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.
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
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
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?
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?
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
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.