viernes, julio 17, 2009

Transferir datos de Excel a Access - tercera nota

En las notas anteriores vimos cómo transferir datos de Excel a Access manualmente y programáticamente.

La necesidad de usar Access surge como una de las soluciones posibles cuando queremos, por ejemplo, consolidar datos de varias hojas en una única base de datos para generar a partir de ella una tabla dinámica.
Si el total de filas a consolidar supera el límite de una hoja de Excel (hasta la versión 2003 incluida, 65536 filas) no podemos almacenar los datos consolidados en una única hoja.
Si trabajamos con Excel 2007 podemos almacenar más de un millón de filas en una hoja, lo cual nos exime de tener que usar herramientas externas a Excel. Pero como más del 50% de los usuarios usa versiones anteriores a Excel 2007, almacenar datos en Access es una buena solución.

En las soluciones que muestro en las notas mencionadas suponemos que Access está instalado en la máquina del usuario. Por supuesto, éste no es siempre el caso y en esta nota mostraremos como usar Access como recipiente para nuestros datos aún cuando no esté instalado en nuestra máquina.

Empecemos por aclarar que los archivos con formato .mdb no requieren la presencia de Access para poder ser utilizados. Estos archivos están asociados con el Microsoft Jet Database Engine, que está incluido en el paquete de Office. Si bien este componente ha sido abandonado por Microsoft, sigue siendo vigente por motivos de compatibilidad.

Nuestra meta es crear dos rutinas, una para crear el archivo “.mdb” y otra para almacenar los datos de la hoja u hojas Excel en la tabla.

Nuestro primer paso es crear una referencia a

* Microsoft Ext. ADO 2.5 for DDL Security (o posteriores).
* Microsoft ActiveX Data Object 2.5 Library (o posteriores).
en el editor de VB, en el menú Tools—References



datos de Excel a Access

Un detalle a tomar en cuenta es que las macros que mostraré más adelante están construidas para el ejemplo específico de esta nota y hay que editarlas para adaptarlas a otros usos.
En nuestro ejemplo tenemos un cuaderno Excel con tres hojas. Cada hoja contiene datos de tres distintos años (2005, 2006 y 2007) que queremos consolidar en una única hoja para construir a partir de ella una tabla dinámica.
La rutina para crear el archivo “.mdb” con una tabla para almacenar los datos es la siguiente:

Option Explicit

Const dataSource As String = "provider=microsoft.jet.oledb.4.0;" _
& "data source=d:\BaseDeDatos.mdb"
Const tableName As String = "datos_export"

Sub crearDB()
Dim catalog As ADOX.catalog
Dim new_table As ADOX.Table


'crear la base de datos

Set catalog = CreateObject("adox.catalog")
catalog.Create dataSource

' crear la tabla para contener los datos

Set new_table = CreateObject("adox.table")
new_table.Name = tableName
new_table.Columns.Append "mes", adDate
new_table.Columns.Append "pais"
new_table.Columns.Append "suma", adDouble
catalog.Tables.Append new_table

End Sub

En la parte superior del módulo, antes de la rutina “crearDB” hemos definido dos constantes. Lo hacemos de esta manera ya que también en la segunda rutina haremos uso de estas constantes.
Esta macro crea una base de datos Access (BaseDeDatos.mdb) con una tabla (datos_export)

datos de Excel a Access

Ahora tenemos que exportar los datos de la hoja activa al la tabla “datos_export”, lo que hacemos con esta macro:

Sub AgregarDatos()
' exporta los datos de la hoja activa a una tabla de Access
' este procedimiento es especifico para el ejemplo.
' Para otros usos hay que adaptarlo

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
' all records in a table
fila = 2 ' la primer fila en la hoja despues de los encabezamientos

Do While Len(Cells(fila, 1)) > 0
With rs
.AddNew
.Fields("mes") = Range("A" & fila).Value
.Fields("pais") = Range("B" & fila).Value
.Fields("suma") = Range("C" & fila).Value
.Update
End With
fila = fila + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


Esta rutina es la misma que hemos usado en la segunda nota de la serie, adaptada a nuestro ejemplo.


Ahora activamos la hoja cuyos datos queremos exportar y corremos la macro para cada una de las hojas que queremos consolidar.


A esta altura de los acontecimientos tenemos un archivo Access con un tabla que contiene los datos de las hojas (podemos ver que el tamaño del archivo se ha incrementado)

datos de Excel a Access

Ahora podemos crear la tabla dinámica usando la opción Fuente de datos Externa

datos de Excel a Access

En el segundo paso creamos una nueva fuente de datos, que en nuestro ejemplo llamaremos “consolidarMdb” usando el driver de Access

datos de Excel a Access


datos de Excel a Access


datos de Excel a Access


datos de Excel a Access

Después de apretar Connect, apretamos OK con lo cual hemos creado la nueva conexión. De aquí en adelante seguimos los pasos del asistente tal como hemos mostrado en esta nota.


El resultado:

datos de Excel a Access




Technorati Tags:

8 comentarios:

  1. Jorge antes que nada te felicito por tu blog, es muy bueno, y de igual manera aprovecho el comentario para hacerte una pregunta no referente al tema, pero es una duda que creo me pudieras responder, mi pregunta es: ¿como hacer para poder escribir una cifra de numero con mas de 20 digitos sin que me aparezca la referenica de (E+46) y me pueda aparecer la cifra completa?, de antemano muchas gracias y reitero mis felicitaciones hacia tu blog.

    Saludos.

    ResponderBorrar
  2. Ivan
    podés convertir el número a texto anteponiendo un ' o dándole a la celda formato de texto antes de introducir el número o usando la función TEXTO. En todo caso, el número se convierte en texto así que no lo podés usar para hacer cálculos.
    Tené en cuenta que el límite de precisión en Excel es de 15 cifras significativas. Si tenés que hacer cálculos con números con más de 15 cifras me parece que la única posibilidad es usar Vba.

    ResponderBorrar
  3. Hola Jorge, buenísimo el blog!!

    Te escribo pq necesito adaptar esta macro de la siguiente forma:

    En vez de generar una sola tabla, que ahora genere varias tablas que tomen el nombre de cada hoja del libro (todas tienen el mismo formato) y que llene secuencialmente cada tabla de access con los valores respectivos de cada hoja. (Esta bien si los campos se mantienen igual "mes", "pais", "suma")

    Podrias ayudarme??
    Muchas Gracias

    Matías

    ResponderBorrar
  4. Matías
    podés adaptar las macros de manera de correr una macro específica para cada hoja. Si necesitás más ayuda ponete en contacto conmigo por mail.

    ResponderBorrar
  5. Nicolas Escudero02 noviembre, 2011 17:38

    Hola Jorge, te felicito por el articulo, muy bueno!

    Tengo un inconveniente con algo similar a lo que explicas aqui: estoy trabajando con una macro desde el "Libro1.xlsm" que utiliza DAO para extraer valores de otro "Libro2.xls" (Lo hago con DAO ya que son miles de datos y además necesito que el Libro2 no se abra al leerlo).

    Anda tal y como yo quiero, pero tengo el problema de que yo tuve que poner un referencia "Microsoft DAO 3.51
    (or 3.6) Object Library" en mi Editor VBA, pero cuando otro usuario de la red utiliza este "Libro1.xlsm" la macro se detiene ya que no reconoce el "Dim db As DAO.Database" ya que evidentemente ellos no tienen hecha la referencia.

    Como puedo solucionar esto? ya sea forzando la instalación de la referencia (en lugar de que excel solo avise que la misma no esta) o bien declarando algo en el código VBA que la instale.

    Desde ya, muchisimoas gracias!

    ResponderBorrar
  6. Nicolás,

    no sé si se puede hacer programáticamente (supongo que se puede), pero si guardas tu código en un libro las referencias que hayas creado se conservan. También podrías distribuir el código como complemento (Add In) con las referencias guardadas.

    ResponderBorrar
  7. Te hago una consulta, estoy armando una pequeña aplicación en excel que utiliza un .mdb como recipiente. Mi problema es el siguiente: el libro de excel va a utilizarse en una pc con office 2003 mientras que yo estoy armando las macros en office 2010 x64 y estoy en un quebradero de cabeza porque jet 4.0 no es compatible con office x64 y necesito una cadena de conexión que sirva para ambos casos y que no necesite instalar ningún tipo de drivers en la pc que tiene el office 2003. Hay alguna solución posible??

    ResponderBorrar
  8. Andrés, supongo que si pero te sugiero que hagas tu consulta en un foro de Access.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.