Exportar Datos de Excel a Access con VBA

ezealvarez12 · el 21 de febrero

Hola, tengo un problema. Necesito exportar a travez de una macro los datos de una tabla de excel a una tabla de access (ambas tablas tienen las mismas columnas). He buscado codigos para poder hacer el procedimiento pero como soy novato no soy capaz de adaptarlo a mis necesidades.

Lo que necesitaria es un codigo base que me sirva de estructura para exportar cierto rango de celdas de una hoja de excel a una tabla de access, donde yo tenga que cambia la ruta de conexión y el nombre de las tablas.

desde ya muchas gracias.

joshua · el 21 de febrero

Saludos.

Adapta la siguiente macro, utilizando Ado, debes tomar en cuenta lo siguiente:

En tu proyecto debes referenciar lo siguiente:

Microsoft Ext. ADO 2.5 for DDL Security (o posteriores).
Microsoft ActiveX Data Object 2.5 Library (o posteriores).[/HTML]

Dependiendo de la version de Access sera el tipo de motor:

[HTML]Access_1997:= Microsoft.Jet.OLEDB.3.51
Access_2000:= Microsoft.jet.oledb.4.0
Access_2007:= Microsoft.ACE.OLEDB.12.0[/HTML]

[HTML]Const dataSource As String = "provider=microsoft.jet.oledb.4.0;" _
& "data source=C:\Bases\Base_Datos.mdb"
Const tableName As String = "Records"
Sub AgregarDatos()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long
Set cn = New ADODB.Connection
cn.Open dataSource
Set rs = New ADODB.Recordset
rs.Open tableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
Sheets("Tu Hoja").Select
fila = 2 ' la primer fila en la hoja despues de los encabezamientos
Do While Len(Cells(fila, 2)) > 0
With rs
.AddNew
.Fields("ID_COL") = Range("A" & fila).Value
.Fields("SUCURSAL") = Range("B" & fila).Value
.Fields("FECHA_COL") = Range("C" & fila).Value
.Update
End With
fila = fila + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub [/HTML]

Espero te sea de utilidad.

Atte.

joshua

Antoni · el 22 de febrero

Hola:

Si no lo consigues con el código de joshua, que por cierto funciona de maravilla, sube el archivo Excel, el diseño y nombre de la tabla Access y el nombre de la base de datos Access, e intentaremos echarte una mano.

Saludos.

ezealvarez12 · el 23 de febrero

Gente muchas gracias, por el momento el primer código me sirvió.

tengo que ver si tengo que cambiar algún detalle de mi proyecto.

Desde ya muchas gracias. Descubri este foro recientemente buscando como poder solucionar este problema, y lo recorrí y la verdad que muy bueno!

Suerte!

ezealvarez12 · el 23 de febrero

Joshua una pregunta, primero te explico como es que hago uso de este codigo para exportar a access y despues te planteo el problema que me surge.

Yo hice un archivo en donde se cargan datos, el mismo es igual para varias personas, quienes cargan diferentes cosas pero con las mismas columnas, es decir, cambia el contenido de las celdas de información nomas. La idea es que cada persona cargue la información y la exporte (a través de la macro que utiliza ese código) a una base de datos access, la cual se encuentra guardada en una carpeta compartida en red. Todas estas personas tienen los permisos necesarios para acceder y modificar este archivo access. El problema me surgio cuando probando el archivo (en mi computadora funciona perfectamente) desde la computadora de una de esas personas, cuando va a exportar los datos le salta un error que dice: "no se encuentra el proyecto o la biblioteca". Al principio creí que como esta persona tiene una versión office 2003 y yo tengo una 2010 cambiando el tipo de motor en el código del archivo de su computadora iba a resolverlo. Pero la realidad fué que me dió el mismo error y no se a qué se refiere la verdad. Tal vez vos sepa a qué se refiere y puedas explicarmelo mejor.

Desde ya muchas gracias, con ese código que me pasaste pude aprender a cómo exportar desde excel a access.

Saludos!

joshua · el 24 de febrero

Saludos.

Segun te endiendo solo la base de datos esta compartida, màs no los libros, en cada libro se debe hacer lo siguiente:

En cada libro se debe referenciar lo siguiente en el proyecto de vba:

Microsoft Ext. ADO 2.5 for DDL Security (o posteriores).
Microsoft ActiveX Data Object 2.5 Library (o posteriores).[/HTML]

Prueba y comentas.

Atte.

joshua

ezealvarez12 · el 28 de febrero

Saludos.
Segun te endiendo solo la base de datos esta compartida, màs no los libros, en cada libro se debe hacer lo siguiente:

En cada libro se debe referenciar lo siguiente en el proyecto de vba:
Microsoft Ext. ADO 2.5 for DDL Security (o posteriores).
Microsoft ActiveX Data Object 2.5 Library (o posteriores).[/HTML]
Prueba y comentas.

Atte.

joshua

Hola, gracias por responder. Por el momento no lo puedo probar porque hubo un problema de red así que no sé si ese era el problema. Muchas gracias igual

Con respecto al código que me pasaste, tengo una duda, cada vez que exporto los datos desde excel me los agrega a la tabla en access, con lo cual si voy agregando datos nuevos y pongo para exportar, los datos que ya se exportaron anteriormente se vuelven a exportar y en la base de access están duplicados o más veces, dependiendo de la cantidad de veces que hayan sido exportados. Hay alguna manera de que cada vez que pongo para exportar desde excel lo que haga sea sustituir los datos de la base de access?? Supongo que eso podría hacerse si antes de que exporte los datos pongo que el procedimiento me borre los datos de la base en access pero no se bien como hacerlo.

Si llegas a saber como podría hacer te agradecería que me lo comentes.

Desde ya muchas gracias, saludos.

Antoni · el 29 de febrero

Hola:

Coloca esta sentencia inmediatamente después de la conexión con la BD.

cn.Execute "Delete from NombreTabla", , adCmdText

Con esto conseguirás borrar la tabla antes de exportar la información.

Saludos.

ezealvarez12 · el 29 de febrero

Hola, primero muchas gracias por responder.

Segundo, probe poniendo esa sentencia, al primer intento (o sea cuando exporto los nuevos datos por primera vez) me funciona, borra la tabla anterior y carga la nueva. Quise hacer una prueba mas exhaustiva y agregue nuevos datos y volvi a exportar, en este intento no se porque pero no me agrega los nuevos datos (con lo cual entiendo que no me borra la tabla anterior y me carga la nueva).

te paso el codigo por las dudas.

Const dataSource As String = "provider=Microsoft.ACE.OLEDB.12.0;" _

& "data source=\\Terarsvcfs01\sarareas\DINE\Visitas a Clientes\baseclientesPrueba.mdb"

Const tablaName As String = "Info_Visitas"

Sub ExportarDatosAccess()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long

Set cn = New ADODB.Connection

cn.Open dataSource

'cn.Execute "Delete from Info_Visitas", , adCmdText

Set rs = New ADODB.Recordset

cn.Execute "Delete from Info_Visitas", , adCmdText

rs.Open tablaName, cn, adOpenKeyset, adLockOptimistic, adCmdTable

Sheets("Info Visita").Select

fila = 5 ' la primer fila en la hoja despues de los encabezamientos

Do While Len(Cells(fila, 1)) > 0

With rs

.AddNew

.Fields("código") = Range("A" & fila).Value

.Fields("V/R") = Range("B" & fila).Value

.Fields("Fecha") = Range("C" & fila).Value

.Fields("MES") = Range("D" & fila).Value

.Fields("N°") = Range("E" & fila).Value

.Fields("Nombre") = Range("F" & fila).Value

.Fields("Gerente") = Range("G" & fila).Value

.Fields("Jefe Ventas") = Range("H" & fila).Value

.Fields("Insp Comercial") = Range("I" & fila).Value

.Fields("Otros") = Range("J" & fila).Value

.Fields("Participante Cliente") = Range("K" & fila).Value

.Fields("Tema 1") = Range("L" & fila).Value

.Fields("Tema 2") = Range("M" & fila).Value

.Fields("Tema 3") = Range("N" & fila).Value

.Fields("Tema 4") = Range("O" & fila).Value

.Fields("Tema 5") = Range("P" & fila).Value

.Fields("Pendientes") = Range("Q" & fila).Value

.Update

End With

fila = fila + 1

Loop

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

Muchas gracias por tu ayuda.

Antoni · el 29 de febrero

Hola:

Así debería funcionar:

[COLOR=#333333]

... ... ...

... ... ... 


Sub ExportarDatosAccess()

[/COLOR]

[COLOR=#333333]Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long[/COLOR]

[COLOR=#333333]Set cn = New ADODB.Connection[/COLOR]

[COLOR=#333333]cn.Open dataSource[/COLOR]

[COLOR=#333333]cn.Execute "Delete from " & [/COLOR][COLOR=#333333]tablaName[/COLOR][COLOR=#333333], , adCmdText[/COLOR]

[COLOR=#333333]Set rs = New ADODB.Recordset[/COLOR]

[COLOR=#333333]rs.Open tablaName, cn, adOpenKeyset, adLockOptimistic, adCmdTable[/COLOR]


... ... ... 

... ... ...[/CODE]

Si no funciona, sube la base de datos sin registros, y un archivo Excel para probar.

Saludos.

ezealvarez12 · el 29 de febrero

Ya probe y funciona perfecto!! gracias.

Ahora me queda por ver cual es la razon del problema que tengo cuando ejecuto el excel desde otra computadora que me aparece un arror que dice que no se encuentra el proyecto o la consulta. Ya probe con lo que me dijo más arriba joshua pero se dio el caso que en referencias no aparece Microsoft ActiveX Data Object 2.5 Library (o posteriores), y no se si será eso o que.

Gracias.

joshua · el 1 de marzo

Saludos.

Pues ese es el problema, recuerda que estas referenciando una libreria si no cuentas con ella simplemente no funcionara la macro.

Atte.

joshua

Un saludo Maestro Macro Antonio

ezealvarez12 · el 1 de marzo

entonces si cuando armo la macro yo referencio el activex data object con version anterior (que se que lo tienen los excel de las otras personas) podria funcionar en las otras maquinas?

yo porbe si sacando la referencia esa que dice DDL funciona y si lo hace.

saludos.