por Hace unos días recibí una consulta donde me solicitaban ayuda para exportar datos desde Excel a Access. El lector había consultado la entrada CONECTAR BASE DE DATOS DE ACCESS (ACCDB) CON EXCEL UTILIZANDO HERRAMIENTA OLEBD donde se explica como conectar y traernos a nuestro Excel una tabla o una consulta desde Access.
Pues bien, me pareció bastante interesante y también tenía ganas de trabajar un poco con ADO y SQL. Así que hoy trabajaremos esta situación, y como siempre vamos a poner un ejemplo:
Imaginad que trabajamos en unos grandes almacenes y nos entregan un archivo Excel con el detalle de empleados, nombres, idiomas, fechas de nacimiento, etc …, y nosotros, que trabajamos habitualmente con Access, cada vez que nos envían la información tenemos que guardarnos la misma tabla una y otra vez para después importarla desde Access y realizar nuestros informes. Esta situación puede ser muy habitual, sobre todo para los usuarios de Access que no lo tienen tan fácil para encontrar colaboradores que trabajen con este programa.
* Si necesitáis crear bases de datos con nombres y apellidos podéis ver esta entrada donde os explico como lo podéis hacer: pinchad aquí
Siguiendo con el ejemplo, el archivo Excel que nos envían se llama «COMPAÑÍA» y la pestaña de la hoja donde tenemos la información se denomina «EMPLEADOS»:
Ahora que tenemos estos datos, debemos exportarlos a Access y para ello, vamos a trabajar con la siguiente macro:
Sub EXPORT_ACCESS()
'Definimos las variables a utilizar
Dim ConAccess As ADODB.Connection
Dim ConExcel As String, obSQL As String
Dim Origen As String, Destino As String
'Establecemos conexion con la base de datos de Access que hemos creado (EMPRESA)
'debéis verificar la ruta a vuestra base de datos.
Set ConAccess = New ADODB.Connection
ConAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=F:\EMPRESA.accdb;"
'Eliminamos la tabla TRABAJADORES y verificamos que si no existe no salte un error (Control)
On Error GoTo Control
obSQL = "DROP TABLE [TRABAJADORES]"
ConAccess.Execute obSQL
Control:
'Establacemos conexion con el archivo de Excel, desde donde vamos a exportar la tabla
'Debéis verificar la ruta a vuestro archivo excel y respetar las comillas "dobles" y "simples"
ConExcel = "'F:\COMPAÑIA.xls' 'Excel 8.0;HDR=Yes;'"
'Definimos el nombre de las tablas:
'Origen será la tabla de Excel
'Destino será la tabla de Access
Origen = "[EMPLEADOS$]"
Destino = "[TRABAJADORES]"
'Importamos la tabla a Access con los nuevos cambios que hayamos realizado en Excel
obSQL = "SELECT * INTO " & Destino & " FROM " & Origen & " IN " & ConExcel
ConAccess.Execute obSQL
ConAccess.Close
End Sub
Aunque ya explico el funcionamiento de la macro en los comentarios en verde. Vamos a incidir en algunas partes del código.
Después de insertar la macro y antes de ejecutarla debéis crear una base de datos (vacía), en este ejemplo la denomino «EMPRESA» y luego definir el nombre de la tabla que creará la macro con los datos importados de Excel, en este caso, nombro la tabla como [TRABAJADORES].
Para poder actualizar la tabla tantas veces como necesitemos, es necesario que por cada vez que se importemos los datos, realicemos: o bien una consulta de actualización, o como en este caso, he optado por eliminar directamente la tabla y volver a importarla, de esta forma actualizaremos toda la información, con este código conseguimos el borrado:
On Error GoTo Control
obSQL = "DROP TABLE [TRABAJADORES]"
ConAccess.Execute obSQL
Control:
El control del error es necesario, dado que si en la base no tenéis ninguna tabla que se llame TRABAJADORES, se generará un error por no encontrar la tabla, así lo controlamos y lo obviamos.
En caso de que solamente queráis ir incluyendo tablas, por ejemplo, por cada mes una diferente, tendríais que eliminar el código que borra la tabla, y cada vez que importaseis renombrar la tabla de Access Destino = «[la tabla x]»
Ni que decir tiene que antes de ejecutar la macro, debéis establecer las rutas correctas en vuestro equipo, en el código lo marco en rojo. En mi caso están en la unidad «D» (un pendrive).
Os muestro la base de datos de Access para que veáis el resultado:
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
Sin esta referencia, la macro no funcionará, ¡es imprescindible que la marquéis!
Ahora os dejo el enlace a Drive, en wordpress no se permite alojar archivos de Access 🙁
pero google es una buena alternativa para solucionar el problema.
Espero que os sea de utilidad!
Descarga el archivo de ejemplo pulsando en: EXPORTAR DATOS DE EXCEL A ACCESS MEDIANTE ADO
man tienes conexión con sql server?
Creó que algo salió mal brother algo falló en OnError ,al correr la macro aparece el error » Se ha producido el error ‘-2147217900 (80040e14)’ en tiempo de ejecución:
La tabla ‘TRABAJADORES’ ya existe
Hola Christian,
Antes de nada, prueba a ejecutar la macro de nuevo. Entra en el editor y ejecútala. Si has realizado modificaciones es posible que te sigo informando de un error anterior que ahora ya no existe, pero que es necesario restablecer y ejecutar de nuevo. Si este no es el problema:
Asegúrate que la sentencia:
obSQL = «DROP TABLE [TRABAJADORES]»
ConAccess.Execute obSQL
Está exactamente igual, sobre todo [TRABAJADORES]
El control On error GoTo es para controlar errores por si no existen tablas. El error que indicas es porque no ha borrado la tabla Trabajadores. Por ello debes controlar la sentencia que te indico.
Si el problema persiste. Enviáme los dos archivos (excel y access con un ejemplo de tabla y verifico cúal puede ser el problema. La macro funciona perfectamente en Excel 2007, 2010 y 2016.
Saludos.
amigo muchas gracias por esta información, estaba necesitando una macro que no guardara registro por registro por que tada mucho de esa forma cuando son muchisimos datos. agradecido
Es verdad funciona perfecto, ya resolví mi problema con Office 2016.
Saludos
Hola Segu, en este mismo ejemplo si solo quieres pasar x columnas, por ejemplo id, nombre y seccion, como se completa.
Saludos
Hola Joaquín:
Es sencillo, en la parte de la macro en la que construimos la sentencia SQL:
obSQL = "SELECT * INTO " & Destino & " FROM " & Origen & " IN " & ConExcelDebes especificar aquellos campos que te interesan en lugar de exportar la tabla completa, es decir, así:
obSQL = "SELECT [EMPLEADOS$].[ID], [EMPLEADOS$].[NOMBRE COMPLETO], [EMPLEADOS$].[SECCION] INTO " & Destino & " FROM " & Origen & " IN " & ConExcelSaludos.
Perfecto, funciona como un reloj suizo.
Gracias
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.
Esta consulta ya ha fue respondida en otro post. Saludos
Es posible hacer ese remplazo de la tabla si esta activa en red?
Hola Jorge:
Sí, el reemplazo lo puedes realizar perfectamente con la tabla abierta.
Saludos.
Hola Segu,
He leído y probado tu tutorial y funciona perfecto.. Muchas gracias por el trabajo.
Viendo este ejemplo, corrígeme sino me equivoco, el id es la clave de cada registro.
Me gustaría saber como puedo hacer una consulta desde excel donde me devuelva los datos de un registro concreto, especificando solo la llave. (ya sea 1 llave o varias).
Gracias!
Hola Manu:
Perdona por no responder antes. Respecto a tu pregunta, supongo que te refieres a la forma de importar a Excel información de Access, eso lo puedes hacer con la información de este post:
https://excelsignum.com/2014/02/27/conectar-base-de-datos-de-access-accdb-con-excel-utilizando-herramienta-olebd/
Aunque en este ejemplo, lo que estamos importando son tablas enteras de Access a Excel, para consultar un registro o registros determinados, solo tienes que modificar la consulta SQL dentro de la macro.
Saludos
Hola una consulta, tire la macro pero me sale un error que dice » No se puede actualizar.Bases de datos u objeto de solo lectura. a que se refiere ese error, saludos
Hola Leonardo, me imagino que has ejecutado la macro con la tabla de access que vas a borrar abierta. Por eso te marca un error de que la base que estás intentado eliminar para crear otra, está abierta y es de solo lectura.
Saludos.
Hola, estoy intentando ejecutar el código con el ejemplo descargado, habiendo cambiado la ruta, y me da el error que no puede encontrar el objeto «EMPLEADOS».
Por otra parte se podría hacer para que en vez de borrar la tabla, fuese añadiendo como datos anexados.
Estoy usando Office 2016. Un saludo
Bien, he conseguido quitar el error y ya funciona, y ahora sería posible la segunda parte, que los datos fuesen anexados a la tabla en vez de borrarla.
Hola Francisco.
Mejor mira este post. En la parte final contesto a tu pregunta:
https://excelsignum.com/2016/07/08/anadir-registros-en-access-desde-excel-con-vba/
Muchas gracias, exactamente lo que buscaba. Me ha servido de mucho
Buen día al ejecutar la macro me genera un error Run-time error ‘-2147467259 (80004005)’ Could nota find installable ISAM
Hola HAzal: Ese error es debido a la versión de Excel que tienes. Puedes probar a cambiar lo siguiente en la macro: esta parte:
«Provider=Microsoft.ACE.OLEDB.12.0;»
Por esta otra:
«Provider=Microsoft.Jet.OLEDB.4.0;»
Así debería funcionarte.
Saludos.
Gracias se solucionó mi problema ahora me surgió otro quisiera generar tablas nuevas con nombres diferentes
Es posible hacer que las tablas se guarden con el nombre del contenido de una celda
Hola Hazal:
Tendrías que utilizar una variable para guardar el nombre de la celda y luego utilizarla en la consulta.
De todas formas, creo que deberías examinar esta otra entrada que quizás te resulta de mayor utilidad:
https://excelsignum.com/2016/07/08/anadir-registros-en-access-desde-excel-con-vba/
Cuál sería la sintaxis a colocar en la consulta, ya que la he puesto de diversas maneras y no me funciona
Hola Alan:
Qué quieres que haga esa consulta??. Has probado a utilizar a añadir registros directamente con el post que te recomendé??
Si ya probé la recomendación sin embargo no me funciona, requiero que se cree una tabla nueva con un nombre nuevo cada vez
Funciona maravillosamente
Pregunta
Como podría subir un rango Ejemplo «A1:G199» en lugar del objeto Origen = «[PRUEBA$]»
Esto con el fin de poder tener mas tablas en la Hoja Pruebas.
De antemano mil gracias
Hola Agustín:
Aunque no lo he probado, debería funcionar así correctamente.
«[PRUEBAS]$A1:G199”
O así: [Worksheets(“PRUEBAS″)$A1:G199]
Saludos.
Hola Excelente Video lo probe en local y funciona, ahora me esta dando error al colocar el access y el excel en un servidor , debe ser que estoy colocando mal la ruta, en el caso que los archivos reposen en un servidor como tendria que ir?
ConAccess.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & _
«Data Source=Z:\BBDD\EMPRESA.accdb;»
Así estaría correcta la ruta. Has visto si tiene contraseña el access?.
Que error te muestra?