por 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:
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):
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:
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:
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
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.
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.
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.
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 " & conExcelPor 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.
Solo funsiona con excel guardado en xls. Como lo puedo ejectuar en un excel Xlsm ya que la conexion [Excel 8.0;HDR=Yes; falla. ME podrian apoyar
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.
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
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.
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
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?
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 & "]." & OrigenVerifica si te sigue mostrando ese error. No debería.
Saludos.
Funciona perfectamente, Muchas Gracias…
TENGO UN ERROR POR QUE VARIAS CELDAS TIENEN MAS DE 255 CARACTERES COMO HAGO PARA QUE SE CARGUE COMPLETO
Hola jhon:
Has probado a configurar el campo en access, que sea texto o memo para que te incluya el número máximo de caracteres?.
Saludos.
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
Hola Jennyfer:
Necesitaría ver el archivo para verificar cuál es el problema. Pues enviarme el access y el excel con datos inventados (no hace falta que sean originales) y así testeo cuál puede ser el problema).
Envia a excelsignum@yahoo.es
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
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
Hola Segu, que pena responder hasta ahora, funciona de mil maravillas, de ante mano mil gracias, quisiera pedirte un apoyo mas para mejorar el proceso,
Como podría hacer que la macro valide primero si se encuentra el registro, si lo encuentra que actualice y si no lo encuentra que lo inserte. al modificar tu código le indique que inserte el registro que no tenga la llave primaria, como llave primaria tengo un campo diferente a id automático de access.
nuevamente mil gracias.
Hola Fabian:
Para hacer lo que comentas, se tendría que hacer una consulta de actualización y luego otra para los registros nuevos. Pero una consulta de actualización es compleja y debes verificar todos los campos donde se haya realizado un cambio.
Lo más útil para estos casos es pasar un delete sobre aquellos ID que coincidan en el Excel y el Access y luego volver a cargar, de modo que actualizará todos los campos.
Sería algo así:
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=C:\COMPAÑIA.xls]." & Origen
'Sentencia SQL para incluir nuevos registros no repeditos
fin = Application.CountA(Range("A:A"))
'eliminamos registros que existen en Excel y Access
For i = 2 To fin
obSQL_2 = "DELETE * FROM " & Destino & " WHERE " & Destino & ".Id = " & Cells(i, 1)
'Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\EMPRESA.accdb;"
.Open
.Execute obSQL_2
.Close
End With
Next i
'Actualizamos de nuevo
obSQL = "INSERT INTO " & Destino & " SELECT * FROM" & conExcel & _
"WHERE " & Origen & ".ID NOT IN (SELECT ID FROM " & Destino & ")"
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\EMPRESA.accdb;"
.Open
.Execute obSQL
.Close
End With
End Sub
Hola segu, la verdad no me es de importancia validar que campos han cambiado si no si el registro ya esta que se actualice todo sin importar que campos sean diferentes y si el registro no esta entonces que lo inserte.
Creería que el código anterior me sirve si cambio la sentencia de Delete por Update, no se si funcione de esta forma.
Hola Fabián:
No tienes que hacer un update, primero debes hacer un delete y luego un insert into, que es lo que hace la macro. Así grabas los registros que no tienes y también el resto.
Saludos.
Hola Segu disculpa por la intensidad, tu solución es muy buena y la aplique a mi proceso, sin embargo tengo inconveniente que no me deja finalizar el proceso.
necesito cambiar el campo de la condición, donde busca el registro WHERE » & Destino & «.Id = » & Cells(i, 1), me explico que no busque por el «Id» si no por otro campo que no es la llave primaria, en realidad es la concatenación de dos campos de la tabla » WHERE » & Destino & «.numerodocumento & idgrupo= » & Cells(i, 3) «, pero no logro que funcione. si lo dejo solo por el Id, si me funciona pero el id en este momento no es mi llave primaria.
Te agradecería si me puedes dar otra manito.
Hola Fabian:
Necesito ver esa tabla. Si puedes enviame el excel y el access con el código que tienes y un ejemplo con datos en ambos archivos indicándo qué campos debes concatenar.
Así podré ayudarte. excelsignum@yahoo.es
Mira este es el código, al principio hace algunas validaciones y luego inserta los registros, la parte de insertar funciona perfecto, si encuentra el registro no lo vuelve a insertar. comente la parte donde primero elimina, por que como te comente no logro que lo realice.
Ya te envío el archivo, sin embargo por la cantidad de conexiones que tiene no se si logre funcionar. de antemano mil gracias.
‘Insertar datos a Access
Sub InsertAccess()
Dim conAccess As ADODB.Connection
Dim Origen As String, Destino As String
Dim conExcel As String, obSQL As String
Dim Rol, Tipodocumento As Range
Dim Celda As Object
‘Validamos que todos los campos obligatorios esten diligenciados
ult = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To ult
If Cells(i, 1).Value = 1 Then
MsgBox («Por favor diligenciar todos los campos obligatorios»), vbCritical, «Campos vacios»
Cells(i, 22).Offset(1, 0).Select
Exit Sub
End If
Next
‘Llamamos procedimientos, Limpiamos registros de caracteres especiales, convertimos tipo de letra
Call convertiranopropio
Call eliminarpuntosycomas
Call convertirenmayuscula
Call convertirenminuscula
Call EliminarEspacios
Call remplazartildes
Range(«Y2»).Select
‘Validamos que el rol sea el correcto
Set rolr = Range(«Tabla3[[Rol]]»)
Set rolt = Range(«Tabla2[[descripcion]]»)
encontrado = 0
For Each Celda In rolr
tem1 = Celda
For Each Celda1 In rolt
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («El Rol ingresado no pertenece a la lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE EL ROL!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos que el tipo de documento sea correcto
Set tipodr = Range(«Tabla3[[Tipo de Documento]]»)
Set tipodt = Range(«Tabla1[[descripción]]»)
encontrado = 0
For Each Celda In tipodr
tem1 = Celda
For Each Celda1 In tipodt
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («El tipo de Documento no pertenece a la Lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE TIPO DE DOCUMENTO!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos la ciudad del documento
Set ciudaddr = Range(«Tabla3[[Ciudad del documento]]»)
Set ciudadt = Range(«Tabla8[[Nombre]]»)
encontrado = 0
For Each Celda In ciudaddr
tem1 = Celda
For Each Celda1 In ciudadt
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («Un registro del campo ciudad del documento no pertenece a la lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE LA CIUDAD DEL DOCUMENTO!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos que el genero sea correcto
Set generor = Range(«Tabla3[[Sexo]]»)
Set generot = Range(«Tabla10[[descripcion]]»)
encontrado = 0
For Each Celda In generor
tem1 = Celda
For Each Celda1 In generot
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («El genero no pertenece a la Lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE TIPO DE GENERO!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos lista de egresado sea correcto
Set egresado = Range(«Tabla3[[Egresado.]]»)
For Each Celda In egresado
If Celda «SÍ» And Celda «NO» And Celda «» Then
MsgBox («Un registro del campo Egresado no pertenece a la Lista desplegable, por favor validar»), vbCritical, «CAMPO EGRESADO ERRADO»
Exit Sub
End If
Next Celda
‘Validamos laprofesion
Set profesionr = Range(«Tabla3[[Profesión.]]»)
Set profesiont = Range(«Tabla9[[descripcion]]»)
encontrado = 0
For Each Celda In profesionr
tem1 = Celda
For Each Celda1 In profesiont
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («Un registro del campo Profesión no pertenece a la lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE LA PROFESIÓN!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos la ciudad de residencia
Set ciudadr = Range(«Tabla3[[Ciudad de residencia]]»)
Set ciudadt = Range(«Tabla8[[Nombre]]»)
encontrado = 0
For Each Celda In ciudadr
tem1 = Celda
For Each Celda1 In ciudadt
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («Un registro del campo ciudad de residencia no pertenece a la lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE LA CIUDAD DE RESIDENCIA!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Validamos el medio de difusión
Set medior = Range(«Tabla3[[Medio de difusión]]»)
Set mediot = Range(«Tabla7[[descripcion]]»)
encontrado = 0
For Each Celda In medior
tem1 = Celda
For Each Celda1 In mediot
tem2 = Celda1
If tem1 = «» Then
encontrado = 1
Else
If tem1 = tem2 Then encontrado = 1
End If
Next Celda1
If encontrado = 0 Then
MsgBox («El Medio de difusión no pertenece a la lista desplegable, por favor validar.»), vbCritical, «¡NO EXISTE EL MEDIO DE DIFUSIÓN!»
Exit Sub
End If
encontrado = 0
Next Celda
tem1 = «»
tem2 = «»
‘Definimos las tablas de Access y Excel con las que vamos a trabajar
Destino = «[Participantes]»
Origen = «[Participantes$D:Y]»
»eliminamos registros que existen en Excel y Access
‘fin = Application.CountA(Range(«A:A»))
‘For i = 2 To fin
‘obSQL_2 = » DELETE * FROM » & Destino & » WHERE » & Destino & «.numerodocumento & idgrupo= » & Cells(i, 3)
»Cadena de conexión Access
‘Set conAccess = New ADODB.Connection
‘With conAccess
‘.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=\\10.26.1.76\Apertura programas\BD_DECC_fase_1_be.accdb;»
‘.Open
‘.Execute obSQL_2
‘.Close
‘End With
‘Next i
‘Cadena de conexión a nuestro archivo de Excel
conExcel = «[Excel 8.0;HDR=Yes;DATABASE=\\10.26.1.76\Apertura programas\Participantes.xlsm].» & Origen
‘Sentencia SQL para incluir nuevos registros no repeditos
‘obSQL = «INSERT INTO » & Destino & » SELECT * FROM » & conExcel
obSQL = «INSERT INTO » & Destino & » SELECT * FROM » & conExcel & _
«WHERE » & Origen & «.numerodocumento & idgrupo NOT IN (SELECT numerodocumento & idgrupo FROM » & Destino & «)»
‘Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=\\10.26.1.76\Apertura programas\BD_DECC_fase_1_be.accdb;»
.Open
.Execute obSQL
.Close
End With
MsgBox «Registros Cargados», vbInformation, «Proceso Éxitoso»
Call convertirCSV
‘Limpiamos tabla y eliminamos filas
Range(«Z2»).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Rows(«4:4»).Select
‘Range(«F4»).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range(«Z2»).Select
resultado = MsgBox(«¿Desea cargar los registros al consolidado de asistencia?», vbQuestion + vbYesNo, «Cargar registros»)
If resultado = vbYes Then
MsgBox «Recuerde que el archivo quedo alojado en la ruta:» + Chr(13) + «\\10.26.1.76\Apertura programas\Carnes\Registros participantes\», vbInformation, «Tener en cuenta»
Shell «explorer » & «http://asistenciadec.bitacorajaveriana.co/carga_masiva.php», vbNormalFocus
End If
ActiveWorkbook.Close savechanges:=False
End Sub
Hola Fabian:
Cuando te comenté el tema de enviarme un ejemplo, es el archivo de Excel y access con los datos en cada uno de ellos, no necesito toda la macro, solo la parte que no te funciona bien y en el archivo de Excel y también el access con los datos.
Envíame los dos archivos con unos pocos datos de ejemplo, reproduciendo el error que indicas. No obstante, el problema que tienes creo que se debe a los espacios y la comillas, pero tengo que verificarlo.
Saludos.
Buenas tardes, he visto tu codigo y es bastante rapido, sin embargo en mi caso necesito actualizar algunos campos en el excel y se vean reflejados en el access, y realmente no lo he podido solucionar, Ojala pudieras apoyarme
Hola Eder, no comprendo qué es lo que quieres hacer. Pero en cualquier caso, con esta macro actualizas access desde excel, si deseas lo contrario, este post te podría ayudar:
https://excelsignum.com/2016/12/09/exportar-una-tabla-o-consulta-de-access-a-excel-con-ado/
Hola Segu,
Favor si me pudieras ayudar, todo funciona perfecto. No ingrese el campo id por que por el momento no creo necesitarlo.
El problema es que necesito comparar campos antes de anexarlos en la base de datos access. en resumen los datos que anexo son diarios,una base de 1000 registros todos con la misma fecha, al dia siguiente son 1000 mas (variable) con la fecha del dia en curso. Pero necesito validar que si por ejemplo ejecuto por error la macro dos veces en el dia, quede con la ultima data anexada.
Intenté agregar un IF pero no me funciona por que no se tomar los campos en cada una de las tablas, Destino y Originen.
Ejemplo:
With conAccess
.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=C:\Users\davila\Desktop\Cuadratura\Proceso 4\Respaldo Modificaciones.accdb;»
.Open
‘comprobar si lo que se va a anexar ya existe.
If (Origen.[«Fecha»] = Destino.[«Fecha»]) Then ‘No se como tomar campos fecha en cada base.
MsgBox «Datos Duplicados», vbInformation, «BD»
‘Que elimine el anterior con misma fecha y anexe nuevos datos.
.Execute obSQL
Else
.Execute obSQL
End If
.Execute obSQL
.Close
End With
Hola David.
No necesitas realizar ningún if. Lee la última parte del post, con esa sentencia siempre vas a cargar datos nuevos.
Quizás deberías introducir in cuadro de advertencia para obligarte a verificar los datos o la macro antes de ejecutarla.
Saludos
Hola David,
Me podrías indicar como puedo guardar los resultados de una consulta con SELECT en un variable usando el codigo que mostraste? Gracias
buenas tardes amigo como estas, estoy usando tu código con algunas modificaciones:
Sub InsertAccess()
Dim conAccess As ADODB.Connection
Dim Origen As String, Destino As String
Dim conExcel As String
Dim obSQL As String
Dim SQL As String
‘Definimos las tablas de Access y Excel con las que vamos a trabajar
Destino = «[Empleados]»
Origen = «[EMPLEADOS$A1:AN4243]»
‘Cadena de conexión a nuestro archivo de Excel
conExcel = «[Excel 8.0;HDR=Yes;DATABASE=Z:\Relacion laboral\MILI\CONSTANCIAS\DATOS.xls].» & Origen
‘Sentencia SQL para vaciar tabla y registrar nuevamente
SQL = «delete from » & Destino ‘Limpio la tabla de Acces para vaciar desde cero
obSQL = «INSERT INTO » & Destino & » SELECT * FROM » & conExcel ‘Inserto los campos nuevamente
‘Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=Z:\Relacion laboral\MILI\CONSTANCIAS\datos.mdb; Jet OLEDB:Database Password=rrhh;»
.Open
.Execute SQL
.Execute obSQL
.Close
End With
End Sub
lo que me sucede es que la macro me corre bien pasando mi base de datos de Excel a Access, solo que mi base de datos tiene 10.000 filas y solo me exporta hasta la 4243 (Origen = «[EMPLEADOS$A1:AN4243]») esto colocando q exporte un rango especifico, ya he probado números inferiores y los pasa excelente pero cuando excedo ese valor da Error : se ha perdido conexión con microsoft excel y sus hojas vinculadas.
quería ver si me podrías ayudar.
gracias de antemano
Hola Yordin:
No reconozco el erro que indicas. No obstante, quizás el método para realizar la macro debería ser usando un for each para pasar cada una de las consultas.
En este ejemplo uso ese método, aunque la consulta es sobre Excel, la idea sirve para la macro que tienes, es decir, crea las dos consultas, la de borrar y la de añadir (SQL y OBSQL), luego con un for each realizar las dos consultas.
Lee detenidamente el post que te indico: https://excelsignum.com/2018/08/02/realizar-varias-consultas-sql-con-ado-en-la-misma-macro/
Saludos.
Hola Segu!, disculpa si ya has dado respuesta a esto, tengo una pregunta! ojala puedas ayudarme.
tengo un tabla enorme con datos repetidos : ejemplo
Material1|venta 200|enero 12|2019
material1|venta300|enero 13|2019
cual seria el comando sql (excel VBA) para que me exporte y me muestre en la tabla de access todos los datos. la idea es realizar un repositorio de ventas consolidado que se vaya actualizando a medida que le voy metiendo datos nuevos conforme los días pasan
saludos,
Hola Pablo:
Has probado esto?
https://excelsignum.com/2016/07/08/anadir-registros-en-access-desde-excel-con-vba/
O este tb te puede servir:
https://excelsignum.com/2016/03/31/exportar-datos-de-excel-a-access-mediante-ado/
Saludos
Buenas tardes amigos
Tengo unos campos en excel que tengo que diligenciar ya sea de una listo o digitados. Después de hacer esto quiero crear una base de datos pero que se guarde cada registro en access, ya que cada vez que diligencie las celdas son informaciones distintas y me gustaría que con un botón haga
*seleccionar las celda diligenciadas de excel
*guardar la información en access
teniendo en cuenta que le registro no esta consecutivo en las celdas ejemplo (a3-d5-a8-d10)
ya que no quiero tener la información en el mismo archivo por que es información que no todas las personas devén acceder a esta
Hola Juan David:
Para hacer lo que indicas implica realizar una programación nueva, en cuento tenga tiempo lo intento. No obstante, lo más profesional es pasar el archivo de Excel a Access o los registros (por filas a Access), que es lo que hace este post.
Saludos
Buenas noches Segu,
Gracias por todo lo que publicas nos sirve mucho, leyendo los post encuentro el de Fabian pregunta lo siguiente: «».. que no busque por el «Id» si no por otro campo que no es la llave primaria..» Yo también tengo este caso, mi llave primaria no es el automático, si no por un código de venta único asignado a la venta que se encuentra en la columna H en mi caso. Y éste código se llama «Código de Orden». Por favor podrías ayudarme cómo se sería el código?. Gracias de antemano por la respuesta. Saludos
Y cuál es el problema?, debería funcionarte igualmente, solo que ahora debes controlar los duplicados.
Porque no echas un vistazo a la respuesta que le doy a STF
Saludos.
Perfecto , Muchas gracias funciona!!
Buenas Tardes Segu, cuando la clave principal es un número corre perfecto , cuando lo cambié a texto me sale el siguiente error:
Se ha Producido el error ‘-2147217913 (80040e07)’ en tiempo de ejecución : No coinciden los tipos de datos en la expresión de criterios.
Sucede que tengo una columna donde quiero que funcione como clave y tiene los siguientes datos, Ejemplo: 69252326, 69252326A, 69252326B, 69784521 etc, cuando lo subo a acces en formato número sólo me sube los códigos que que no tienen alguna letra y los que tienen letra no suben a acces, entonces he tenido que configurar la columna en acces como tipo texto corto para ver todos los códigos. Ahora al correr la macro para que me elimine aquellas coincidencias en el excel y vuelva a subir y los que son nuevos que se suban me bota el error mencionado. Cómo puedo hacer que corra la macro para éste caso ? gracias por el apoyo
Hola Aníbal. Cuando hablamos de bases de datos, o bien el campo es numérico o bien alfanumerico, pero nunca puede contener registros numéricos y alfanuméricos al mismo tiempo.
La alternativa es que ingreses los datos con un único tipo. Dado lo que tienes, te recomiendo que sea alfanumérico, pero para eso los registros de número los debes pasar a texto. Para ello, en excel intenta con un concatenar al principio del número con una comilla simple:’69252326
Debería funcionar correctamente.
Saludos.
Buen día,
Por favor si pudieras ayudarme con la configuración de la llave en acces, en mi archivo excel quiero que la columna ORDEN sea mi llave principal en Acces, por el cual lo convertía a Alfanumérico como por ejemplo: ‘69751660, 69751660A, ‘69751754…
En Acceso la columna ORDEN está con la llave principal y de tipo Texto Corto:
Y me sale el siguiente error: «No coinciden los tipos de datos en la expresión de criterios»
Lo curioso es que hasta ahora sólo he logrado que funcione con los números, pero con el texto no logro hacer que corra la macro. Por favor tu apoyo en qué puedo estar fallando.
Gracias de antemano por el apoyo brindado.
Saludos
Hola Aníbal, verifica que todos los campos exportado son texto, el error indica que alguno de los elementos no lo es.
Saludos
Buena noche, De ante mano muchas gracias por compartir tus conocimientos,
Tengo la siguiente novedad, al momento de correr la Macro pe sale el siguiente error
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 = «[DEVOLUCIONES]»
Origen = «[NDE$]»
‘Cadena de conexión a nuestro archivo de Excel
conExcel = «[Excel 8.0;HDR=Yes;DATABASE=C:\Users\andres.munoz\Desktop\Brayi\INVENTARIOS\BODEGAS JDH\FORMATO\LOGISTICOS JDH\BASE.xlsb].» & Origen
‘Sentencia SQL para incluir nuevos registros no repeditos
‘para que registre nuevos datos obSQL = «INSERT INTO » & Destino & » SELECT * FROM » & conExcel
obSQL = «INSERT INTO » & Destino & » SELECT * FROM» & conExcel & _
«WHERE » & Origen & «.ID NOT IN (SELECT ID FROM » & Destino & «)»
‘Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=C:\Users\andres.munoz\Desktop\Brayi\INVENTARIOS\BODEGAS JDH\FORMATO\LOGISTICOS JDH\BASE DEVOLUCIONES.accdb;»
.Open
.Execute obSQL
.Close
End With
End Sub
Error: Se ha producido un error ‘-2147217904 (80040e10)’ en tiempo de ejecución
No se han especificado valores para algunos de los parámetros requeridos
Quedo atento
Mil gracias
Me ocurre exactamente lo mismo: estoy tratando de encontrar qué valor es el que no está especificado…. Por favor, si encuentras la solución, coméntala.
Gracias y saludos
Debes repasar el tipo de información de los campos. Hs probado a generar el ejemplo del post?,
Buenos días:
El ejemplo del post me funciona correctamente. El problema se genera al cambiar la secuencia inicial de SQL, por la versión que indicas en los comentarios para incluir sólo los nuevos registros:
(obSQL = «INSERT INTO » & Destino & » SELECT * FROM» & conExcel & _
«WHERE » & Origen & «.ID NOT IN (SELECT ID FROM » & Destino & «)»)
Te paso mi código y el error que me sale al ejecutarlo:
Sub Exportar_Access_2()
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 = «[Geología]»
Origen = «[Hoja2$]»
‘Cadena de conexión a nuestro archivo de Excel
conExcel = «[Excel 8.0;HDR=Yes;DATABASE=C:\Users\tbelver\Desktop\LABORATORIO\Excel reporte datos\Geología\Propuesta Rubén\PLANTILLA PRIMARIOS_v82.xlsm].» & Origen
‘Sentencia SQL para incluir sólo los registros nuevos, no repetidos
obSQL = «INSERT INTO » & Destino & » SELECT * FROM» & conExcel & _
«WHERE » & Origen & «.ID NOT IN (SELECT ID FROM » & Destino & «)»
‘Cadena de conexión Access
Set conAccess = New ADODB.Connection
With conAccess
.ConnectionString = «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=C:\Users\tbelver\Desktop\LABORATORIO\Excel reporte datos\Planta\Base de datos laboratorio.accdb;»
.Open
.Execute obSQL
.Close
End With
End Sub
«Se ha producido el error ´-2147217904 (80040e10)´en tiempo de ejecución:
No se han especificado valores para algunos de los parámetros requeridos»
Quedo en espera de tus comentarios.
Muchas gracias y saludos
Debes verificar la información de los campos que estás usando. Tendría que tener un ejemplo del error que te aparece para poder evaluarlo.
Excelente Post Segu, una consulta.
Si deseo tomar como origen varias paginas de Excel como quedaria el codigo?
Muchas gracias!
Tendrías que realizar un loop indicando en una variable el origen de cada dato. Se tendría que volver a reprogramar todo, si tengo tiempo subo un ejemplo
SI fuese así estaría mas que agradecido, no encuentro la vuelta a poder poner como origen mas de una pagina de Excel.
Muchas gracias!
Buenas tardes, quisiera saber que debo hacer si de la hoja de excel que voy a importar solo necesito algunas columnas, por ejemplo: requiero importar los datos de la columna A y asignaros a un campo, de igual forma los datos de la columna E y asignarlos a otro campo.
De antemano gracias
Aunque no es mismo post, tendrías que modificar la sentencia SQL especificando los campos que quieres incluir. Intento hacer un ejemplo cuando tenga un poco de tiempo.