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

Leer una base de datos Access

Hoy nos toca hablar de algo muy interesante y útil. Vamos a analizar como podemos leer una base de datos access, importando los datos de una de sus tablas.

Para poder leer una base de datos, necesitaremos seleccionar desde VBA, dentro del menú Herramientas, la opción Referencias, poniéndole una muesca a Microsoft ActiveX Data Objects 2.8 Library, tal y como podéis comprobar en la siguiente imagen (esta opción la encontraréis bajando hasta la letra M de Microsoft, pues las referencias están ordenadas alfabéticamente. En cuando le pongáis una muesca, os aparecerá arriba del todo como en la imagen):


Ahora tan solo nos quedará copiar el macro que veréis a continuación, y donde informaremos de lo siguiente:

  • La ruta donde se encuentra la base de datos (en el macro hemos considerado que la base de datos está en la misma carpeta donde tenemos el fichero de excel con el macro y desde el que queremos leer la base de datos).

  • El nombre de la base de datos.

  • El nombre de la tabla de la base de datos que queremos leer.

  • La celda inicial donde vamos a empezar a escribir los datos.


Sub conectar_con_la_base_de_datos()
'**************************************************
' Para que esto funcione, debemos seleccionar
' en el menú Herramientas (desde VBA), la opción
' Referencias, y le pondremos una muesca a
' "Microsoft ActiveX Data Objects 2.8 Library"
'**************************************************
'Indicamos la ruta de la base de datos:
'Supondremos que la base de datos
'está en la misma ruta donde tenemos
'guardado este fichero de excel
'si la ruta fuera otra, la pondremos
'entre comillas, por ejemplo así, con
'cuidado de no poner la antibarra del final:
'ruta = "C:\Mis documentos".
'Indicaremos también el nombre de la
'base de datos, la tabla, y la celda
'inicial donde empezaremos a escribir

ruta = ThisWorkbook.Path
base_de_datos = "frases-celebres.mdb"
tabla = "frases"
celda_inicial = "A1"
'**************************************************
'Ocultamos el procedimiento

Application.ScreenUpdating = False
'Si hay errores, que siga
'procesando esta página

On Error Resume Next
'Creamos el objeto conexión
Set Conn = New ADODB.Connection
'Creamos el objeto recordset
Set rs = New ADODB.Recordset
'Nos conectamos a la base de datos
Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ruta & "\" & base_de_datos)
'Montamos la sentencia SQL para
'mostrar todos los datos de la tabla

Sql = "Select * from " & tabla
'Abrimos la base de datos
rs.Open Sql, Conn, adOpenStatic, adLockOptimistic
'contamos los registros totales
registros_totales = rs.RecordCount
'Si ha errores, mostramos un mensaje
'y finalizamos el macro

If Err > 0 Then
'mostramos un mensaje
MsgBox (Chr(13) + " Lo sentimos, se han producido errores, " + _
Chr(13) + " y no se ha podido completar la operación. " + _
Chr(13) + Chr(13)), vbOKOnly, " Importación incorrecta"
'finalizamos el macro
Exit Sub
End If
'Recorremos todos los registros
'empezando a escribir en la celda A1

Range(celda_inicial).Select
'contamos los campos que tenemos
'en la base de datos

numero_de_campos = rs.Fields.Count
'comenzamos con el bucle, a
'recorrer los encabezados

For i = 0 To numero_de_campos - 1
'Ponemos en negrita el encabezado
Selection.Font.Bold = True
'ponemos el nombre del encabezado
'y además, en mayúsculas

ActiveCell = UCase(rs.Fields(i).Name)
'pasamos a la siguiente columna
ActiveCell.Offset(0, 1).Select
Next
'miraremos el máximo de filas a importar
If Rows.Count = 65536 Then
'si tenemos una versión anterior a
'excel 2007, importaremos el máximo
'que nos permite excel: 65536 filas
'de filas, pero restaremos las filas
'necesarias, dependiendo de donde
'empezemos a escribir en la celda_inicial

total_filas = 65536
maximo = total_filas - Range(celda_inicial).Row
Else
'en caso contrario, importaremos
'un máximo de un millón de filas

total_filas = 1000000
maximo = total_filas - Range(celda_inicial).Row
End If
'seleccionamos la fila siguiente
'para escribir los datos de la tabla,
'mientras haya datos, y mientras estos
'no ocupen más que las filas disponibles
'que nos quedan en la hoja de cálculo

Range(celda_inicial).Offset(1, 0).Select
'creamos un contador para contar
'los registros que llevamos

contador = 1
Do While Not rs.EOF And contador <= maximo
'comenzamos a escribir los datos
For i = 0 To numero_de_campos - 1
ActiveCell = rs.Fields(i)
'pasamos a la siguiente columna
ActiveCell.Offset(0, 1).Select
Next
'volvemos a la columna inicial
'donde estábamos al principio

ActiveCell.Offset(0, -numero_de_campos).Select
'pasamos a la siguiente fila, siempre
'y cuando no estemos ya en la última
'fila que nos permite la hoja de cálculo

If ActiveCell.Row < total_filas Then ActiveCell.Offset(1, 0).Select
'nos desplazamos al siguiente
'registro de la base de datos

rs.MoveNext
'sumamos 1 al contador
contador = contador + 1
Loop
'cerramos la conexión
Conn.Close
'limpiamos los objetos
Set Conn = Nothing
Set rs = Nothing
'Mostramos el procedimiento
Application.ScreenUpdating = True
'mostraremos un mensaje dependiendo de
'si se han importado todos los registros
'de la base de datos, o solo una parte

If registros_totales = contador - 1 Then
MsgBox (Chr(13) + " Se importaron correctamente todos los registros " + _
Chr(13) + " de la base de datos """ & base_de_datos & """, y de la tabla " + _
Chr(13) + " llamada """ & tabla & """. " + _
Chr(13) + Chr(13) + " Se han importado los " & contador - 1 & " registros de la tabla. " + _
Chr(13) + Chr(13)), vbOKOnly, " Importación incompleta"
Else
MsgBox (Chr(13) + " Se importaron correctamente solo algunos registros " + _
Chr(13) + " de la base de datos """ & base_de_datos & """, y de la tabla " + _
Chr(13) + " llamada """ & tabla & """. " + _
Chr(13) + Chr(13) + " En concreto solo se importaron " & contador - 1 & " registros, de " + _
Chr(13) + " los " & registros_totales & " registros disponibles. " + _
Chr(13) + Chr(13)), vbOKOnly, " Importación completa"
End If
End Sub

Con esto ya tendremos resueltas nuestras necesidades, para leer correctamente una tabla de una base de datos.

Desde aquí podéis descargar el fichero de excel, con todo el código que os presento en este artículo, más la base de datos que he utilizado como ejemplo, que contiene una serie de frases célebres, para que así podáis importarla desde excel, y ver como funciona todo.



35 comentarios:

Anónimo dijo...

Hola te felicito por tu blog, pero tengo una consulta ¿como hago cuando tengo una consulta con pase de parametros? por ejemplo de este tipo:

SQL = "SELECT fecha, hora FROM miTabla" _
& "WHERE fecha < parametro1 and fecha > parametro2 "

donde los parametros deben ser ingresados via teclado?
Espero que me puedas responder

El pilt®afilla - www.3piesalgato.com dijo...

Hola que tal. Muchas gracias por tu comentario.

Lo que planteas es bastante sencillo. Imagina que quieres buscar solo las frases célebres de Groucho Marx, de ese ejemplo que hay colgado.

Tan solo tienes que crearte una variable que se llame por ejemplo "autor_cita", y colocarla al principio del macro, para que pregunte al usuario en nombre del autor cuya cita desea localizar:

autor_cita = InputBox("Introduce el nombre de un autor", "Autor")

Luego donde está la sentencia SQL, tendrás que cambiarla, poniendo esto:

Sql = "Select * from " & tabla & " WHERE autor LIKE '%" & autor_cita & "%'"

De esa forma, si introduces en el inputbox el nombre de Groucho (sin el apellido) te buscará las citas de todos los autores que contengan la palabra Groucho en su nombre.

Solo hay que tener cuidado con el tema de las comillas simples y dobles en la sentencia SQL. Si trabajas con fechas, imagino que deberás utilizar el símbolo de la almohadilla #, pero ese es ya más un tema de access, así que si tienes dudas sobre SQL, access y demás, tendrás que googlear un poquillo.

Saludos. Espero que te sirva.

Diego Fernando Calderon dijo...

Hola Javier:

Primero dejame felicitarte por el trabajo tan fantastico que vienes realizando en tu Blog..Me ha servido de mucha ayuda..mil y mil gracias

Ahora la verdad es que necesito una ayuda bastante grande, para ver si puedo realizar un trabajo que me encomendaron.

Resulta que tengo en papel una forma (tipo factura) PREIMPRESA y me pidieron que si podia realizar algo en excel para que se digitaran unos datos para que al imprimirlos salgan exactamente donde deben ir en la forma preimpresa.
Se me ocurrio escanear la imagen y ponerla de fondo en la hoja de excel, pero me di cuenta que me pone la imagen en todo el fondo de la hoja...A lo cual alguien me dijo que depronto por medio de una macro se podia lograr poner en un rango de Celdas definidas y NO en toda la hoja como lo hace por defecto.

Sera que me puedes ayudar con una macro para ese tipo de requerimiento..?? o me podrias dar una idea de como realizar ese trabajo de una manera mas rapida o fiable??

Gracias por tu ayuda y Dios te bendiga

att:

Diego Calderon
dfcalderon@gmail.com

El pilt®afilla - www.3piesalgato.com dijo...

Gracias por tu comentario.

Lo que puedes hacer es crearte uno o varios formularios (Userforms), o bien inputbox donde le preguntes al usuario los datos de la factura (importe, concepto, fecha de la factura, etc. para luego imprimir esos datos. Personalmente no veo que aporte nada especial el uso de macros a lo que aportaría rellenar los datos de la factura en pantalla, pero en cualquier caso, para que te quede bien impresa la factura, una vez hayas rellenado los datos, y no te descuadre nada, vas a tener que utilizar el método de prueba y error, es decir, vas a tener que hacer pruebas, e imprimir como te queda, para ir ajustando el ancho y alto de las celdas, en función de tus necesidades.

Y recuerda que si esa factura la imprimes en otrs impresosa distinta de la habitual, lo más probable es que te salga todo descuadrado, con lo que tendrías que volver a recolocar todo en su sitio, mediante el método de prueba y error.

Saludos.

Anónimo dijo...

Hola ke tal, queria saber si se puede grabar desde excel a una base de datos.

El pilt®afilla - www.3piesalgato.com dijo...

Puas la verdad es que no lo he intentado, pero no puede distar mucho de la opción contraria, que es la que se explica en este artículo. Siento no poder ayudarte, porque no estoy muy puesto en access.

En cualquier caso, te recomiendo que te pasese por aquí, que son especialistas en access: www.mvp-access.com

Salu2

cer dijo...

Disculpa y para cargar unas tablas de access en un combobox en un user form....
Espero y me puedas ayudar
Saludos desde torreon Coahuila Mexico

Anónimo dijo...

Saludos tengo el siguiente problema amigo..

Tengo un documento bien largo pero tiene muchas variables, quisiera saber lo siguiente como hago para que cada palabra me la sume, es decir, imaginemos que tenemos reptidamente en diferente celdas localisadas la palabra "coche" si las cuento uno por uno son en total 30, pero quiero saber si el excel tiene una funcion que me indique el numero de palabras.

Espero tu respuesta lo mas rapido posible gracias.

El pilt®afilla - www.3piesalgato.com dijo...

Para Darzamat: prueba a ver que sacas, utilizando el código de este artículo, y leyendo lo que pone este sobre el combobox. No olvides leer los comentarios de ese otro artículo, porque respondí una duda de un usuario, que me parecía interesante.

Para anónimo: Pues no conozco ninguna función que haga eso. Prueba a adaptar esta otra función personalizada que cree, o bien prueba a utilizar este buscador.

Unknown dijo...

Claro amigos todo lo solicitado se puede hacer, yo he hecho menus con botones en excel y todos los datos se los grabo en acces, por lo cantidad de registro prefiero hacerlo así, e igualmente me puedo traer datos desde acces y cargarlos a listas, combox en excel.. con gusto les puedo colaborar...
juankarlos05@gmail.com

Anónimo dijo...

Hola, me parece muy útil lo que haces aqui.

Queria saber como puedo hacer lo mismo pero conectandome a una base de datos de Visual FoxPro 7

Le agradeceria su ayuda.
gracias.

El pilt®afilla - www.3piesalgato.com dijo...

tan solo tienes que cambiar la conexión con la base de datos, para que en lugar de ser una base de datos access, sea la FoxPro. En este enlace, puedes ver las diferentes cadenas de conexión con las bases de datos más habituales:

www.connectionstrings.com

Saludos

DAVIX dijo...

Pues mira tengo un problema ya que de excel necesito meter unos datos, para que con ellos de una base de datos me tome otros valores, y pueda meter otras formulas, es decir ejemplo.
Tengo temperatura y preesion y necesito leer un valor a esas condiciones y que me lo ponga en excel automaticamente con esos 2 valores, no se si pudieras ayudarme te lo agradeceria mucho.

David

DAVIX dijo...

No se si talvez con un macro que meta datos, y al ller los datos 2 pues me tome otro de la base de datos, y posteriormente lo puedo calcular, gracias

Es un excelente blog

Unknown dijo...

Javier Felicidades por tu Blog, es de mucha ayuda para quienes estamos comenzando en esto de VB. Tengo una duda respecto a la lectura de la tabla de Access, resulta que tengo una tabla que tiene un nombre con separación de espacios, ej "Tabla principal", resulta que al estar así, la macro no me funciona. ¿Existe una solución para esto que no involucre el cambio de nombre de la tabla?
De ante mano muchas gracias.
Saludos.

Andrés Silva
1977.andres@gmail.com

Unknown dijo...

Javier, no te preocupes, ya resolví mi problema, debo poner el nombre de la tabla entre corchetes cuadrados, osea
"Tabla Principal", la debo poner como "[Tabla Principal]"
Gracias de todas formas, y sigue así.

Saludos.

Atte.
Andrés Silva C.
1977.andres@gmail.com

Anónimo dijo...

Bueno antes que nada, muchisimas grasias por este gran trabajo.

Tengo una Gran duda, quisiera saber si es posible hacer una aplicacion en visual, que me genere un archivo de exel, con datos de una base de datos de acces, variables, etc.

un ejemplo, yo tengo un formato que tengo que precentar (hoja de exel con bordes, titulo, logo, etc....) y quiero que eso se copie el numero de veces (anexos necesarios, pueden empezar de uno hasta como 350) y despues de que lo copie lo rellene con los datos de mi base de datos y mis variables.

no se si me explique bien, anexo mi msn por si me quieres agregar y de paso me despejas unas dudas.

animelafuerza@hotmail.com

De antemano mil gracias.

Unknown dijo...

Estimado Javier, necesito abusar de tus conocimientos una vez más. Resulta que desde Excell necesito hacer una serie de consultas a un archivo Access lo que implica creaciones de tablas. Mi problema esta en que una ves que haya obtenido los resultados, pueda borrar las tablas creadas e el Archivo Access, en definitiva ¿cómo puedo borrar tablas de Access con una Macro desde Excel?.

Muchas gracias nuevamente por tu gran ayuda.

Muchos Saludos.

Atte.
Andrés Silva C.

El pilt®afilla - www.3piesalgato.com dijo...

Pues no sé si se podrá, pero yo haría la consulta directamente desde excel, y listo, tal y como recuperas el recordset, o destruyes una vez finalizada la consulta.

En este artículo de access, lo que hay es una consulta a una tabla, donde recuperamos todos los campos, pero bien podríamos haber recuperado solo el último registro, o aquellos que sean de un mismo autor (la base de datos era de frases célebres), o cualquier otro criterio que deseemos.

Saludos.

Unknown dijo...

Muchas Gracias Javier por tu pronta respuesta. Mi problema consistía en que no podía borrar la tabla ya que se había ocupado con otra consulta (también hecha desde VB), solucioné el problema cerrando la conexión y abriéndola nuevamente pero solo para borrar la tabla.

Pero e surgió otro problema. Cada vez que se crea una tabla en Access el archivo aumenta de tamaño, pero al borrar la tabla el peso del archivo no disminuye a menos que compactes la base directamente desde Access. ¿Es posible compactar una base de Datos de Access con VB en Excel?

Muchas Gracias nuevamente.

Saludos.

El pilt®afilla - www.3piesalgato.com dijo...

Efectivamente, se puede compactar una base de datos de access, desde la propia aplicación excel. Acabo de modificar ligeramente el código que tengo para compactar una base de datos access, desde una página "asp", para que esta misma tarea se haga desde excel, y funciona ;-)

En el próximo artículo (la semana que viene), explicaré como podemos compactar una base de datos de access, sin salir de Excel, y lo que es mejor, sin ni siquiera abrir la base de datos de access.

Saludos

Unknown dijo...

Javier, una ultima consulta en esta serie. ¿como puedes ver o listar las tablas que tiene un archivo Access desde Excel con una Macro?
Muchas gracias una vez mas por tu valioza ayuda.

Salu2

El pilt®afilla - www.3piesalgato.com dijo...

También se puede haecr, pero lo dejaremos para otra entrega del blog. Espero que tengas paciencia, pero la verdad es que tus consultas dan bastante juego, y pueden ser aprovechadas por muchos usuarios, así que lo explicaré con algo más de detalla en un artículo.

Saludos.

Anónimo dijo...

me marco el error 3709 como lo puedo solucionar

Buen codigo.

Saludos!

Anónimo dijo...

Hola, a mi tambien me marco el mismo error error 3709 en
rs.Open Sql, Conn, dbOpenDynaset, adLockOptimistic

como lo soluciono

Saludos!

El pilt®afilla - www.3piesalgato.com dijo...

Probad a ver si la solución de Microsoft os funciona: http://support.microsoft.com/kb/276389/es

Probad eso desde VBA en Excel: Herramientas ---> Opciones ---> Pestaña General ---> Muesca en "Interrumpir en errores no controlados".

Creo que eso que propone Microsoft es lo mismo que poner el:

On error Resume Next

que he puesto antes de llamar al método Open.

Creo que ese error surge cuando la tabla de la base de datos a la que os conectáis, no devuelve ningún registro (no hay datos). Como no estoy seguro, no me hagáis mucho caso :-)

Anónimo dijo...

Hola Javier , te agradezco el tiempo que puedas darle a mi pregunta.Resulta que tengo una base de datos en Access y mi programa de VBA en Excel .Puedo conectarme de manera normal , tal como explicas en tu codigo , cuando la base de datos no esta protegida con contraseña.Me puedes indicar por favor como se modificaria el codigo cuando la base de datos esta protegida con contraseña?.Saludos

El pilt®afilla - www.3piesalgato.com dijo...

Interesante pregunta...

En el caso de que la base de datos de access tenga password, deberás cambiar la cadena de conexión con la bas de datos.

Imagina que el password es este: pepe

En tal caso, en lugar de conectarte a la base de datos con estas líneas:

Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ruta & "\" & base_de_datos)

Deberás utilizar estas otras:

Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ruta & "\" & base_de_datos) & "; Jet OLEDB:Database Password=pepe"

Muy sencillo, ¿verdad?.

EmersonIvan dijo...

Hola Javier, acerca del blog de leer una base de datos acces, se puede intorducir algun codigo donde podamos filtrar datos de los campor de la tabla para despues importarla.

gracias...

El pilt®afilla - www.3piesalgato.com dijo...

Por supuesto EmersonIvan. Solo tienes que cambiar la consulta SQL.

En el ejemplo estamos leyendo toda la tabla, a través de esta sentencia (fíjate que esta línea aparece en el código del macro):

Sql = "Select * from " & tabla

Solo tienes que saber un poco de SQL, para "decirle" a Access que datos quieres importar.

Googleando, puedes buscar algún manual de SQL. Realmente no es nada complicado decirle a Access que registros quieres recuperar.

Saludos.

Anónimo dijo...

Amigo en principio felicitaciones!!!, eres un duro..., mi pregunta es la siguiente: tengo una consulta de sql que la ejecuto desde excel, pero necesito crearle una macro para que en el momento de actualizar la consulta esta me pregunte que tipo de documento deseo filtrar?,(es algo parecido al primer comentario que hicieron a esta publicación), lo que pasa es que he creado el inputbox pero el codigo no me toma el valor...anexo codigo de la macro para la consulta... de antemano muchas gracias... Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\Carlosladino\Escritorio\CONVENIOS.xlsm;DefaultDir=C:\Documents and Settings\Carlo" _
), Array("sladino\Escritorio;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Hoja1$`.CODI_DOCU, `Hoja1$`.NUM_DOCU, `Hoja1$`.FEC_COMPRO, `Hoja1$`.ESTADO, `Hoja1$`.CODIGO_CUENTA, `Hoja1$`.NOM_CUENTA, `Hoja1$`.NIT, `Hoja1$`.NOM_TERCE, `Hoja1$`.DETALLADO, `Hoja1$`.CODIGO_C" _
, _
"ENTRO, `Hoja1$`.NOMBRE_CENTRO, `Hoja1$`.DEBITO, `Hoja1$`.CREDITS, `Hoja1$`.ASUNTO" & Chr(13) & "" & Chr(10) & "FROM `Hoja1$` `Hoja1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Hoja1$`.CODI_DOCU='11')" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tabla_Consulta_desde_Excel_Files"
.Refresh BackgroundQuery:=False
End With
Range("A2").Select
End Sub

Anónimo dijo...

Hola, felicitaciones me ha servido de maravilla, pero se me presenta un inconveniente, los datos los pasa a la hoja1 y ahi tengo los path de las bases que manejo y me los borra, como hago para que los datos me los coloque en la hoja2 (por ejemplo).

Agradezco la ayuda.
LEC

El pilt®afilla - www.3piesalgato.com dijo...

Pues antes de la línea donde le decimos que empiece a escribir los datos, le pondremos en qué hoja vamos a escribir. Es decir, antes de la línea esta:

Range(celda_inicial).Select

Ponemos esto (antes de esa línea anterior):

'Si su nombre interno es Hoja2
'(el nombre que vemos desde VBA)
Hoja2.select

O sino, si el nombre visible de la pestaña es Hoja 2, entonces pondremos esto:

'seleccionamos la Hoja 2
Sheets("Hoja 2").Select

Saludos

Todos somos muy ignorantes. Lo que ocurre es que no todos ignoramos las mismas cosas. Albert Einstein dijo...

Hola a todos , muy interesante este tema. Pero no me funciona con bases de datos de access 2007 *.accdb Que puede fallar? que debo cambiarles?

Saludos y gracias

El pilt®afilla - www.3piesalgato.com dijo...

Tienes que hacer 2 cambios. El primero, es localizar esta línea:

base_de_datos = "frases-celebres.mdb"

Y cambiarla por esto (si es que estás utilizando la base de datos del ejemplo):
base_de_datos = "frases-celebres.accdb"

El segundo cambio, consiste en localizar la línea:

Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _

Y cambiarla por esta otra:

Conn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _

Con esos 2 cambios, te funcionará en Excel 2007, para importar una base de datos Access 2007.

Saludos.