Home > Bases de datos, Excel, vba > Conectar Excel a Access, MySql y SQL

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

Categories: Bases de datos, Excel, vba Tags: , , , , ,
  1. José Luis
    April 13, 2011 at 04:38

    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.

    • April 13, 2011 at 22:10

      Agradezco los comentarios. Mucha suerte en tu procedimiento.

      Saludos desde México.

      Alex.

  2. DANIELA
    April 15, 2011 at 23:25

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

  3. Hector Perez
    May 24, 2011 at 11:37

    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. August 5, 2011 at 15:34

    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.

    • August 5, 2011 at 16:39

      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

    • August 5, 2011 at 16:46

      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
      August 6, 2011 at 17:05

      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. August 5, 2011 at 16:58

    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

    • sergioacamposh
      August 6, 2011 at 17:09

      Te refieres a hacer la macro en Access o Excel ??

  6. miguel
    August 9, 2011 at 08:47

    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
      August 11, 2011 at 01:26

      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
    August 15, 2011 at 14:58

    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
    August 15, 2011 at 18:51

    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
    August 23, 2011 at 04:20

    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
    August 23, 2011 at 04:25

    ya lo solucioné

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

    gracias igualmente

    • sergioacamposh
      August 25, 2011 at 08:19

      Qué bueno que lo solucionaste. Saludos.

  11. miguel
    August 25, 2011 at 09:18

    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.

    • sergioacamposh
      August 25, 2011 at 09:22

      No te preocupes. Qué gusto que lo hayas solucionado.

      Saludos !!

  12. Joan
    September 22, 2011 at 10:01

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

    • sergioacamposh
      September 24, 2011 at 18:24

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

  13. skorth
    January 13, 2012 at 23:31

    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?

    • sergioacamposh
      January 14, 2012 at 09:34

      por lo pronto puedes intentar guardar la base como archivo .mdb

  14. FELIPEELBAMBINO
    February 2, 2012 at 15:52

    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
    April 10, 2012 at 14:02

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

  16. Alvedys Mata
    April 10, 2012 at 14:18

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

  17. Roger
    April 13, 2012 at 11:35

    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
      April 13, 2012 at 16:58

      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.

      • Roger
        April 17, 2012 at 17:48

        Te agradesco, ya solucione el problema..saludo

  18. RG
    June 15, 2012 at 09:20

    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
    June 19, 2012 at 10:17

    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

    • June 19, 2012 at 13:39

      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
    June 19, 2012 at 10:18

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

    • June 19, 2012 at 13:42

      Entonces aquí ya salimos del tema Excel para cambiarnos al tema vb.net, el cual este no es el blog para tocar ese tema.

  21. Dizo
    June 20, 2012 at 11:46

    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

    • June 20, 2012 at 12:36

      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.

  1. June 14, 2011 at 00:29
  2. December 29, 2011 at 22:04

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s