Facebook Twitter Gplus Flickr Pinterest LinkedIn YouTube RSS
Home Bases de datos Conectar Excel a Access, MySql y SQL
formats

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

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
47 Comments  comments 
  • José Luis

    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.

    • http://sergioacamposh.spaces.live.com sergioacamposh

      Agradezco los comentarios. Mucha suerte en tu procedimiento.

      Saludos desde México.

      Alex.

  • DANIELA

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

  • Hector Perez

    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.

  • Pingback: Exportar de Excel a Access mediante formulario de vba « Excel e Info: tecnología e información

  • http://gravatar.com/adofeg adofeg

    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.

    • http://gravatar.com/adofeg adofeg

      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

    • http://gravatar.com/adofeg adofeg

      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

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

  • http://gravatar.com/adofeg adofeg

    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

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

  • miguel

    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

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

  • miguel

    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

  • miguel

    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

  • novato

    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

  • novato

    ya lo solucioné

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

    gracias igualmente

    • sergioacamposh

      Qué bueno que lo solucionaste. Saludos.

  • miguel

    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

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

      Saludos !!

  • Joan

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

    • sergioacamposh

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

  • Pingback: EXCELeINFO – Top 10 de publicaciones vistas en 2011 « EXCELeINFO – Excel vba e información

  • skorth

    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

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

  • FELIPEELBAMBINO

    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

  • Alvedys Mata

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

  • Alvedys Mata

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

  • Roger

    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

      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

        Te agradesco, ya solucione el problema..saludo

  • RG

    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.

  • dizo

    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

    • http://about.me/sergioacamposh sergioacamposh

      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.

  • dizo

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

    • http://about.me/sergioacamposh sergioacamposh

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

  • Dizo

    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

    • http://about.me/sergioacamposh sergioacamposh

      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.

  • Pingback: EXCELeINFO – Excel vba e información - Estadísticas del Blog 2012, lo más visitado

  • Pingback: EXCELeINFO – Excel vba e información - Generar sentencias SQL INSERT INTO en Excel

  • Hernán Camilo MV

    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

  • Edisson Pedraza

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

  • america

    hola una pregunta que es “Option=3″

Sistema Wordpress corriendo bajo... Windows Server 2008 R2
Follow

Get every new post delivered to your Inbox

Join other followers