Transferir datos de Excel a Access – segunda nota.

jueves, junio 11, 2009

En la nota anterior vimos como transferir datos de Excel a Access manualmente usando los mecanismos nativos de Access.
Otra alternativa es exportar los datos de Excel a Access programáticamente, es decir usando Vba.

Para nuestro ejemplo usaremos nuevamente el archivo de la nota anterior.

En la nota anterior usamos los mecanismos de Access para importar los datos de Excel.
Al realizar la tarea con Vba haremos el camino a la inversa, decir crearemos una conexión a Access desde Excel. Esto lo haremos usando objetos de la ADO library (ADO = ActiveX Data Objects).

Antes de sumergirnos en los vericuetos del código, una observación importante: para poder usar las funciones de ADO en Excel debemos primero crear una referencia con el menú Tools-Reference del editor de Vb



Transferir datos de Excel a Access

Como en el ejemplo anterior, empezamos por crear una base de datos que contiene una tabla a la cual queremos exportar los datos de Excel. Nuestra base de datos (bd1.mdb) contiene la tabla Ventas que por ahora está vacía

Transferir datos de Excel a Access

Sin entrar demasiado en detalles técnicos, podemos describir la tarea de la siguiente manera:

# - creamos una conexión a la base de datos

#- definimos en qué tabla de la base de datos queremos poner los datos

# - definimos un objeto que contenga los datos a transferir (recordset)

# - definimos donde se encuentran los datos a transferir (en nuestro ejemplo el cuaderno tiene dos hojas con datos)

# - transferimos los datos

He agregado la hoja Parámetros en la cual podemos definir donde se encuentra la base de datos, cuál es la tabla a la cual queremos copiar los datos y en qué cuaderno se encuentran. De esta manera, podemos controlar los parámetros del código con facilidad.
Transferir datos de Excel a Access

Como ven he agregado también la línea a partir de la cual hay que transferir los datos de la hoja. En general los datos empiezan en la fila 2 (la 1 está ocupada por los encabezamientos). También podemos elegir de qué hoja pasar los datos, con una lista desplegable en la celda B3.
El código de la macro es el siguiente

Sub ExcelaAccess_ADO()

Dim Conn As ADODB.Connection, RecSet As ADODB.Recordset
Dim fila As Long, primerFila As Integer, ultimaFila As Long, iX As Long
Dim dataSource As String, Tabla As String
Dim wsName As String


'definimos los parametros que seran usados por el codigo

dataSource = Sheets("parametros").[B1]
Tabla = Sheets("parametros").[B2]
wsName = Sheets("parametros").[B3]
primerFila = Sheets("parametros").[B4]


' establecemos la conexion a la base de datos

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
dataSource & ";"

' definimos un recordset
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable

ultimaFila = WorksheetFunction.CountA(Sheets(wsName).Range("A:A"))

For iX = primerFila To ultimaFila 'mientras que la celda la columna A
'tenga un valor transferimos la fila
With RecSet
.AddNew
.Fields("Factura") = Sheets(wsName).Cells(iX, 1).Value
.Fields("Fecha") = Sheets(wsName).Cells(iX, 2).Value
.Fields("Producto") = Sheets(wsName).Cells(iX, 3).Value
.Fields("Descripcion") = Sheets(wsName).Cells(iX, 4).Value
.Fields("Cantidad") = Sheets(wsName).Cells(iX, 5).Value
.Fields("Precio") = Sheets(wsName).Cells(iX, 6).Value
.Fields("Total") = Sheets(wsName).Cells(iX, 7).Value
.Update
End With
Next iX


RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
End Sub



Este código debe ponerse en un módulo común de Vba.

35 comments:

Anónimo,  18 enero, 2012 20:37  

Hola Buenas Tardes..Muy bueno tu codigo pero me gustaria saber como esta dispuesto en excel y en access lo que estas transfiriendo porque estoy tratando de ahcer lo mismo y no he podido...

Agradeceria mucho tu ayuda

Jorge L. Dunkelman 18 enero, 2012 21:37  

Tal como está explicado en la nota. ¿Qué es lo que no te sale?

sr bayona 27 junio, 2012 23:32  

tengo una duda el codigo cuando se exportan los datos a la hora de crear un nuevo registro este lo superpone al anterior o lo crea en el orden que se lleva

Jorge L. Dunkelman 30 junio, 2012 10:49  

Los registros se van agregando.

Anónimo,  21 agosto, 2012 01:56  

Hola yo estoy tratando de hacer lo mismo, estuve buscando la forma y encontre lo que has publicado, yo estoy trabajando con access, el codigo que pusiste lo puse en un modulo asi como lo indicas, pero al momento de correrlo me muestra el siguiente error:

Error de compilación
Sub o función no definida
Me podrías ayudar por favor

Jorge L. Dunkelman 21 agosto, 2012 11:09  

¿Has creado la referencia a la ActiveX Data Objects Library cono está indicado al principio de la nota?

Anónimo,  21 agosto, 2012 19:01  

Hola soy Azul.

Yo estoy buscando la froma de pasar un excel a access y vi lo que has publicado, estoy probando tu codigo, pero tengo unas dudas, lo que pones como sheets es el nombre de la hoja de excel en donde se encuetran los datos a pasar

Jorge L. Dunkelman 23 agosto, 2012 14:52  

Azul,

si te refieres a Sheets(wsName), la variable wsName contiene el nombre de la hoja con los datos que hemos definido con
wsName = Sheets("parametros").[B3]

donde la celda B3 de la hoja "parametros" contiene el nombre de la hoja ("abril" en nuestro ejemplo).

08Madrid 05 octubre, 2012 15:22  

Buenas tardes,
Estoy intentando hacer este ejercicio y creo que no me encuentra donde tengo guardad la excel ventas_access_vba el compilador falla con el error de "error en el método sheets" cuando le doy a depurar me marca: dataSource = Sheets("parametros").[B1]

Jorge L. Dunkelman 06 octubre, 2012 15:46  

¿La hoja "parametros" existe en tu modelo?

Armando Acosta Salas 15 octubre, 2012 21:03  

Muchas gracias Jorge, excelente ejemplo, justo lo que estaba buscando.

Realicé tu ejercicio, y está bien estructurado.

Para estos ejercicios es importante aclarar a los usuarios que requieren conocimientos basicos de base de datos asi como programación en VBA.

Fuera de ello, todo esta super bien, muchas gracias por compartirnos tus conocimientos.

Anónimo,  07 diciembre, 2012 22:01  

Estimado,
gracias por el codigo. Sabes, estoy usando Access 2007 y al pasar por el reconocimiento de la base de datos, me arroja un error, el que indica que no reconoce el formato de la base de datos.
me podrias ayudar?

agradecido

Carlo Leon,  07 diciembre, 2012 22:16  

Muchas gracias jorge, el ejemplo esta buenisimo.
tengo una duda. pues estoy ocupando Access 2007 y me arroja un error que no reconoce el formato de la tabla de datos. en el ejemplo es mdb y las que uso son accdb. sabes por donde puedo corregir ese detalle?.
agradecido

Jorge L. Dunkelman 09 diciembre, 2012 18:05  

Releyendo el comentario, reemplazar el código

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
dataSource & ";"

por

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & _
dataSource & ";"

Carlo Leon,  10 diciembre, 2012 14:29  

Funciono Perfecto.
muchas gracias

Anónimo,  27 diciembre, 2012 06:28  

Buenas noches. Antes que nada me parece excelente tu blog.
Ahi va mi pregunta, me sale el siguiente error:
"error 3706: no se encontro el proveedor especificado".
Los pasos arriba detallados los segui todos, tenes idea que puede estar pasando?
Gracias.
Matias

Jorge L. Dunkelman 27 diciembre, 2012 07:24  

Matías,
¿qué versión de Excel estás usando?
El proveedor a que se refiere el mensaje es Microsoft.Jet.OLEDB.4.0 (fijate en el comentario del 9/12/2012 también). Parece ser que no tenés alguna de esas versiones.

Anónimo,  20 junio, 2013 02:46  

Jorge, que buen aporte, con unas adecuaciones a mi proyecto funciona de maravilla.

Muchas Gracias.
ATTE. David

sebastian ramirez 28 junio, 2013 17:43  

Jorge no me resulta con excel 2010, alguna ayuda

Jorge Dunkelman 28 junio, 2013 19:25  

Tenés que reemplazar la orden

Provider=Microsoft.Jet.OLEDB.4.0

con

Provider=Microsoft.ACE.OLEDB.12.0

Anónimo,  07 noviembre, 2013 21:38  

Muy buen informacion pero me sale un error y dice esto "Error de sintaxis en la clausula From

Anónimo,  09 noviembre, 2013 05:15  

ERROR EN LA SNTAXIS DE LA CLAUSULA FROM.
ME MANDA A ESTE RENGLON:
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable

Jorge Dunkelman 09 noviembre, 2013 17:36  

¿Has creado la referencia a la ADO library, como se muestra en la primer parte de la nota? ¿Has modificado los parámetros para que apunten a la hoja y a las celdas adecuadas?

Anónimo,  27 febrero, 2014 01:23  

hola que tal te consulto. como se puede hacer para que me actualize los datos desde access y me elimine los existentes dado que me los va agregando y solo quiero que actualice los mismos.
Ej. Cliente 1000 compro 2300 el martes a las 9 de la mañana
a la tarde este cliente 1000 compro 2300 mas,cuando actualizo no me deja dado que la base de acces no se deja poner datos por duplicado

Jorge Dunkelman 09 marzo, 2014 16:59  

Disculpas por la demora. No me queda claro lo que querés hacer. En la base de datos hay un registro del cliente 1000 con 2300 en ventas. ¿Ahora hay que actualizarlo a 4600? Si es así, ¿no querías registrar las ventas por fecha/hora? Me parece extraño lo de los duplicados ya que si bien es el mismo cliente los datos son distintos (fecha/hora, contidad, importe).

DOUGLAS HERNANDO GRILLO URIBE 19 marzo, 2014 02:17  

Jorge gracias por el código, pero quisiera saber lo siguiente:
Tengo una tabla con un campo Primary Key, importo el excel a access mediante código VB6 y hasta ahí me funciona muy bien.
Lo que quiero saber es como hago para importar datos de dos columnas de excel a dos columnas específicas de la misma tabla access sin borrar los registros existentes en las demás columnas de la misma tabla.

Muchas gracias por tu respuesta
Atte, Douglas

Jorge Dunkelman 19 marzo, 2014 16:03  

Hola Douglas, ¿se trata de corregir registros existentes?

DOUGLAS HERNANDO GRILLO URIBE 19 marzo, 2014 16:58  

Jorge gracias por tu pronta respuesta. Esa tabla maneja 2 campos o columnas que mes a mes debo actualizar pues deben contener unos pagos y la fecha de pago, digamos que si podría ser corregis registros existentes porque los campos pago y fechapago los debo modificar mes a mes
Trabajo con Ado
Lo he tratado de hacer de la siguiente manera:
Set Cn = New ADODB.Connection 'Ajusta Cn como conexión ADO con BD
Set Rs = New ADODB.Recordset 'Ajusta Rs como recordset ADO con BD
Set ArchivoExcel = New Excel.Application

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & path + nombrebasedatos + ";"
Rs.ActiveConnection = Cn
Rs.CursorLocation = adUseClient
Rs.LockType = adLockOptimistic
Rs.Open "Select * From DatosPersonalesCartera"

For x = 2 To 253
With Rs
.Requery
.AddNew
!apto = ArchivoExcel.Cells(x, 2) 'este es Primary Key
!pago = ArchivoExcel.Cells(x, 16) 'estos son lo campos que debo importar a la tabla mes a mes
!fechapago = ArchivoExcel.Cells(x, 17) 'y están ubicados en la columna 2, 16 y 17 de la tabla

.Update
End With

La verdad es que tengo problemas porque cuando ejecuto la aplicación me sale error en la primary key porque se duplica el índice; y de otro lado no me importa los datos.

Gracias por tu ayuda.

Jorge Dunkelman 20 marzo, 2014 07:16  

Douglas, te sugiero que, si no lo has hecho, pongas tu consulta en alguno de los foros de Access. En estos días, lamentablemente, no dispongo de tiempo libre para ocuparme de consultas específicas.

Diego Retro 09 octubre, 2014 20:12  

buen día:

Presento un problema en la siguiente sentencia RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable , el error que muestra es "Argumentos incorrectos, fuera del intervalo permitido o en conflicto con otros

Jorge Dunkelman 10 octubre, 2014 17:54  

Tal como dice la información del error, tienes que revisar los argumentos, en particular la variable Tabla.

AnNeRoL 18 abril, 2016 12:45  

Buenas tardes,

Se para aquí:
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & _
dataSource & ";"

¿Hay alguna actualizacón posterior?

Jorge Dunkelman 18 abril, 2016 20:17  

Fijate en esta página

https://www.connectionstrings.com/excel/

Anónimo,  16 diciembre, 2016 23:25  

Buenas tardes,

observo que en ningún momento se establece la ruta del excel que se va a cargar a access, y sin embargo la hoja si.
en mi caso voy a hacer que VBA abra todos los excel contenidos en una carpeta y repita el proceso para cada uno de ellos, siendo todos iguales, y cargándolos a una tabla de access.

Mi primera duda es si al cargar un archivo tras otro no estará pisando las líneas de la tabla del access cargadas con el excel anterior.

La segunda duda es cómo establezco en que carpeta debe buscar el excel para ya encargarme yo de que busque todos los archivos de esa carpeta.

Un saludo
Muchas gracias previamente.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP