Intercambiar datos entre Excel y Access 2010 (ADO) con macros VBA

Tras algunos intentos comparto un ejemplo muy simple de como intercambiar datos entre Excel y Access 2010 usando una conexión ADO y macros VBA.

El primer trozo de código escribe o transfiere datos desde Excel 2010 hacia Access 2010, desde las celdas “A1” (para el nombre) y “B1” (para el apellido) hacia la base de datos que lleva el nombre de “datos.accdb” con una sola tabla “tabla1” y dos campos “nombre” y “apellido”, que se encuentran en la misma carpeta que nuestro archivo en Excel 2010.

Sub escribiraccess()

    Dim cs As String
    Dim sPath As String
    Dim sql As String
    Dim cn As ADODB.Connection

    sPath = ThisWorkbook.Path & "\datos.accdb"
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

    Set cn = New ADODB.Connection
    cn.Open cs

    sql = "insert into tabla1 (nombre, apellido) values('" & Cells(1, 1).Value & "', '" & Cells(1, 2).Value & "')"

    cn.Execute sql

    cn.Close

    Set cn = Nothing

End Sub

El segundo trozo de código transfiere datos desde Access 2010 hacia Excel 2010 previamente escritos con el código anterior y lo enviará a la celda “C1”.

Sub escribirexcel()

    Dim cs As String
    Dim sPath As String
    Dim sql As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    sPath = ThisWorkbook.Path & "\datos.accdb"
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

    Set cn = New ADODB.Connection
    cn.Open cs

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
    End With

    sql = "select * from tabla1"

    rs.Open sql, cn

    Range("C1").CopyFromRecordset rs

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Sub

Por último dejo el ejercicio para que puedan descargarlo.

Etiquetas:, , , ,

"Trackback" Enlace desde tu web.

M. Vizcarra

Ing. Industrial, egresado de la Facultad de Ingeniería Industrial y de Sistemas de la Universidad Nacional Hermilio Valdizán de Huánuco, Perú.

Comentarios (29)

  • Ing. Odalys Morales

    |

    Hola Colega Vizcarra
    Me gustaria saber como transfiero los datos desde una Tabla en ACESS (un pequeno Banco de datos con nombres de clientes y nombres de Projectos ) a una o varias celdas de una Hoja en Excel.
    Podria indicarme un Macro.
    Agradecida de Antemano. Una Venezolana desde Austria.
    Dipl. Ing. Odalys Morales

    Reply

  • Excel

    |

    Muy buenas macros. Ya las he probado y funcionan muy bien.

    Reply

  • Carlos

    |

    Eres lo mejor amigo esto estado buscando hace años.
    muchas gracias

    Si no te molesta me podrias ayudar a traer los datos de access a excel en base a condiciones, por ejemplo solo quiero que me traigan los datos con fecha mayo o asi.

    Reply

  • JORGE

    |

    Intercambiar datos entre Excel y Access 2010 (ADO) con macros VBA, estas macros son lo que estaba buscando tiempo atras, sin embargo no logre hacerlas correr, ya que me sale el error en la cuarta linea de la primera macro, no si es que hay que habilitar algunos otros comandos, librerias, centros de confianza, etc, si pudieras ayudarme con esta duda te agreceria mucho

    Reply

  • Osciel Barraza

    |

    Hola.
    Me ha ayudado mucho este código, lo que agradezco mucho, pero la consulta es la siguiente. cual sería el código para la edición de los datos o la eliminación del registro completo???

    Atento a sus comentarios
    Osciel Barraza G.

    Reply

  • Ricardo Coral

    |

    Excelente, funciona perfecto. Gaste mucho tiempo intentando infructuosamente realizar lo que aqui generosamente me ayudaron en unos cuantos segundos. Muchas gracias.

    Reply

  • MARIANO ALBERTO MTZ.

    |

    excelente!!! esto esta perfecto, agradezco mucho al autor de este codigo, es clarisimo y sencillo, 🙂 pensaba que era mas complicado pero estos ejemplos ayudaron demasiado!

    Reply

  • Irasema

    |

    para traer informacion a Excel, donde solo traiga los registros de acuerdo a la condicion where, como debo declarar la variable, hice varios intentos y me marco error.
    Agradezco tu apoyo,

    Reply

  • M. Vizcarra

    |

    En el macro escribirexcel() cambia esta linea:

    Range("C1").CopyFromRecordset rs

    por esta otra:

    If Not rs.EOF Then
        myarray = rs.GetRows
        Else
        MsgBox "No hay datos", vbCritical
    End If

    La variable myarray (que es un arreglo donde pasaras todos los datos) debe ser una variable global, declárala antes de todos los macros así:

    public myarray

    Reply

  • Juan

    |

    Tengo una pregunta, como hago para que coja todo un rango, es decir, esta macro solo toma los valores de las Celdas A1 y B1, para pasar a ACCESS, pero si tengo 5 nombres como hago para que se pasen los 5 nombres que estan en la columna A y sus apellidos de la columna B.

    Reply

  • Rolando Q

    |

    Gracias por el ejemplo, es muy ilustrativo, seria bueno un ejemplo de como poder realizar consultas desde excel, para buscar un dato especifico de la tabla, ademas de otro que muestre como se pueden editar los datos de la tabla desde excel
    Gracias por el aporte

    Reply

  • Alvedys

    |

    Amigo excelente el código, pero me quiero traer los datos a una hoja especifica en el Excel. ayúdame en eso por favor

    Reply

  • María Isabel

    |

    Hola, necesito una ayuda, muchas gracias a quién pueda ayudarme.
    Tengo un error de compilación en la siguiente línea:

    Dim cn As ADODB.Connection

    ¿Qué debo hacer?

    Espero una respuesta.

    Reply

  • xiia

    |

    como sería la intruccion de excel 2007 a acces 2007. Estoy buscando la funcionalidad de grabar los datos de excel a acces, donde coincida el ejecutivo y cliente

    Reply

  • xiia

    |

    Una pregunta, la macro esta limitada para que guarde solo 2 campos. Hice la prueba en agregar los campos en la BD y lo correspondiente a excel, pero envia error

    Reply

  • Julio

    |

    Hola Ing. Vizcarra, tengo una pregunta, como le puedo hacer para revisar desde excel si ya tengo un registro en access.

    Reply

  • M. Vizcarra

    |

    Bueno, cuenta cuantos registros hay, guardas esa cantidad en alguna celda como referencia y cuentas otra vez la cantidad de registros. Al momento que alguien actualice la tabla, la cantidad de registros será mayor, lo que indica que tienes nuevos datos. Para contar los registros tienes que enviar los datos a una matriz, como lo indiqué mas arriba, luego solo utilizas la propiedad ubound así: Ubound(matriz). NO es la única manera, puedes investigar más acerca de como contar registros.

    Reply

  • Gabriel Mendoza

    |

    Saludos amigo y muchas gracias por su aporte. Tengo un problema con las líneas:
    sPath = ThisWorkbook.Path & “\datos.accdb” en la parte “thisWorkBook”
    y en la linea:
    sql = “insert into tabla1 (nombre, apellido) values(‘” & Cells(1, 1).Value & “‘, ‘” & Cells(1, 2).Value & “‘)” en la parte de “Cells”
    ¿que estaré haciendo mal?. Gracias

    Reply

  • M. Vizcarra

    |

    El problema puede estar en las comillas, verifica que sean comillas y apóstrofes simples, otro inconveniente podría estar en las referencias, asegúrate activar la referencia a “Microsoft ActiveX Dtata Objects 2.8 Library”, estas se encuentran en Herramientas/Referencias en el panel de programador de macros

    Reply

  • Marco P.

    |

    Muy buena la macro, pero al pasar los datos desde excel a access solo pasa un registro o columna:
    Esquema excel
    Nombre Apellido numero
    Luis Sanchez 12345
    Maria Perez 44476
    Ana Lino 31222

    Pero al pasar a access, solo pasa un dato:
    Esquema Access
    Id Nombre Apellido numero
    1 Luis Sanchez 12345
    Y el resto ya no pasa.

    Gracias, por la ayuda.

    Reply

  • Raul

    |

    Hola Buenos días,

    Muchas gracias por su aporte, me esta siendo de mucha utilidad. Pero tengo una pregunta; Si quiero introducir a la tabla de Access toda la información que tengo en una columna ( sin incluir las celdas vacías) como lo ejecuto en el código SQL??? ya que dentro del código solo se especifican las coordenadas de la celda : ‘” & Cells(1, 1).Value & “‘, ‘” & Cells(1, 2).Value & “‘, ‘” & Cells(1, 3).Value & “‘, ‘” & Cells(1, 4).Value & “‘ … … … … … … etc. Y yo quiero introducir por ejemplo el rango de celdas “A1:A980”. De nuevo muchas gracias por tu aporte y gracias por responder nuestras dudas. Saludos!

    Reply

  • Germán Caíno

    |

    Buenas,

    Les escribo para agradecerles por el excelente aporte. El código hace exactamente lo que se proponen, sin arrojar ningún error. Además la explicación es muy clara y concreta. Nuevamente muchas gracias.

    Reply

  • Jonhatan

    |

    Excelente aporte, estoy aprendiendo del tema y me sirvió demasiado en mi día a día! Gracias

    Reply

  • David

    |

    Gracias por el aporte en primer lugar. En segundo, estoy teniendo problemas al intentar meter una fecha en la BD. He utilizado tanto las ‘ como el # para introducirla, he intentado convertirla con CDate, e includo he intentado dejar de lado la variable y forzarla a mano en plan ’02-02-2015′, o ’02/02/215’. Todo con idéntico resultado. No me deja meterla. Syntax error.
    Con string y con numericos se lo paso desde variables sin problema. Con fecha no puedo.
    Conste que lo intento introducir en un campo fecha de la BD.
    ¿Puedes arrojar algo de luz sobre el problema?
    Gracias

    Reply

  • M. Vizcarra

    |

    @David: intenta utilizar esta función:
    Format(variable_o_fecha_en_string, “dd/mm/yyyy”)

    Reply

  • Luis Rojas

    |

    Hola amigo, muchas gracias por compartir esta info, tengo un duda, veras si yo quisiera modificar una fila de la BD de Access desde Excel como sería la programación?, agradezco tu pronta respuesta
    Gracias

    Reply

  • Ricardo

    |

    amigo tengo un problema con el uso de la Hoja COBRANZA(“Monto Anterior” celda H1) , CLI(“compra acumulada” la celda F2, “Deuda” celda G2), VTA(“Precio Unitario” celda Q2), RVTA(“Monto Anterior” celda I2, “Monto Total” celda K2), COB(“Monto de Documento” celda L2, “Cobrado” celda M2). me arroja redondeado, quisiera tenerlo en decimales.

    De antemano te agradezco.

    mi correo es ricardolujoso@hotmail.com

    Reply

  • william

    |

    Buen día, quisiera realizarte una consulta;
    Quisiera extraer información de una tabla en access seleccionando una opción mediante un CUADRO COMBINADO desde excell.
    Las opciones que tengo en el cuadro combinado son: despachado, anulado, pendiente, quisiera que al momento de seleccionar uno de ellos me muestre en excel toda la información con respecto a ese estado.

    Muchas Gracias por el apoyo.

    Reply

  • Eugenia

    |

    Hola a todos,
    Mi pregunta es, cómo hago en VBA para llamar a un rango de datos presentes en access, seleccionando un intervalo de fechas establecidas en excel?.

    Reply

Deja un comentario