Hojas de cálculo en Excel - página principal

Leer las tablas de una base de datos Access

Andrés, un lector del blog, me preguntó hace unos días, si era posible leer las tablas de una base de datos Access desde excel, y como en la anterior ocasión, cuando explicábamos como compactar una base de datos Acess, desde excel, he tenido que hacer una sencilla adaptación de una rutina que servía para leer las tablas de una base de datos desde una página asp, y que fuese utilizable desde Excel.

La rutina que os presento, nos muestra un formulario (UserForm), y un combobox en su interior, que nos presentará las tablas de la base de datos que hayamos elegido. Una vez seleccionada la tabla que deseemos, pasaremos su nombre a una variable, y ya podremos hacer de todo con esa tabla: consultar registros, borrar registros, etc. Evidentemente, podremos mostrar todos los datos de la tabla seleccionada, tal y como explicábamos en el artículo sobre como leer una base de datos Access.

En el siguiente ejemplo, vamos a leer las tablas de una base de datos Access existente en una unidad F (en mi caso, la unidad F, corresponde a una llave USB), y dentro de una carpeta llamada hojas-de-calculo-en-excel (el nombre de este blog).

Lo primero que tendremos que hacer, será crear el UserForm. A continuación, le añadiremos un Combobox (dejaremos el nombre por defecto: ComboBox1), y le incluiremos este código:


Private Sub ComboBox1_Enter()
'Esto se producirá cuando
'cliqueemos en el combobox
'Si hay errores, que continúe

On Error Resume Next
'quitaremos todo lo que haya en el combobox1
ComboBox1.Clear
'Vamos a llenar dinámicamente el combobox
'con los nombres de las tablas.
'Primero definimos una constante

Const rsSchemaTablas = 20
'definimos la ruta de la base de datos
ruta = "F:\hojas-de-calculo-en-excel\base-de-datos.mdb"
'creamos la conexión
Set oConn = CreateObject("ADODB.Connection")
'abrimos la base de datos
oConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ruta)
'creamos el filtro
Filtro = Array(Empty, Empty, Empty, "TABLE")
'leemos las tablas de la base de datos
Set rsSchema = oConn.OpenSchema(rsSchemaTablas, Filtro)
'hasta que no llegue al final...
Do While Not rsSchema.EOF
'Añadimos el nombre de la tabla al combobox
ComboBox1.AddItem rsSchema("TABLE_NAME")
'nos movemos al siguiente registro
'que nos devuelve el nombre de la
'siguiente tabla

rsSchema.MoveNext
Loop
'cerramos y limpiamos los objetos
oConn.Close
rsSchema.Close
Set oConn = Nothing
Set rsSchema = Nothing
End Sub

Si deseáis compactar una base de datos, que esté en la misma carpeta donde tengamos el fichero de excel, entonces sustituiremos la variable ruta del código anterior, para que nos quede definida de la siguiente forma:

ruta = ActiveWorkbook.Path & "\"

También añadiremos al UserForm, un botón que presente el texto "Aceptar" (un CommandButton), y que al pulsarlo, nos mostrará un mensaje (un MsgBox) que nos indicará el nombre de la tabla elegida, de tal forma que podremos hacer una consulta a esa tabla específica. El código del botón aceptar (el nombre del botón será el que nos presenta excel por defecto: CommandButton1), será este:

Private Sub CommandButton1_Click()
'pasamos el nombre de la tabla
'elegida, a una variable

tabla_elegida = ComboBox1.List(ComboBox1.ListIndex)
'mostramos un mensaje
MsgBox ("Has elegido la tabla: " & tabla_elegida)
End Sub

Si lo aplicáis a un ejemplo concreto, recordad cambiar en el código fuente de la hoja de cálculo, la ruta donde tendréis la base de datos, para que os funcione correctamente, informando manualmente de la carpeta donde se encuentra ésta, o bien, si la colocáis en la misma carpeta que el fichero de excel, poniendo como ruta, el path del propio fichero de excel, tal y como se menciona en este artículo.



14 comentarios:

-=(ANDRES)=- dijo...

Javier, he estado esperando con ansias este articulo, muchas gracias por publicarlo. Funciona perfectamente y me es muy útil para automatizar mis procesos.
Tengo una pequeña consulta ¿Es posible hacer esto sin un userform?, es decir solo con el comboBox directamente insertado en la hoja de cálculo?.
Saludos y Gracias nuevamente

Javier Marco dijo...

En este artículo tienes un ejemplo de como poner un combobox fuera de un userform: llenar dinámicamente un combobox

-=(ANDRES)=- dijo...

Javier, necesito hacerte la siguiente consulta: ¿Es posible desde Excell con VB vincular una tabla a una base de Access de otra base también de Access?, tal vez me estoy escapando un poco del tema de Excell pero en preguntar no hay engaño.

Saludos y felicitaciones por tu Blog

Javier Marco dijo...

La verdad es que quieres hacerlo todo desde excel, y es probable que se pueda hacer incluso eso, pero creo que meter más cosas de access aquí, ya sería rizar el rizo, y estaría fuera del alcance del blog.

Seguramente en alguna de las webs y foros especializados en access, te puedan echar un cable con el código VBA para vincular tablas. Quizás con ese código, y con el de como conectarse a una BD access desde excel (explicado en este blog), puedas conseguirlo.

Pero ten en cuenta que yo no sé hacerlo todo :-)

-=(ANDRES)=- dijo...

Tal vez tienes razón, a lo mejor me obsesione con Excel (incluso llegue hacer un juego de pelea y estoy en la mitad de la segunda versión :) ) de todas formas la ayuda que he encontrado acá me ha sido bastante útil.

Gracias nuevamente y Saludos.

-=(ANDRES)=- dijo...

Javier. ¿existe alguna forma de que esto funcione con tablas vinculadas? ya que la consulta no me lista las Tablas Vinculadas.

Gracias y Saludos.

Javier Marco dijo...

Pues acabo de establecer relaciones entra las tablas del ejemplo que he dejado para descargar, y funciona correctamente, pues devuelve los nombres de las tres tablas, con independencia de si están relacionadas o no.

Me extraña que te suceda eso...

-=(ANDRES)=- dijo...

Que extraño, a mi solo me lista las tablas del archivo y no las Vinculadas, ¿será debido a que no tengo el comboBox en un en un UserForm?...

Anónimo dijo...

Buenos días Javier:

Lo primero de todo, darte las gracias de antemano, por por lo menos a mí abrirme los ojos a nuevas opciones, con la Excel.

Ya se porque lo comentas que no das abasto, pero tengo una consulta personal:

Trabajo en una agencia de transportes, y me gustaría poder ofrecer a los clientes la posibilidad de que a través de una excel puedan calcular su coste de los envios. Las variables que tiene una oferta de transportes son las siguientes (agrego un ejemplo de una oferta):
Asturias Leon Madrid
5 kg. 3 € 5 € 7 €
10 kg. 6 € 10 € 14 €

Lo que me gustaría es que pudiesen introducir una provincia y el peso de su envío y que se calculase directamente el importe de su envío.

Por último a este importe habría que añadir según la negociación con el cliente el 8% sobre el valor del porte en concepto de seguro.

Como te digo ya se que pido mucho, pero he intentado buscar y buscar ayuda y no encuentro nada.

Muchas gracias, un saludo.

Iván Pay dijo...

Buenos días de nuevo Javier:

Para poder contactar conmigo te dejo mis direcciones de correo electrónico:

- painho1976@hotmail.com
- comercial.asturias@redur.es

Muchas gracias un saludo.

Anónimo dijo...

Excelente blog, me ha servido mucho gracias

Carlos Mario Botero
Desde Colombia

Andres dijo...

como hago para meter una hoja de excel en mi blog

Anónimo dijo...

Estoy fascinada con esta página

Javier Marco dijo...

Gracias por tu comentario :-)