EXPORTAR UNA TABLA O CONSULTA DE ACCESS A EXCEL CON ADO

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):

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado

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.

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado2

Y ahora que tenemos la macro lista, podemos ejecutarla y veremos como la macra, 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 trabaja de definir la conexión.

El resultado de la macro es el siguiente:

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado3

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

Anuncios

REALIZAR CONSULTA SQL DESDE VBA EN EXCEL, HACER UNA CONSULTA DE ACCESS EN EXCEL

Hola a todos,

¿Qué tal estáis?, espero que bien 🙂  Si recordáis en el anterior post comenté que en la próxima entrada mostraría un ejemplo de como podemos utilizar una consulta sql en excel, o lo que es muy parecido, como replicar una consulta que hemos hecho utilizando Access pero en Excel. Pues bien, hoy es lo que vamos a ver.

Lo primero que voy a hacer es realizar la consulta en Access y luego replicarla en Excel, de forma que se pueda ver claramente el proceso y la comparación. Imaginemos que estamos trabajando para una empresa que vende jamones … nos llamamos La Pata Negra, S.L. y resulta que somos los encargados de seleccionar dentro de la plantilla de la empresa a un nuevo comercial para que venda nuestros productos.

Como es habitual, el jefe nos entrega una relación de empleados (que tiene desde hace tiempo y no está actualizada, es decir, hay empleados que ya no están y hay otros nuevos que no tiene, a esta tabla vamos a llamarla “Listado”. Por otro lado hemos conseguido que desde el departamento de personal nos envíen un archivo con la información actualizada de los empleados así como una serie de datos, a esta tabla vamos a llamarla “Datos”.

Nuestro trabajo va a ser sencillo, como en la primera tabla sabemos que algunos empleados pueden ya no estar y en la segunda sabemos que están todos, debemos cruzar los datos y obtener detalle de los empleados antiguos (que puedan seguir en la empresa) y los nuevos.

Estas serían las tablas:CONSULTAS_SQL_1

y la consulta a realizar (muy básica), sería la siguiente: necesitamos buscar aquellos empleados que estén en la tabla “Datos” y que además coincidan con los que están en la tabla “Listado” de forma que vamos a obtener los empleados antiguos que siguen en la actualidad y también los nuevos. Pero además queremos que busque aquellos que tengan estudios de “MASTER“, que vivan en “MADRID” y que tengan menos de “30” años.

En Access la consulta sería esta, SIEMPRE uniendo por el campo IDENTIFICADOR, que es un registro único para cada empleado:
CONSULTAS_SQL_2

 

Donde además queremos que nos muestre información de “INGLÉS” y si posee “VEHICULO”. Una vez ejecutada la consulta nos ofrece a cuatro candidatos que poseen los requisitos que hemos definido previamente:
CONSULTAS_SQL_3

Ahora solo faltaría tomar una decisión de a quién seleccionar en base a criterios que ya no serían tema este blog 🙂

EN EXCEL

Pues ahora esto mismo lo voy a realizar en Excel. Para ello debemos contar con las dos tablas de referencia, “DATOS” y “LISTADO” que vamos importar a Excel, cada una en una hoja y agregamos una tercera que vamos a llamar “RESULTADO”, que es donde mostremos el resultado de la consulta:CONSULTAS_SQL_4

Antes de continuar y mostrar el código que voy a utilizar, os comento que es necesario que actualicéis referencias en el libro de Excel, en concreto debéis marcar las siguiente para que la conexión de ADO funcione correctamente. Esto lo tenéis que hacer entrando en el editor de Visual pinchar en Herramientas y luego en Referencias. Y una vez que se abra el cuadro para elegir las referencias, marcáis las siguientes. (las referencias se quedan en el libro, por lo que en este archivo no hace falta que las marquéis, pero sí será necesario en un nuevo libro).

CONSULTAS_SQL_5
Ahora que tenemos la hoja preparada para el código, lo voy a poner completo para luego comentarlo:

Código  completo:
Public Sub CONSULTA_SQL()
'Definimos las variables y creamos los
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection
'Cada vez que ejecutemos la consulta borramos los datos de la consulta anterior en la hoja resultado_
'si se produce un error por estar la hoja vacía, saltamos directamente al proceso de consulta a través de la etiqueta control_e
On Error GoTo control_e
LIMPIARDATOS = Application.CountA(Worksheets("RESULTADO").Range("a:a"))
Worksheets("RESULTADO").Range("A1:G" & LIMPIARDATOS).ClearContents
Worksheets("RESULTADO").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
control_e:
'indicamos los parámetros de la consulta SQL
obSQL = "SELECT [DATOS$].[IDENTIFICADOR], [DATOS$].[NOMBRE], [DATOS$].[ESTUDIOS] , [DATOS$].[INGLES],[DATOS$].[VEHICULO],[DATOS$].[PROVINCIA],[DATOS$].[EDAD]" & _
"FROM [LISTADO$] RIGHT JOIN [DATOS$] ON [LISTADO$].[IDENTIFICADOR] = [DATOS$].[IDENTIFICADOR]" & _
"WHERE((([DATOS$].[ESTUDIOS]) ='MASTER') AND (([DATOS$].[PROVINCIA]) ='MADRID') AND (([DATOS$].[EDAD]) <30))"
'Creamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\CONSULTA_SQL_EN_EXCEL.xls"
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grabar los datos de la consulta creando el objeto recordset
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
Res = obRes & Dataread.Fields(0).Value & " " & Dataread.Fields(1).Value
Dataread.MoveFirst
'Copiamos los datos a la hoja RESULTADO
With Worksheets("RESULTADO").Select
Worksheets("RESULTADO").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
With Worksheets("RESULTADO")
.Range("a1") = ("IDENTIFICADOR")
.Range("B1") = ("NOMBRE")
.Range("C1") = ("ESTUDIOS")
.Range("D1") = ("INGLES")
.Range("E1") = ("VEHICULO")
.Range("F1") = ("PROVINCIA")
.Range("G1") = ("EDAD")
End With
'Pintamos de rojo Los encabezados
With Worksheets("RESULTADO")
.Range("A1").Interior.Color = vbRed
.Range("B1").Interior.Color = vbRed
.Range("C1").Interior.Color = vbRed
.Range("D1").Interior.Color = vbRed
.Range("E1").Interior.Color = vbRed
.Range("F1").Interior.Color = vbRed
.Range("G1").Interior.Color = vbRed
End With
Loop
End Sub

Como podéis ver, básicamente lo que hacemos es realizar una consulta ADO entre ambas hojas para conseguir el resultado indicado.

La consulta SQL es muy parecida a la que se realiza desde Access:
obSQL = "SELECT [DATOS$].[IDENTIFICADOR], [DATOS$].[NOMBRE], [DATOS$].[ESTUDIOS] , [DATOS$].[INGLES],[DATOS$].[VEHICULO],[DATOS$].[PROVINCIA],[DATOS$].[EDAD]" & _
"FROM [LISTADO$] RIGHT JOIN [DATOS$] ON [LISTADO$].[IDENTIFICADOR] = [DATOS$].[IDENTIFICADOR]" & _
"WHERE((([DATOS$].[ESTUDIOS]) ='MASTER') AND (([DATOS$].[PROVINCIA]) ='MADRID') AND (([DATOS$].[EDAD]) <30))"

Ahora la vamos a comentar, primero determinamos aquellos campos que necesitamos que sean visibles:
"SELECT [DATOS$].[IDENTIFICADOR], [DATOS$].[NOMBRE], [DATOS$].[ESTUDIOS] , [DATOS$].[INGLES],[DATOS$].[VEHICULO],[DATOS$].[PROVINCIA],[DATOS$].[EDAD]"

Luego indicamos a partir de qué tablas y que relación de consulta vamos a realizar. En este caso queremos saber todos aquellos que se encuentran en la tabla Datos y los que tienen el mismo identificador en la tabla “Listado”. Es decir la opción tres que se expresa en la consulta de Access:

CONSULTAS_SQL_6

Para ello escribimos RIGHT JOIN * y unimos las tablas por el campo [IDENTIFICADOR], así:
"FROM [LISTADO$] RIGHT JOIN [DATOS$] ON [LISTADO$].[IDENTIFICADOR] = [DATOS$].[IDENTIFICADOR]"

(*) Los otros dos tipos de consulta son LEFT JOIN (Opción 2) o INNER JOIN (Opción 3).

El siguiente paso es indicar que queremos que sus estudios sean MASTER, que sean de MADRID y que tengan menos de 30 años:
"WHERE((([DATOS$].[ESTUDIOS]) ='MASTER') AND (([DATOS$].[PROVINCIA]) ='MADRID') AND (([DATOS$].[EDAD]) <30))"

El resto de la macro lo que hace es grabar la consulta en un recordset y devolver el resultado con los parámetros indicados en la hoja RESULTADO. He incluido un control para errores cuando al ejecutar la macro y limpiemos los datos de la consulta, que siempre debería existir algún contenido, en caso de no tener contenido, no se produzca un error.

El resultado sería el siguiente, ¿os resulta familiar?
CONSULTAS_SQL_7

Efectivamente, es el mismo resultado que utilizando Access.

Casi se me olvida, la fuente de los datos que se indica en el código (en rojo) ha de hacer referencia (ser el mismo) al nombre de nuestro archivo Excel.
.ConnectionString = “DATA SOURCE=” & Application.ActiveWorkbook.Path + “\CONSULTA_SQL_EN_EXCEL.xls

Importante: si vinculáis la hoja con otro archivo y es diferente de .xls debéis modificar en la conexión los siguientes elementos:

.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0; HDR=YES"

Como siempre os dejo el ejemplo para que probéis con un caso práctico, os he añadido un botón para ejecutar la macro en la hoja LISTADO.

Descarga el archivo de ejemplo pulsando en: CONSULTA_SQL_EN_EXCEL