Conectar Excel a Access, MySql y SQL

Twittear este post

 Compartir en Facebook

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

:: Descargar zip con ejemplos

You may also like...

49 Responses

  1. José Luis says:

    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.

  2. DANIELA says:

    UM ESTA INTERESANTE PERO FALTAN LAS BASE DE DATOS PARA CADA ARCIVO, LO PUEDEN SUBIR

  3. Hector Perez says:

    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.

  4. adofeg says:

    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.

    • adofeg says:

      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

    • adofeg says:

      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

    • sergioacamposh says:

      Para ese trabajo, Excel tiene herramientas de datos para hacer consultas a bases de datos. Sólo entra a la pestaña/menú Datos.

  5. adofeg says:

    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

  6. miguel says:

    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

    • sergioacamposh says:

      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 !!

  7. miguel says:

    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

  8. miguel says:

    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

  9. novato says:

    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

  10. novato says:

    ya lo solucioné

    con quitar el punto de interrupción y volverlo a poner siguió, te dije que era novato jaja

    gracias igualmente

  11. miguel says:

    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.

  12. Joan says:

    Aparentemente se ve bien la conexion con mysql ese codigo es de un formulario vba de excel..?

    • sergioacamposh says:

      El código se puede agregar a formulario, pero ese código vba hace referencia al contenido de las celdas.

  13. skorth says:

    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?

  14. FELIPEELBAMBINO says:

    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

  15. Alvedys Mata says:

    Amigo sergio como hago para que solo agregue los últimos datos a mi tabla de acces?

  16. Alvedys Mata says:

    Retiro mi pregunta anterior lo que quiero es que sobre escriba los datos como lo dice FELIPEELBA.

  17. Roger says:

    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

    • sergioacamposh says:

      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.

  18. RG says:

    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.

  19. dizo says:

    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.

  20. dizo says:

    por cierto debo de agregar que esto o quiero hacer mediante código a través de mi programa en vb.net

  21. Dizo says:

    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.

  22. Hernán Camilo MV says:

    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

  23. Edisson Pedraza says:

    no e podido conectar sql y exel.lo que note es que tengo la contraseña por default.

  24. america says:

    hola una pregunta que es “Option=3”

  25. Santos Manuel Castellon says:

    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

  26. Matias says:

    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!

  1. June 14, 2011

    […] También puedes consultar Conectar Excel a Access, MySql y SQL […]

  2. December 29, 2011

    […] Conectar Excel a Access, MySql y SQL […]

  3. December 29, 2012

    […] Conectar Excel a Access, MySql y SQL […]

  4. June 24, 2013

    […] La semana pasada surgió la necesidad de cargar información desde Excel a SQL Server. Lo primero que propuse es usar ya un archivo que publiqué anteriormente en este artículo Conectar Excel a Access, MySql y SQL. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: