AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA

Desde Excel podemos interactuar con Access, de hecho, podemos agregar nuevos registros, actualizarlos o eliminarlos. La forma de hacerlo es mediante ADO y trabajando con SQL o con Recordset.

Hace unos meses publiqué una entrada titulada: EXPORTAR DATOS DE EXCEL A ACCESS MEDIANTE ADO. Aquí hago referencia a posibilidad exportar una tabla de Excel a Access, pero no especificando nuevos registros, sino especificando nueva tabla, es decir, lo que hago realmente es crear una tabla de Access desde Excel (en bloque) y luego en el momento de actualizarla, borro la tabla de la base de datos de Access y vuelvo a transferir la de Excel (completa).

Sin embargo, hoy lo haremos de otra forma y nos centraremos en la posibilidad de insertar los nuevos registros especificándolos desde una hoja Excel.

Antes de comenzar, una aclaración, como todos sabéis cuando creamos nuestras bases de datos en Access o importamos tablas, existe la opción de permitir que Access agregue una clave principal:

AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA1

Para este ejemplo lo haremos de esta forma, permitiendo que Access agregue un ID a cada registro, más adelante os explicaré la importancia de hacerlo de esta forma.

Ahora que hemos creado (en este caso importado la tabla de Excel) nos encontramos con una base de datos de 198 registros (que se corresponden con los empleados de unos grandes almacenes):

AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA

Pues bien, imaginad que queremos incluir a 3 empleados en la tabla, para ello abrimos nuestra hoja Excel y detallamos los empleados y campos que queremos incluir:

AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA2

Como podéis observar hemos incluido a los tres empleados y hemos añadido los ID’s que le corresponden a cada uno de ellos. Ahora incluimos esta macro en un módulo estándar:

Sub InsertAccess()
Dim conAccess As ADODB.Connection
Dim Origen As String, Destino As String
Dim conExcel As String, obSQL As String
'Definimos las tablas de Access y Excel con las que vamos a trabajar
Destino = "[TRABAJADORES]"
Origen = "[EMPLEADOS$]"
'Cadena de conexión a nuestro archivo de Excel
conExcel = "[Excel 8.0;HDR=Yes;DATABASE=F:\COMPAÑIA.xls]." & Origen
'Sentencia SQL para incluir nuevos registros no repeditos
obSQL = "INSERT INTO " & Destino & " SELECT * FROM " & conExcel
'Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=F:\EMPRESA.accdb;"
.Open
.Execute obSQL
.Close
End With
End Sub

En la macro debéis especificar la ruta de ambos archivos (Excel y Access) en vuestro equipo y otorgar nombre a cada una de las tablas. (En la macro lo indico en las líneas comentadas)

Una vez que ejecutéis el proceso, la macro incluirá los tres empleados en la base de Access:

AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA3

La importancia de dejar que Access cree una clave principal (Id) es que si intentamos incluir algún nuevo registro con un Id repetido, la macro mostrará un error y no cargará los datos. Así evitamos tener registros duplicados.

Si no queremos este control y deseamos introducir registros que puedan ser o estar duplicados, simplemente debemos elegir “sin clave principal”.

Por otra parte, y después de haber recibido alguna consulta relacionada, si los registros que queremos añadir no son nuevos, sino que tenemos registros que se pueden repetir con los que ya tenemos en la base de datos de Access, entonces podremos indicar en la macro que a la hora de pasar nuestros datos a Access, si ya existen, no los importe y si son nuevos que los añada a la base de datos.

Solo tendréis que cambiar la secuencia SQL del ejemplo por esta otra:

obSQL = "INSERT INTO " & Destino & " SELECT * FROM" & conExcel & _
"WHERE " & Origen & ".ID NOT IN (SELECT ID FROM " & Destino & ")"

De esta forma no tendremos registros duplicados, ni tampoco nos saltarán avisos de Access indicando que no podemos añadir registros duplicados.

Para eliminar registros puede que os interese consultar el siguiente post: ELIMINAR REGISTROS EN ACCESS DESDE EXCEL CON VBA

Por último, para la macro funcione, una vez insertada la macro nuestro editor de VBA debéis marcar en la biblioteca de referencias: Microsoft ActiveX Data Objects 2.8 Library

Descarga el archivo de ejemplo pulsando en: AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA

Anuncios

19 pensamientos en “AÑADIR REGISTROS EN ACCESS DESDE EXCEL CON VBA

  1. Pingback: ELIMINAR REGISTROS EN ACCESS DESDE EXCEL CON VBA | Excel Signum

  2. Saludos, te queria consultar cual seria la diferencia si deseo pasar solo la primera fila con datos de la tabla de excel en lugar de la tabla completa o mejor aun un rango dentro de esta fila como ser A2:G2. Gracias de antemano.

    Le gusta a 1 persona

    • Hola Eduardo:

      Debes especificar el rango en el código, por ejemplo:

      Destino = "[TRABAJADORES]"
      Origen = "[EMPLEADOS$A1:G2]"

      Donde estamos importando hasta la fila 2. Es importante tenemos en cuenta que ADO debe saber el nombre de os campos a importar, por eso es necesario indicar la columna donde están los nombres de los campos.

      No deben existir registros duplicados.

      Saludos.

      Me gusta

  3. Buenos días,

    mi caso es que tengo ya una columna creada con “ID” (la primera) pero no la que crea Access al insertar una tabla desde Excel, ¿cómo podría hacer para no duplicar los datos con mi mismo ID en Access? Ya que me los está duplicando

    Muchas gracias, saludos.

    Me gusta

    • Hola STF:

      Existen varias formas de evitar los duplicados:
      – Utilizar una tabla solo con los registros nuevos a incorporar a la base de datos de access.
      – Ejecutar una consulta en access para eliminar esos duplicados.
      – En lugar de pasar registros, pasar la tabla directamente.
      – O realizar en la macro una subconsulta que que añada solo los registros nuevos.

      Creo que esto último es lo que necesitas, porque me imagino que tienes una tabla en excel en la que vas añadiendo todos los registros.

      Debes sustituir esto:

      obSQL = "INSERT INTO " & Destino & " SELECT * FROM " & conExcel

      Por esto:

      obSQL = "INSERT INTO " & Destino & " SELECT * FROM" & conExcel & _
      "WHERE " & Origen & ".ID NOT IN (SELECT ID FROM " & Destino & ")"

      De esta forma si el Id está repetido, ese registro no se importará desde Access y por lo tanto ni habrá duplicados saltarán avisos de error.

      Saludos, espero que te sirva.

      Me gusta

    • Hola William:

      El archivo funciona perfectamente con archivos xlsm. Lo que creo ocurre es que cuando indicas la fuente de datos si tu archivo es xlsm debes especificarlo en el tipo de archivos, en el ejemplo sería así: [Excel 8.0;HDR=Yes;DATABASE=F:\COMPAÑIA.xlsm]

      Por eso esa línea del código te da error, porque te falta la “m” en el xls.

      Por lo demás debería funcionarte correctamente.

      Saludos.

      Me gusta

  4. Tengo un problema para el data source: use lo siguiente:
    ruta2 = ActiveWorkbook.path & “\carpetas\” & contrato & “\Base de datos\Database.accdb”
    “Data Source= ruta2;Jet OLEDB:Database Password=xxxxxxxx”
    .Open
    .Execute obSQL
    el problema es que no me capta la ruta2 me arroja un error con una dirección distinta

    Me gusta

  5. Logre solucionar el problema anterior, pero ahora me tira otro error:

    error con el archivo excel, me sale que no encuentra la tabla 1

    Dim conAccess As ADODB.Connection
    Dim Origen As String, Destino As String
    Dim conExcel As String, obSQL As String
    ‘Definimos las tablas de Access y Excel con las que vamos a trabajar
    Destino = “[Actividades]”
    Origen = “[Tabla1]”
    Dim contrato As String
    contrato = Worksheets(“Usuario Activo”).Range(“C2”)
    Dim ruta As String
    Dim ruta2 As String
    ruta2 = ActiveWorkbook.path & “\carpetas\” & contrato & “\Base de datos\Database.accdb”
    ruta = ActiveWorkbook.path & “\carpetas\” & contrato & “\Bitácora\Bitácora.xlsm”
    ‘Cadena de conexión a nuestro archivo de Excel
    MsgBox ruta
    conExcel = “[Excel 8.0;HDR=Yes;DATABASE=” & ruta & “].” & Origen
    ‘Sentencia SQL para incluir nuevos registros no repeditos
    obSQL = “INSERT INTO ” & Destino & ” SELECT * FROM ” & conExcel
    ‘Cadena de conexión Access
    Set conAccess = New ADODB.Connection
    With conAccess
    .ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=” & ruta2 & “;”
    .Open
    .Execute obSQL
    .Close
    End With

    en el archivo de origen hay varias tablas y la que necesito es tabla1.

    Me gusta

    • Hola Carlos.

      En la macro debes hacer referencia a la hoja de Excel no a una tabla. La hoja debe llamarse Tabla1 y tienes que especificar un dólar al final para que la instrucción sepa que es una hoja:

      Origen = “[Tabla1$]”

      En resumen, renombra la hoja como Tabla1 y modifica en la macro añadiendo el dolar, te debería funcionar correctamente.

      Saludos

      Me gusta

    • Hola, he estado viendo tú código y me interesa mucho el tema de que automáticamente el macro sepa donde está situado el excel y su nombre. Esto lo tengo solucionado con

      Dim ruta As String
      ruta = ThisWorkbook.Path & “\” & ThisWorkbook.Name

      Después utilizo el trozo de código que has puesto tú:

      conExcel = “[Excel 8.0;HDR=Yes;DATABASE=” & ruta & “].” & Origen

      y me da un error de “ERROR DE COMPILACIÓN” y me señala: “].”

      ¿me podrías ayudar?

      Me gusta

      • Hola José Manuel:

        Pues lo he probado y me funciona correctamente … y es igual a lo que indicas:

        conExcel = "[Excel 8.0;HDR=Yes;DATABASE=" & ruta & "]." & Origen

        Verifica si te sigue mostrando ese error. No debería.

        Saludos.

        Me gusta

  6. Hola Segu

    Me parece genial la macro pero no entiendo como defino las columnas osea a que te refieres con esto:

    “Es importante tenemos en cuenta que ADO debe saber el nombre de os campos a importar, por eso es necesario indicar la columna donde están los nombres de los campos.”

    ps me arroja este error: Escriba un valor en el campo ‘Base General.ZAC’

    creo que se debe a eso ya es el campo ZAC es el primer campo del access y del excel y es el que esta indexado

    Mil gracias

    Me gusta

  7. Buena Tarde Segu, de antemano mil gracias por compartir tus conocimientos es de gran valor para los que estamos empezando,
    Probé tu solución y es muy funcional automatiza muchísimo tiempo.

    Sin embargo hay un error que no he podido solucionar y quisiera pedir tu ayuda para solventarlo.

    cuando realizo el proceso no tengo problema<s si solo tengo abierto el archivo de origen osea el archivo de Excel donde tengo los nuevos registros, sin embargo si llego a tener abierto otro archivo de Excel al tiempo me dispara un error donde indica que no encuentra el primer campo, lo cual me forzad a cerrar todos los archivos para realizar el registro.

    no se si de pronto me puedes ayudar con este incidente, lo que yo asumo es que al tener mas archivos abiertos no logra identificar con cual se debe realizar la conexión.

    Nuevamente mil gracias

    Me gusta

    • Hola Fabián:

      Pues no debería arrojarte ningún error, se supone que cuando pulsas el botón para ejecutar la macro, el libro está activo. Si lo haces de otra forma, quizás debas activar el libro en la macro:

      Con un ThisWorkbook.Activate al principio de la macro, debería activar el libro y de esa forma no importar el que estén otros archivos abiertos.

      Saludos

      Me gusta

¿Te ha gustado?, Realiza un comentario.

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s