Conectar Excel a Access, MySql y SQL
En esta ocasión comparto 3 archivos que actualmente utilizo para dar de alta datos a bases de datos de Access, SQL y MySql desde Excel. Lo importante es saber exactamente el nombre de la base de datos, la tabla, y en el caso de SQL y MySql, el servidor, usuario y contraseña.
Comparto las macros que nos permiten hacer la tarea antes mencionada, aunque los archivos adjuntos son completamente funcionales.
Excel a Access
Sub exportaraccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, n As Long Dim nfila As String ' On Error GoTo Errores If Range("a2") = "" Or Range("b2") = "" Or Range("c2") = "" Or Range("d2") = "" Or Range("e2") = "" Then MsgBox prompt:="No hay datos para exportar", Buttons:=vbOKOnly + vbCritical, Title:="Campos vacios" Exit Sub End If ' Set cn = New ADODB.Connection cn.Open "provider=microsoft.jet.oledb.4.0; " & "data source=" & ThisWorkbook.Path & "" & shtListas.Range("rngBase") & ".MDB;" 'cn.Open "provider=microsoft.jet.oledb.4.0; " & "data source=" & ThisWorkbook.Path & "GUION.MDB;" Set rs = New ADODB.Recordset rs.Open shtListas.Range("rngTabla"), cn, adOpenKeyset, adLockOptimistic, adCmdTable n = 2 Do While Range("a" & n) <> Empty With rs .AddNew .Fields("Nombre") = Range("a" & n).Value .Fields("Cuenta") = Range("b" & n).Value .Fields("Password") = Range("c" & n).Value .Fields("Permisos") = Range("d" & n).Value .Fields("Campana") = Range("e" & n).Value .Fields("Supervisor") = Range("f" & n).Value .Fields("Monitoreos") = Range("g" & n).Value .Fields("Estatus") = Range("h" & n).Value .Fields("Nivel") = Range("i" & n).Value .Fields("Tipo") = Range("j" & n).Value .Fields("Grupo") = Range("k" & n).Value .Fields("No Empleado") = Range("l" & n).Value .Fields("Fecha Ingreso") = Date End With n = n + 1 Loop With rs .AddNew .Fields("Nombre") = Range("a" & n).Value .Fields("Cuenta") = Range("b" & n).Value .Fields("Password") = Range("c" & n).Value .Fields("Permisos") = Range("d" & n).Value .Fields("Campana") = Range("e" & n).Value .Fields("Supervisor") = Range("f" & n).Value .Fields("Monitoreos") = Range("g" & n).Value .Fields("Estatus") = Range("h" & n).Value .Fields("Nivel") = Range("i" & n).Value .Fields("Tipo") = Range("j" & n).Value .Fields("Grupo") = Range("k" & n).Value .Fields("No Empleado") = Range("l" & n).Value .Fields("Fecha Ingreso") = Date End With ' Set rs = Nothing cn.Close Set cn = Nothing ' MsgBox prompt:="Los datos fueron enviados correctamente", Buttons:=vbOKOnly, Title:="DATOS EXPORTADOS" Range("a2").Activate ' If [a3] = Empty Then Range("a2", Selection.End(xlToRight)).ClearContents Exit Sub End If nfila = Range("A65535").End(xlUp).Row ' Range("a2:F" + nfila).ClearContents Exit Sub Errores: MsgBox Err.Description & vbNewLine & vbNewLine & "Recuerda que el archivo debe estar en la misma ruta de la base de datos.", vbCritical, empresa End Sub Sub exportaraccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, n As Long Dim nfila As String ' On Error GoTo Errores If Range("a2") = "" Or Range("b2") = "" Or Range("c2") = "" Or Range("d2") = "" Or Range("e2") = "" Then MsgBox prompt:="No hay datos para exportar", Buttons:=vbOKOnly + vbCritical, Title:="Campos vacios" Exit Sub End If ' Set cn = New ADODB.Connection cn.Open "provider=microsoft.jet.oledb.4.0; " & "data source=" & ThisWorkbook.Path & "" & shtListas.Range("rngBase") & ".MDB;" 'cn.Open "provider=microsoft.jet.oledb.4.0; " & "data source=" & ThisWorkbook.Path & "GUION.MDB;" Set rs = New ADODB.Recordset rs.Open shtListas.Range("rngTabla"), cn, adOpenKeyset, adLockOptimistic, adCmdTable n = 2 Do While Range("a" & n) <> Empty With rs .AddNew .Fields("Nombre") = Range("a" & n).Value .Fields("Cuenta") = Range("b" & n).Value .Fields("Password") = Range("c" & n).Value .Fields("Permisos") = Range("d" & n).Value .Fields("Campana") = Range("e" & n).Value .Fields("Supervisor") = Range("f" & n).Value .Fields("Monitoreos") = Range("g" & n).Value .Fields("Estatus") = Range("h" & n).Value .Fields("Nivel") = Range("i" & n).Value .Fields("Tipo") = Range("j" & n).Value .Fields("Grupo") = Range("k" & n).Value .Fields("No Empleado") = Range("l" & n).Value .Fields("Fecha Ingreso") = Date End With n = n + 1 Loop With rs .AddNew .Fields("Nombre") = Range("a" & n).Value .Fields("Cuenta") = Range("b" & n).Value .Fields("Password") = Range("c" & n).Value .Fields("Permisos") = Range("d" & n).Value .Fields("Campana") = Range("e" & n).Value .Fields("Supervisor") = Range("f" & n).Value .Fields("Monitoreos") = Range("g" & n).Value .Fields("Estatus") = Range("h" & n).Value .Fields("Nivel") = Range("i" & n).Value .Fields("Tipo") = Range("j" & n).Value .Fields("Grupo") = Range("k" & n).Value .Fields("No Empleado") = Range("l" & n).Value .Fields("Fecha Ingreso") = Date End With ' Set rs = Nothing cn.Close Set cn = Nothing ' MsgBox prompt:="Los datos fueron enviados correctamente", Buttons:=vbOKOnly, Title:="DATOS EXPORTADOS" Range("a2").Activate ' If [a3] = Empty Then Range("a2", Selection.End(xlToRight)).ClearContents Exit Sub End If nfila = Range("A65535").End(xlUp).Row ' Range("a2:F" + nfila).ClearContents Exit Sub Errores: MsgBox Err.Description & vbNewLine & vbNewLine & "Recuerda que el archivo debe estar en la misma ruta de la base de datos.", vbCritical, empresa End Sub
Excel a MySql (será necesario descargar el driver 5.1 de MySql)
Dim oConn As ADODB.Connection Dim rs As ADODB.Recordset ' Function ExcelMySql() On Error GoTo err Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=100.1.11.11;" & _ "DATABASE=bd_database;" & _ "USER=user;" & _ "PASSWORD=pass;" & _ "Option=3" Exit Function err: MsgBox "Se ha producido el siguiente error: " & err.Description, vbInformation, ActiveWorkbook.Name End Function ' Function esc(txt As String) esc = Trim(Replace(txt, "'", "'")) End Function ' ' Function InsertData() On Error GoTo Er 'Se elimina la llamada a la función de conexión a la base de datos para hacerlo cuando inicie el archivo ' Call ConnectDB Set rs = New ADODB.Recordset sFunction = Application.WorksheetFunction.CountA(Range("A:A")) ' With shInsertData For rowCursor = 2 To sFunction strSQL = "INSERT INTO tbl_cat_usuarios (ID_txtusuariotelsys, txt_clavetelsys, txt_nombre, txt_apepat, txt_apemat, bin_statusactivo, bin_nivel) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ "'" & esc(.Cells(rowCursor, 3)) & "', " & _ "'" & esc(.Cells(rowCursor, 4)) & "', " & _ "'" & esc(.Cells(rowCursor, 5)) & "', " & _ esc(.Cells(rowCursor, 6)) & ", " & _ esc(.Cells(rowCursor, 7)) & ")" ' 'strSQL = "INSERT INTO tutorial (title, author, price) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ esc (.Cells(rowCursor, 3)) & ")" rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic Next End With MsgBox "Exito", vbInformation Exit Function Er: MsgBox "Error: " & err.Description, vbInformation, ActiveWorkbook.Name End Function
Excel a SQL
Dim oConn As ADODB.Connection Dim rs As ADODB.Recordset ' Function ConnectDB() On Error GoTo err Set oConn = New ADODB.Connection oConn.Open "Provider=SQLOLEDB.1;" & _ "Password=pass;" & _ "Persist Security Info=True;" & _ "User ID=user;" & _ "Initial Catalog=BASE;" & _ "Data Source=100.1.111.11" MsgBox "Éxito al conectarse a la base de datos", vbInformation, "1" Exit Function err: MsgBox "Se ha producido el siguiente error: " & err.Description, vbInformation, ActiveWorkbook.Name End Function ' Function esc(txt As String) esc = Trim(Replace(txt, "'", "'")) End Function ' ' Function InsertData() On Error GoTo Er 'Se elimina la llamada a la función de conexión a la base de datos para hacerlo cuando inicie el archivo ' Call ConnectDB Set rs = New ADODB.Recordset sFunction = Application.WorksheetFunction.CountA(Range("A:A")) ' With shInsertData For rowCursor = 2 To sFunction strSQL = "INSERT INTO tbl_operador (ID, txt_nombre, txt_apepat, txt_apemat, txt_tipocuenta, bit_activo, txt_rol, pws_contra) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ "'" & esc(.Cells(rowCursor, 3)) & "', " & _ "'" & esc(.Cells(rowCursor, 4)) & "', " & _ "'" & esc(.Cells(rowCursor, 5)) & "', " & _ "'" & esc(.Cells(rowCursor, 6)) & "', " & _ esc(.Cells(rowCursor, 7)) & ", " & _ "'" & esc(.Cells(rowCursor, 8)) & "' )" ' rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic Next End With MsgBox "Las claves fueron dadas de alta correctamente.", vbInformation, "EXCELeINFO" Exit Function Er: MsgBox "Error: " & err.Description, vbCritical, "EXCELeINFO" End Function
Maravilloso contenido publicas. No te haces a la idea de lo que me interesa la conexión con mysql, ya que ahora estoy en un engorroso procedimiento de importación de excel (previamente depuradas) por medio del software Navicat (un auténtico coñazo).
Lo intento y te cuento. Gracias.
Agradezco los comentarios. Mucha suerte en tu procedimiento.
Saludos desde México.
Alex.
UM ESTA INTERESANTE PERO FALTAN LAS BASE DE DATOS PARA CADA ARCIVO, LO PUEDEN SUBIR
Sergio, te soy honesto soy completamente novato en esto de las macros y me interesa tu codigo para importar la informacion del Excel al Access, desgargue tus ejemplos pero me manda el siguiente error “Se Rquiere un Objeto, Recuerda que el archivo debe estar en la misma ruta que la base de datos” y ya cree un archivo de Access 2007(la bd se llama BASE)y lo pegue en la misma carpeta donde tengo tus ejemplos, no he podido ver el funcionamiento.
Podrias explicarme que tengo que hacer?? Disculpa las molestias.
He de reconocer que no subí los archivos de ejemplo, pero el código es totalmente funcional. Para no hacerte batallar te paso el link para que descarques una base de ejemplo que funcionará con el ejemplo del Blog.
http://cid-4509feb32392c17c.office.live.com/self.aspx/Excel%20e%20Info/Conectar%20Excel%20a%20Access.zip
Saludos !!
Excelente Trabajo, es mucho más de lo que andaba buscando. Me interesa ver un ejemplo orientado a la inversa, en vez de meter datos al servidor de BD, Generar un reporte en excel de los datos de la base.
Sub SelectData()
On Error GoTo Er
‘Se elimina la llamada a la función de conexión a la base de datos para hacerlo cuando inicie el archivo
ConnectDB
Set rs = New ADODB.Recordset
strSQL = “Select * from tabla”
rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
p = rs.GetRows
cant_campos = UBound(p, 1) + 1
cant_registros = UBound(p, 2) + 1
For I = 0 To cant_registros – 1
For J = 0 To cant_campos – 1
MsgBox (p(I, J))
ActiveSheet.Cells(J + 1, I + 1) = p(I, J)
Next J
Next I
Exit Sub
Er:
MsgBox “Error: ” & err.Description, vbCritical, “EXCELeINFO”
End Sub
Fe de erratas : (Gracias por las ideas, VBA es lo máximo)
For I = 0 To cant_registros – 1
For J = 0 To cant_campos – 1
ActiveSheet.Cells(I + 1, J + 1) = p(J, I)
Next J
Next I
Para ese trabajo, Excel tiene herramientas de datos para hacer consultas a bases de datos. Sólo entra a la pestaña/menú Datos.
Una duda Sergio. Actualmente estoy programando VBA en access y quiero saber si hay manera de llamar desde un reporte o un formulario access un archivo excel pero mandandole un parametro, por ejemplo tener preprogramado un archivo excel que reciba 1 parametro y segun eso cambie la data alli consignada
Te refieres a hacer la macro en Access o Excel ??
disculpa por la pregunta: pero me podrias decir que lineas tengo que modificar para especificar la direccion de mi base de datos y el archivo de excel. en el codigo de excel a access. gracias
Busca una línea donde se especifique un archivo con la extensión MDB. Es ahí dónde especificas la ruta y el nombre. Y en la línea de rs.Open especificas el nombre de la tabla.
Saludos !!
ok. mira la cuestion es que le e puesto la direccion de la base y la tabal asi como lo mencionas la cuestion es que me sigue marcando object required. siendo que esta en la misma carpeta, crees que sea por alguna referencia o algo que me este equivocando, si pudieras mandarme un ejemplo te lo agradeceria mucho, gracias.
saludos
estuve haciendo unas pruebas, quite el on error para que me diera el error mas especifico y al llamar al debug me manda a esta linea
rs.Open shtListas.Range(“nombre de mi tabla”), cn, adOpenKeyset, adLockOptimistic, adCmdTable
y aparece la leyenda de error 424 object required. estara mal escrito o a q se deba.
saludos
En este post publicado encontrarás un ejemplo funcional: http://exceleinfo.wordpress.com/2011/06/14/exportar-de-excel-a-access-mediante-formulario-de-vba/
hola sergio
pues tengo una duda, soy nuevo en esto y siguiendo el ejemplo de conectar con mysql, se me queda cuando dice “Éxito al conectarse a la base de datos” y ya no hace nada mas, se queda en un bucle en el que va todo el rato a la parte de la conexión con base de datos
¿por qué no sale y sigue con la ejecución?
muchas gracias por el tutorial
un saludo
ya lo solucioné
con quitar el punto de interrupción y volverlo a poner siguió, te dije que era novato jaja
gracias igualmente
Qué bueno que lo solucionaste. Saludos.
sorry por la tardanza, ya me jalo el codigo. por ahi tenia un problemita con una instruccion q no me la reconocia pero ya corrio y me sirvio bien, gracias sergio.
saludos.
No te preocupes. Qué gusto que lo hayas solucionado.
Saludos !!
Aparentemente se ve bien la conexion con mysql ese codigo es de un formulario vba de excel..?
El código se puede agregar a formulario, pero ese código vba hace referencia al contenido de las celdas.
muy buena aportacion pero soy un poco novato y tengo problemas con access 2010 la extencion es accbd y no puedo hacer la coneccion algun concejo?
por lo pronto puedes intentar guardar la base como archivo .mdb
COMO HAGO PARA QUE NO ME DUPLIQUE LOS DATOS EXPORTADOS Y QUE CADA VEZ QUE LE DE ENVIAR SOBREESCRIBA SOBRE LOS DATOS DE LA TABLA ACCES
Amigo sergio como hago para que solo agregue los últimos datos a mi tabla de acces?
Denle una revisada a este post http://exceleinfo.wordpress.com/2011/06/14/exportar-de-excel-a-access-mediante-formulario-de-vba/
Retiro mi pregunta anterior lo que quiero es que sobre escriba los datos como lo dice FELIPEELBA.
Disculpa tendras codigo en el cual pueda conectame al servidor SQL mediante access, lo que pasa que en acces estan los formularios pero quiero migrar a SQL, y quiero conectarme por medio de una funcion de conexion y hacer consultas, altas, bajas……te agreadeceria tu ayuda
Si vas a vincular Access con SQL no necesitas código vba, ya que ambos son gestores de bases de datos. En Access lo que debes hacer es irte a la pestaña Datos externos > Base de datos ODBC > Importar el origen de datos en una nueva tabla de la base de dato actual …
Ahí creas tu conexión por IP al servidor SQL y sigues los pasos.
Te agradesco, ya solucione el problema..saludo
Que tal, estimado sergio!!
Tengo una duda que posiblemente me puedas ayudar.
Ya tengo realiza la conexión con la base de datos de MySQL (todo un logro gracias a tu tutorial), pero ahora requiero que por ejemplo el macro busque el valor de una celda al valor que tengo en MySql y si lo encuentre que me marque “Ok” si lo encontro, o “error”, si no lo encontro.
Con excel lo hago con la funcion VLOOKUP (y lo busca en otra hoja de excel 🙁 ), pero es realmente lento para muchas celdas.
1) Mi base MySql se llama “prueba” y la tabla se llama “Table 1”.
2) La columna que quiero buscar se llama “Col1”
3) Macro busca el valor y si lo encuentra marcar OK, si no lo encontro ERROR.
Gracias de antemano.
En el ejemplo que publiqué http://exceleinfo.wordpress.com/2011/11/08/ejecutar-consulta-sql-desde-excel/ se hace una consulta a una base de SQL, sería cuestión de adecuarla con un WHERE, así como adearlo para MySql… el principio es el mismo.
Te recomiendo hagas pruebas, ya que actualemnte no tengo un ejemplo que pueda compartir.
Hola
espero que me puedas ayudar, lo que yo quiero hacer es lo contrario a lo que hiciste aquí, quiero pasar mis datos de la base de datos mysql a excel ojala que me pudieras ayudar gracias por tus tiempo y tus tutoriales saludos
Para extraer datos de MySql a Excel, debes entrar a la pestaña Datos y elegir el motor de base de datos que necesitas. Para MySql deber bajarte el driver e instalarlo en tu PC.
por cierto debo de agregar que esto o quiero hacer mediante código a través de mi programa en vb.net
Entonces aquí ya salimos del tema Excel para cambiarnos al tema vb.net, el cual este no es el blog para tocar ese tema.
Debido a que no logre hacer esto mediante vb.net, lo hice manualmente siguiendo los pasos del siguiente link
http://www.w3resource.com/mysql/exporting-and-importing-data-between-mysql-and-microsoft-excel-part2.php
Pero si agrego un nuevo registro en mi base de datos el Excel no se actualiza ¿sabes si existe una forma para lograr que este se actualice? Gracias apreciaría mucho tu ayuda
Cuando realizas el procedimiento que consultaste, te crea un link de acceso directo a tus datos de MySql, lo cual sólo con dar click derecho y elegir la opción ‘Actualizar’ te devolverá los datos de tu tabla. Pero cuidado, por que si haces la Actualización, te reemplazará los cambios que le hayas hecho; de preferencia copia tus datos a otro archivo y ese sólo úsalo para consulta.
Cordial saludo pero pr que al conectar excel con Access me sale el error ‘3343’ en tiempo de ejecución que hace referencia a que no se reconoce el formato de base de datos
no e podido conectar sql y exel.lo que note es que tengo la contraseña por default.
hola una pregunta que es “Option=3”
Sergio, gracias por tu valioso aporte, podrias ayudarme a generar la conexión de Access a través de VBA Excel pero para office 2013 ya que el ejemplo que me muestras aquí no me funciona.
tengo instalado office 2013 64 bits
Muy bueno, mi pregunta es, ya que tengo conectada una base de datos para recurrentemente consultarla desde Excel con macros… en que lugar declarar las variables y procedimientos de conectar y desconectar de la base de datos para no tener que tener escrito el codigo de abrir y cerrar conexion en todos lados? probe separarlo como funciones pero el parametro ADODB.connection me tira problemas, Gracias!