Ejecutar consulta SQL desde Excel
Hace algunos días me surgió la necesidad de extraer datos de unas bases de SQL a Excel, a lo cual me di a la tarea de buscar información al respecto. Cuál fue mi sorpresa que no existe mucha información al respecto. Encontré un ejemplo de ejecutar consulta SQL desde Excel del cual tomé el código que realiza la función y adecué un formulario para que sea más amigable la consulta.
Cómo funciona?
El ejemplo funciona si se tiene una cadena de conexión a SQL, y aunque no está probado para MySql no dudo que también funcione, sólo modificando la cadena.
Se muestra un formulario donde se especifica el nombre del servidor, de la base de datos, así como el usuario y la contraseña, además de un espacio para se que introduzca la sentencia SQL a ejecutar.
Consideraciones
Se requieren conocimientos básicos de SQL para armar la consulta, así como un servidor SQL local o en red.
Ver Video Ejecutar Consulta SQL desde Excel
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Código
Private Sub CommandButton1_Click() 'Llamas la función Ejecutar Cells.ClearContents Call Ejecutar(Sheets(2).Range("consulta"), "Hoja1") End Sub
Function Ejecutar(Sql As String, Hoja As String) On Error GoTo ErrorHandler Dim cn As Object ' crea un objeto Connection Set cn = CreateObject("ADODB.Connection") ' IMPORTANTE: Indicar la cadena de conexión a usar servidor = Sheets(2).Range("servidor") base = Sheets(2).Range("base") Usuario = Sheets(2).Range("usuario") pass = Sheets(2).Range("pass") Conexion = "Provider=SQLOLEDB.1;" & _ "Password=" & pass & ";" & _ "Persist Security Info=True;" & _ "User ID=" & Usuario & ";" & _ "Initial Catalog=" & base & ";" & _ "Data Source=" & servidor 'cn.ConnectionString = "Provider=SQLOLEDB.1;Password=s3cr3t0;Persist Security Info=True;User ID=sa;Initial Catalog=Cobranza;Data Source= 192.168.2.6" cn.ConnectionString = Conexion ' verifica que los parámetros no estén vacios If Sql <> vbNullString And Hoja <> vbNullString Then ' variable para al rec de ado Dim rst As Object ' abre la conexión a la base de datos cn.Open ' crea un nuevo objeto recordset Set rst = CreateObject("ADODB.Recordset") ' Ejecuta el sql para llenar el recordset rst.Open Sql, cn, 1, 3 ' variables para los indices de las filas y columnas c = 0 f = 0 ' recorre las columnas, añade el nombre del campo al encabezado For i = 0 To rst.Fields.Count - 1 Sheets(1).Range(Chr(i + 65) & f + 1).Value = rst.Fields(i).Name Next f = f + 1 ' recorre todo el recordset hasta el final Do While Not rst.EOF ' recorre los campos en el registro actual del recordset para recuperar el dato For i = 0 To rst.Fields.Count - 1 ' añade el valor a la celda Sheets(1).Range(Chr(c + 65) & _ f + 1).Value = rst.Fields(c) c = c + 1 Next ' resetea el indice de las columnas c = 0 ' Referencia al registro actual (incrementa ) f = f + 1 ' Siguiente registro rst.MoveNext Loop ' cierra y descarga las referencias On Error Resume Next rst.Close cn.Close Set cn = Nothing Set rst = Nothing End If Call Macro1 Exit Function ErrorHandler: MsgBox "Ha ocurrido un error: " & Err.Description, vbExclamation, "EXCELeINFO" End Function
:: Descargar EXCELeINFO – Ejecutar consulta SQL desde Excel
yo uso laconexion a datos, pero lo que no he podido hacer es enviar los parametros desde excel a mis parametros @ en mi consulta dentro de la conexion
Revisa la cantidad de campos de la base, ya no soporta tablas con demasiados campos
Hola, soy nuevo en esto y quisiera saber si es posible ejecutar sentencias sql tanto para leer como escribir tablas en un archivo accdb desde una macro de excel, agradeceria me ayudaras con un ejemplo basico, gracias
Para escribir datos en un archivo de Access te puedes guiar en el ejemplo siguiente: https://exceleinfo.wordpress.com/2011/06/14/exportar-de-excel-a-access-mediante-formulario-de-vba/
Y para leer datos desde Access, te recomiendo usar la herramienta de Excel de la pestaña Datos: Obtener datos externos… desde Access.
Aunque sería interesante saber qué propósito buscas con esa cuestión, ya que desde el mismo Access puedes ejecutar tus propias consultas SQL: https://exceleinfo.wordpress.com/2012/05/10/exportar-grandes-cantidades-de-datos-de-access-a-excel-con-select-top/
una duda mas en la sentencia
rst.Open Sql, cn, 1, 3
los parametros despues de conexion es decir 1 y 3 que significan , siempre son esos valores para la funion open del recordset?
Puedes encontrar mejor explicación en el siguiente link: http://www.w3schools.com/ado/met_rs_open.asp
Una consulta: Uso una consulta SQL para traer datos a una tabla sin problemas, pero no consigo traer un solo registro a partir de un dato en una celda. Alguna sugerencia?
Felicitaciones por la página.
Saludos,
Fer
La idea es que el dato en la celda sea tomado como variable en la consulta.
Saludos de nuevo.
El código toma en cuenta los datos que están en la celda B5 de la hoja Consulta, entonces lo que puedes hacer es concatenar valores y que estos valores concatenados sigan siendo los de la celda B5.
Como actualizo la bd desde excel
A falta de más explicación de tu parte, puedes checar el siguiente post que creo te podrá ser de utilidad http://exceleinfo.wordpress.com/2011/02/08/conectar-excel-a-access-mysql-y-sql/
Hola Sergio
Me sale un error:
La operaciòn no esta permitida si el objeto esta cerrado.
Que puede ser.:?
Primeramente hay que validar que tu PC pueda conectar al servidor SQL, además de validar que la setencia esté bien escrita.
Para validar que sí puedar ver el servidor SQL, puedes probar con este posto http://exceleinfo.wordpress.com/2012/05/13/obtener-datos-de-access-sql-mysql-web-txt-etc-desde-excel-sin-vba/
Que tal.
Gracias por compartir; tu solucion me ha servido muchisimo.
Una cuestion:
Por lo que he visto, el numero de campos que se puede traer esta limitado. Como se puede incrementar ese numero para incluir mas en una consulta?
La solución sería haciendo una consulta a la base de datos, pero en la pestaña Datos > De otras fuentes > Desde SQL server.
He hecho muchas pruebas y sí está limitado el número de campos.
Hola.
Me dice que no puedo iniciar sesión, y cuando importo manualmente desde datos-de otras fuentes-Desde SQL Server no hay problema. En SQL Server tengo puesta “Autenticación de Windows” y uso Office 2013.
Un saludo!
Hice las pruebas en Office 2013 y me conecta sin problemas.
Cuando especificas el nombre del servidor intenta tanto con la IP como con el nombre. Y te recomiendo que ingreses el usuario y contraseña de tu servidor SQL, que es diferente a la de sesión de Windows. El usuario por default en Sql Server es ‘sa’.
Muy bueno el ejemplo lo probe con autenticacion sql
y de maravilla funciono.,
en modo windows no funciona.,
Hola!
Muy buen ejemplo, pero tengo una pregunta, actualmente tengo la conexion a ODBC y en el codigo tengo la consulta manualmente, pero lo que quiero es cambiar la condicion de la consulta por medio de un textbox, por ejemplo, si tengo “SELECT * FROM mitabla WHERE fechaHora=’10/25/2013 11:40′”, quiero que ese valor de “fechaHora” darlo por medio de un textbox, es posible? He buscado e intentado de varios formas, pero hasta ahora no lo he logrado, gracias de antemano.
Revisa este ejemplo de este mismo que publiqué precisamente para poder manipular reportes por medio de fecha.
http://blogs.itpro.es/exceleinfo/2013/02/16/obtener-reportes-de-base-de-datos-sql-desde-excel/
Seria mucho pedir si me envias el ejemplo a mi correo? En donde estoy esta bloqueada la pagina, muchas gracias, te dejo mi email marse.17@gmail.com
Prueba este link
https://skydrive.live.com/redir?resid=4509FEB32392C17C!2464&authkey=!ALurNBbLAxXVurY
Tengo un problema, según que tabla consulto, en ocasiones me aparece solo el encabezado y me manda un error, o bien me aparece la tabla perfectamente, os mando un pantallazo a ver si se os ocurre algo.
igual que ruben solo sale el encabezado cuando cambiar el servidor y la base de datos, el recordset no muestra los registros de la tabla
Hola Sergio, gran ejemplo, pero me gustaria saber si es posible o viable hacer lo siguiente. No soy muy experto en excel aun, y me gustaria que en la hoja de consultas tener por cada línea una consulta diferente a la base de datos, y en la hoja de Datos, un menú para poder seleccionar la consulta que quiero ejecutar directamente sin el formulario. De esta manera desde un mismo excel podria tener todas las consultas y ejecutar la que mas me convenga. ¿Sabes si es muy complicado de adaptar tu código? Gracias.