por Hola a todos:
Aunque esta web está dedicada básicamente a Excel, hoy voy a trabajar un poco con Access. En muchas ocasiones, cuando combinamos ambos programas (Excel y Access) obtenemos grandes resultados. El tema de hoy trata precisamente de cómo podemos realizar un pequeño proceso en ADO para exportar una tabla o consulta que tenemos en Access (desde Access).
Sobre ADO hay bastantes ejemplos en esta web, pero en ninguno de ellos programo directamente en Access, hoy lo voy a hacer.
Utilizaremos un ejemplo simple, para ello tenemos una tabla en Access con los datos de los empleados de unos grandes almacenes (la base de datos que siempre pongo de ejemplo):
Esta tabla se denomina DATOS y nuestra intención es exportarla a Excel. Para ello vamos a utilizar la siguiente macro que pegaremos en el editor de VBA de Access:
Option Compare Database
Sub ExporExcel()
Dim APIExcel As Object
Dim AddLibro As Object
Dim AddHoja As Object
Dim nombreHoja As String
Dim i As Integer
Dim consulta As New ADODB.Recordset
'Creamos conexión y recorset
Set cnn = CurrentProject.Connection
consulta.Open "SELECT * FROM DATOS", cnn, adOpenForwardOnly, adLockReadOnly
'Damos nombre a la hoja con la que vamos a exportar los datos
nombreHoja = "DATOS"
'Creamos objeto excel y nuevo libro y no mostramos el archivo
Set APIExcel = CreateObject("Excel.Application")
Set AddLibro = APIExcel.Workbooks.Add
APIExcel.Visible = False
'Añadimos hoja al libro nuevo y nombramos pestaña
Set AddHoja = AddLibro.Worksheets(1)
If Len(nombreHoja) > 0 Then AddHoja.Name = Left(nombreHoja, 30)
'Traemos los datos de cabecera de la tabla Access y los pegamos en la hoja excel
columnas = consulta.Fields.Count
For i = 0 To columnas - 1
APIExcel.Cells(1, i + 1) = consulta.Fields(i).Name
Next i
'Pegamos los datos de la tabla en la nueva hoja
consulta.MoveFirst
AddHoja.Range("A2").CopyFromRecordset consulta
'Damos formato a las columnas, ajustando contenidos
With APIExcel.ActiveSheet.Cells
.Select
.EntireColumn.AutoFit
.Range("A1").Select
End With
'Mostramos la hoja
APIExcel.Visible = True
'cerramos los objetos de la consulta
consulta.Close
cnn.Close
End Sub
Antes de finalizar, os recuerdo la necesidad de marcar en las referencias la librería de ADO Activex Data Objects 2.8 Library, es importante que lo hagáis, de lo contrario, la macro no va a funcionar.
Y ahora que tenemos la macro lista, podemos ejecutarla y veremos como la macro, crea un archivo Excel y pasa los datos de la consulta a una pestaña que va a renombrar con el nombre de DATOS.
En la propia macro ya os voy comentando qué es lo que hace cada línea de código, así que no quiero ser repetitivo, pero es interensate el uso de «CurrentProject.Connection» dado que nos ahorra todo el trabajo de definir la conexión.
El resultado de la macro es el siguiente:
Y esto ha sido todo, espero que os resulte de interés y de utilidad 🙂
Os dejo el arhcivo en Google Drive (en WordPress no es posible subir archivos de Access).
Descarga el archivo de ejemplo pulsando en: EXPORTAR UNA TABLA O CONSULTA DE ACCESS A EXCEL CON ADO
Excelente muy muy bueno
Excelente!!!!!!
Explicación Magistral
Una pregunta: ¿Cómo guardas el archivo excel?
Hola Luis.
Me alegro que te guste. Para guardar no hay nada programado, lo guarda el usuario manualmente. Pero se podría programar el guardado.
Saludos.
Si Gracias Segundo, lo que hice fue esto:
APIExcel.Application.ActiveWorkbook.SaveAs FileName:=Directorio & Archivo
Hola.
Necesito exportar una consulta que tiene mas de 2 millones de registros, exportar en formato txt pero se me queda muy lento el access y tengo que esperar y esperar….
otra solucion porfis, q no sea sql porque no tengo.
Hola Alicia:
No tengo ninguna macro preparada para exportar una consulta a TXT. Pero es interesante y crearé un post sobre este tema. Qué tipo de TXT necesitas generar, ancho fijo o delimitado?.
Espero tu respuesta. Saludos.
Para eso, es mejor exportar con filtro si trabajas con fechas en especifico, puedes agrupar valores y exportar por meses
Como se puede hacer una exportacion y poner las cabeceras que uno requiera si los datos estan en forma horizontal , ponerlos vertical
Hola Daniel:
Envíame un ejemplo de lo que necesitas. Cómo tienes los datos y cómo te gustaría mostrarlos, así podré ayudarte.
Saludos!
me sale error de compilacion por esto -> Dim consulta As New ADODB.Recordset
Ese error es porque la referencia no existe en la biblioteca o no está marcada. Verifica que la tienes activada en la biblioteca de referencias (como indico en el post) o verifica que no la tienes instalada. Saludos.
Hola, a ver si puede ayudarme, tengo una consulta realizada ha través del diseño de consultas, aún estoy un poco verde para hacerlo en código, donde usted pone
‘Creamos conexión y recorset
Set cnn = CurrentProject.Connection
consulta.Open «SELECT * FROM DATOS», cnn, adOpenForwardOnly, adLockReadOnly
Yo he puesto lo siguiente
‘Creamos conexión y recorset
Set cnn = CurrentProject.Connection
consulta.Open «SELECT * FROM MiConsulta», cnn, adOpenForwardOnly, adLockReadOnly
Y me da el error ‘-2147217904(80040e10)’ en tiempo de ejecución
No se ha especificado valores para alguno de los parámetros requeridos
Si pongo el nombre de una tabla si me sale correctamente, pero lo quiero a través de la consulta porque filtro un campo entre dos fechas.
Si puede ayudarme se lo agradeceria.
Hola Ricardo:
Tienes la consulta abierta cuando ejecutas el código?, es probable que ese sea el problema. Saludos
Hola
Muchas gracias por el post.
Tengo el mismo problema de Ricardo, y no tengo la consulta abierta. Por qué más puede ser?
Lo he hecho de otra manera y he conseguido exportar los datos que quiero.
Ahora mi problema es que tengo unos datos en formato hora que los exporta en formato fecha. Hay alguna forma de copiar los datos con formato determinado?
Muchas gracias! Un saludo
Tendrías que modificar el formato en la consulta SQL, modificándola y realizando ahí los cambios.
Saludos
Hola, una consulta, para el caso que vengo desarrollando, la consulta que debo exportar depende de un par de filtros. Ambas consultas arrojan los resultados adecuados en un listbox. Sin embargo, cuando ejecuto el código que usted comparte, los resultados de una se exporta sin ningún problema a la hoja de excel, pero en la otra me arroja el siguiente error: «Se ha producido el error ‘3201’ en tiempo de ejecución. El valor de BOF o EOF es True, o el actual registro se eliminó; la operación solicitada requiere un registro actual.» Este error se produce al ejecutarse la instrucción consulta.MoveLast.
La consulta que genera el error es la siguiente:
SELECT * FROM vMovimientosDetalle WHERE NOMBRE LIKE ‘*bcd*’ AND IdOp = 1 AND Fecha BETWEEN #01/01/2020# And #09/28/2020#
Con la siguiente consulta el código funciona bien
SELECT * FROM vMovimientosDetalle WHERE idOp = 1 AND Fecha BETWEEN #01/01/2020# And #09/28/2020#
Alguna idea de qué puede estar saliendo mal?
Muchas gracias por compartir sus conocimientos y por el tiempo que le dedica.
Hola Kike: Tendría que ver el código o un ejemplo del error. Pero podrías probar a borrar la línea:
consulta.MoveFirst
Y ejecutar solo la consulta. Saludos.
Hola me encanto pero una pregunta yo quiero importar desde un excel y no exportar desde un access como podria ser ?
Hola Ernesto,
Tienes varios ejemplos de post en esta web, utilizar el buscador para consultar.
Saludos.
Hola , buenas tardes
Recurro a este procedimiento para poder realizar la exportación implemento parámetros
utilizando la siguiente cadena:
SELECT*FROM (nombre de la tabla)WHERE (nombre del campo de la tabla) LIKE ‘*» Me.(nombre del campo de un formulario) «*’
al ejecutar la macro me devuelve una ventana en la que me indica que el valor de BOF o EOF es true, o el actual registro se elimino, la operación solicitada requiere un registro actual.
Para mi es importante que pueda pasar el parámetro like ya que necesito que al exportar me exporte la información coincidente con un campo de un formulario.
Cabe destacar que al poner la cadena con un parámetro «=», dela siguiente manera:
SELECT*FROM (nombre de la tabla)WHERE (nombre del campo de la tabla) = » Me.(nombre del campo de un formulario) »
Si funciona y realiza la exportación de los registros coincidentes.
No se si tengas una solución para este problema y me puedas colaborar.
Quedo atento, gracias
tendría que ver el ejemplo y probarlo. En el que he indicado en este post se admite perfectamente el like:
consulta.Open «SELECT * FROM DATOS WHERE SECCIÓN like ‘DE%’ «, cnn, adOpenForwardOnly, adLockReadOnly
Ojo con los comodines … igual lo que necesitas es % en lugar de *
Buen día.
La respuesta al comentario anterior fue de gran ayuda, gracias…
Pero ahora necesito tu ayuda ya que tengo dos inconvenientes:
A veces la consulta exportada en el excel me arroja resultados incoherentes con la consulta. Ejemplo
Si quiero buscar la fecha de hoy 23/06/2021 pero en la base de datos no se encuentra ningún registro coincidente, el informé de igual manera me arroja registros pero de otras fechas.
Entonces termino de explicar el primero y por consiguiente el segundo…. Necesitó que no me arrojé resultados incoherentes a la consulta y como segundo punto que al no encontrar registrós concientes me arrojé un comentario que me diga que no se puede abrir el informe.
De ante mano agradezco tu ayuda con este inconveniente y nuevamente te agradezco por dar respuesta a mi solicitud anterior.
Tendría que ver que sentencias SQL estás usando para poder responderte.
Saludos
Private Sub txtEXPORTAR_Click()
Dim APIExcel As Object
Dim AddLibro As Object
Dim AddHoja As Object
Dim nombreHoja As String
Dim i As Integer
Dim consulta As New ADODB.Recordset
Dim Fecha1
Dim Fecha2
Dim Estado As String
Fecha1 = Me.txtFECHA1.Value
Fecha2 = Me.txtFECHA2.Value
Set cnn = CurrentProject.Connection
consulta.Open «SELECT * FROM RECEPCION WHERE [FECHA_PUESTA_EN_PRODUCCION]>= #» & Fecha1 & «# AND [FECHA_PUESTA_EN_PRODUCCION] 0 Then AddHoja.Name = Left(nombreHoja, 30)
columnas = consulta.Fields.Count
For i = 0 To columnas – 1
APIExcel.Cells(1, i + 1) = consulta.Fields(i).Name
Next i
consulta.MoveFirst
AddHoja.Range(«A2»).CopyFromRecordset consulta
With APIExcel.ActiveSheet.Cells
.Select
.EntireColumn.AutoFit
.Range(«A1»).Select
End With
APIExcel.Visible = True
consulta.Close
cnn.Close
End Sub
Este es el bloque de código entero, si no encuentras el error, de todas formas indícame por favor como puedo poner el cuadro de aviso que indique que no se puede abrir el informe cuando no hay resultados coincidentes, gracias.
Creo que el problema está en el formato de la fecha que le pasas en el SQL. Deberías verificarlo con algunas pruebas y ver si realmente está filtrando correctamente. Prueba a indicar la fecha en el código (sin estar en una variable) y si filtra es que el problema está en el formato de la fecha en la variable.
Saludos.
EXCELENTE RECURSO…FELICITACIONES …TENGO UNA PREGUNTA..
De esta misma programación, como puedo incorporar multiples tablas o consulta? Me explico:
Siguiendo con el ejemplo, se exporto la tabla «DATOS» a excel en la pestaña DATOS y con los registros que empiezan desde la celda A1.
¿Como lo hago si tengo otra tabla llamada TABLA1, y los campos campo1, campo2 pero que quiero que empiecen desde la celda M2 del mismo excel,
y asi sucesivamente entendiendo que tengo muchas tablas?
Lo pregunto por que he tratado de realizar una sabana de información pero no lo puedo juntar todos las tablas en una sola pestaña y ordenado de forma horizontal.
saludos.
Elias
Sub ExporExcel()
Dim APIExcel As Object
Dim AddLibro As Object
Dim AddHoja As Object
Dim nombreHoja As String
Dim i As Integer
Dim consulta As New ADODB.Recordset
‘Creamos conexión y recorset
Set cnn = CurrentProject.Connection
consulta.Open «SELECT * FROM DATOS «, cnn, adOpenForwardOnly, adLockReadOnly
‘Damos nombre a la hoja con la que vamos a exportar los datos
nombreHoja = «DATOS1»
‘Creamos objeto excel y nuevo libro y no mostramos el archivo
Set APIExcel = CreateObject(«Excel.Application»)
Set AddLibro = APIExcel.Workbooks.Add
APIExcel.Visible = False
‘Añadimos hoja al libro nuevo y nombramos pestaña
Set AddHoja = AddLibro.Worksheets(1)
If Len(nombreHoja) > 0 Then AddHoja.Name = Left(nombreHoja, 30)
‘Traemos los datos de cabecera de la tabla Access y los pegamos en la hoja excel
columnas = consulta.Fields.Count
For i = 0 To columnas – 1
APIExcel.Cells(1, i + 1) = consulta.Fields(i).Name
Next i
‘Pegamos los datos de la tabla en la nueva hoja
consulta.MoveFirst
AddHoja.Range(«A2»).CopyFromRecordset consulta
AddHoja.Range(«A2»).CopyFromRecordset consulta
‘Damos formato a las columnas, ajustando contenidos
With APIExcel.ActiveSheet.Cells
.Select
.EntireColumn.AutoFit
.Range(«A1»).Select
End With
‘Mostramos la hoja
APIExcel.Visible = True
‘cerramos las variables de la consulta
consulta.Close
cnn.Close
En esta web puedes encontrar varios post en los que se explica como agrupar información de varios archivos de Excel. Puede que sea de utilidad para realizar lo que indicas. Lo otro, tendría que estudiarlo con calma.
Saludos.
Como hago para que un botón ejecute el código que indicas, lo coloque funciono pero al hacer docmd.openmodule me muestra es el código y no crea el archivo como cuando lo hago por Visual basic
Supongo que tendrás que vincular la macro a la ejecución del botón al igual que Excel.