Primero será necesario activar la cinta de opciones de Desarrollador o Programador: Archivo / Opciones / Personalizar cinta de opciones
Posteriormente insertamos un botón el cual ejecutará nuestro código que se conectará a la base de datos para obtener registros de la base de datos.
Al activar esta opción podremos dibujar un botón el una celda de Excel, a continuación se muestra la siguiente ventana, en ella asignamos un nombre a la macro:
Al presionar el botón Nuevo se abrirá otra ventana con el procedimiento cmdEjecutarScript(), el cual contendrá todo el código que permitirá ejecutar nuestro script.
El siguiente código deberá ser copiado en el cuerpo del método o procedimiento antes mencionado:
Sub cmdEjecutarScript()
Dim CMDStoredProc As ADODB.Command
Dim CnnConexion As ADODB.Connection
Dim RcsDatos As ADODB.Recordset
Dim CadConexion As String 'Cadena de conexión
Dim Row As Integer
Dim RecordsAffected As Long
'Cadena de conexión
Dim Servidor As String
Dim Usuario As String
Dim Contrasena As String
Dim BaseDatos As String
Servidor = "Servidor-SVRMSQL"
Usuario = "sa"
Contrasena = "654546521sasQwert"
BaseDatos = "Empleados"
CadConexion = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & Usuario & ";Pwd=" & Contrasena & ";Initial Catalog=" & BaseDatos & ";Data Source=" & Servidor & ""
Set CnnConexion = New ADODB.Connection
Set RcsDatos = New ADODB.Recordset
Set CMDStoredProc = New ADODB.Command
'Establecemos comunicación con nuestro servidor SQL Server
Call CnnConexion.Open(CadConexion)
'Enlazamos nuestros objetos y definimos el procedimiento almacenado a ejecutar
CMDStoredProc.CommandType = adCmdText
Set CMDStoredProc.ActiveConnection = CnnConexion
CMDStoredProc.CommandText = "SELECT * FROM Empleados"
'Creamos el parámetro del procedimiento almacenado
Call CMDStoredProc.Parameters.Append(CMDStoredProc.CreateParameter("PV_OPCION", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 10))
'Ejecutamos de Script
Set RcsDatos = CMDStoredProc.Execute(RecordsAffected, , ExecuteOptionEnum.adAsyncFetch)
'Recorremos el Recordset resultante para asignarlo a la celda en Excel
Row = 1
Do While Not RcsDatos.EOF
Cells(Row, 2).Value = RcsDatos.Fields(0).Value
Row = Row + 1
RcsDatos.MoveNext
Loop
End SubEn el último fragmento de código se muestra un ciclo en donde se recorren los resultado del script y se copian los datos en la celda de excel.
OJO: Este archivo debe ser guardado como Plantilla de Excel habilitada para macros, ya que de no hacerlo perderán todo el código de la macro.
Link de descarga del archivo de Excel: https://goo.gl/5p74e4
Espero les sea de mucha utilidad.
Muchas gracias por el código, muy claro y preciso.
Saludos
Gacias a ti por tu interacción, Saludos.
Gracias por el aporte, me aclara muchas dudas que tenía.
Hola, gracias por el aporte, que bibliotecas se deben habilitar en excel?
Hola Denger, gracias por contactarnos..
No es necesario activar ninguna biblioteca, con solo habilitar la pestaña de programador para tener acceso a las macros bastará.
Saludos.
Hola!!!!!
El ejemplo es muy bueno, busque varios tutoriales para entender (ya que soy nueva en esto). Pero me marca el siguiente error:
se ha producido el error -2147467259 (80004005) parse connect parameters conexion no valida excel…
Podrían ayudarme u orientarme, creo que esta mal mi cadena de conexión, No se si sea correcta?
CadConexion = «Provider=SQLOLEDB.1;Data Source=ip:puerto/BD;User ID=USER;Pwd=PASSWORD»
Gracias y Saludos
Hola Karla, de hecho veo que tienes un problema en la cadena de conexión:
CadConexion = «Provider=SQLOLEDB.1;Data Source=ip:puerto/BD;User ID=USER;Pwd=PASSWORD»
El Data Source=[debes sustituir por el número ip del servidor o por el nombre de la instancia].
Algo así:
«Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Pwd=contrasena;Initial Catalog=basedatos;Data Source=192.168.1.200»
Saludos.
Hola buenas tardes
Muy útil esto. Una pregunta ¿hay algún motivo por el que solo muestre la primera columna de la consulta? Lo apliqué con mi datos pero no logro traer todas las columnas. También quise ejecutar un procedure en lugar de consulta y me muestra el error «La operacion no esta permitida si el objeto esta cerrado»
Buenos días
Una pregunta ¿pudiste resolver el problema de que solo trae la primera columna? yo tengo el mismo problema.
Saludos
Muchas gracias por tu aporte, es un código bastan sencillo de estudiar, muy claro en su contenido! Tal vez, sólo deberías agregar que dentro del editor de código (Microsoft Visual Basic para Aplicaciones), en el menú Herramientas–>Referencias se debe habilitar: Microsoft ActiveX Data Objetcs 2.0 Library, para ejecutar a la primera, sin errores.
Te deseo mucho éxito!
Muchas gracias por la observación Vicente. Saludos.
Buen dia LCALDERON:
He construido una macro para insertar registros de una hoja de Excel a una base de datos de SQL Server. La tabla en la que requiero insertar los registros posee un campo autonumerico, y por esta cuestion me arroja error al ejecutar la insercion.
Se me ocurre ejecutar antes de la sentencia de insercion, el comando SET IDENTITY_INSERT MOV_PRODUCTOS ON pero al parecer no me esta funcionando. La porcion de codigo para esa sentencia la tengo asi:
stridentidad = «SET IDENTITY_INSERT MOV_PRODUCTOS ON»
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = stridentidad
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
Podrias por favor ayudarme a encontrar que me hace falta para que la sentencia se ejecute correctamente y asi poder insertar los registros? Le agradezco muchisimo su guia
Hola Alexander, tal vez viendo el contenido que pasas a la propiedad adCmdText, ahí debe de estar el error.
Hola Comos sería si quiero poner la cadena de conexión en una función y que me devuelva el resultado del RcsDatos en otro procedimiento cuando llame la función de conexión proporcionando la consulta…
si puedes ayudarme te lo agradecería quiero aprender
muchas gracias
Hola Comos sería si quiero poner la cadena de conexión en una función y que me devuelva el resultado del RcsDatos en otro procedimiento cuando llame la función de conexión proporcionando la consulta…
si puedes ayudarme te lo agradecería quiero aprender
muchas gracias
Estimado, la consulta me está arrojando sólo una columna de la tabla.
Desconozco cuál sería la razón. Sólo hice una consulta simple de SELECT * FROM RSACCOUNT que contiene 38 columnas de datos y muestra la número 2 sin su encabezado.
Buen día,
Funciona excelente, le hice una modificacion para que la consulta funcione con un Where asociado a un dato en otra celda, funciona bien hasta con 5 digitos en la celda, despues de 6 ya no toma la funcion, sabes a que se debe?
Hola.
Me parece buen código.
Al compilar me dice no se ha definido el tipo definido por el usuario en la linea: CMDStoredProc As ADODB.Command
¿Cómo lo puedo solucionar?
Hola Jesus.
Solo te falta agregar la biblioteca Microsoft ActiveX Data Objects Recordset 6.0 Library. Para ello tenés que ir al Menú Herramientas , referencias y ahí buscar la biblioteca que te indico.
suerte !
Buen dia, estoy tratando de hacer una conexion de excel a sql server 2012 pero me da el siguiente error el codigo que compartes aqui, espero me puedas apoyar.
Muchas gracias y saludos!
Buen día, estoy tratando de hacer una conexión de Excel a sql server 2012 pero me da el siguiente error el código que compartes aquí:
Dim CMDStoredProc As ADODB.Command (No se ha definido el tipo definido por el usuario) ya intente activando el Microsoft data objects activeX 6.0 library y sigue igual
espero me puedas apoyar.
Muchas gracias y saludos!