REALIZAR CONSULTAS SQL DE UNIÓN EN EXCEL CON ADO

Hola a todos!:

Hacía tiempo que no subía un post nuevo, pero últimamente estoy bastante escaso de tiempo y esta semana me han enviado algunas consultas de gran complejidad, lo que me ha dejado poco margen para publicar. Pero hoy sí puedo : )

Para este post escribiré acerca de las ventajas de las consultas de unión a la hora de realizar nuestros trabajos y programaciones.

Básicamente una consulta de unión se especifica en SQL con las instrucciones UNION y UNION ALL, ambas realizan el trabajo de unir dos o más consultas SQL, solo que cuando es UNION el resultado de la consulta solo devuelve los valores distintos (sin duplicados) y cuando es UNION ALL devuelve todos los valores (pueden incluir duplicados).

Para el ejemplo de hoy se podrían utilizar ambas, dado que los elementos son distintos en las dos bases de datos.

Imaginad que tenemos dos tablas (que pueden ser archivos independientes, pero para este ejemplo lo hago todo dentro del mismo archivo). Una se corresponde con un grupo de trabajadores de unos grandes almacenes y la otra con otro grupo, las llamaré GRUPO1 y GRUPO2 respectivamente:

REALIZAR CONSULTAS SQL DE UNION EN EXCEL

Pues bien, deseamos obtener un único archivo con lo siguiente:

Del GRUPO1 personas con sexo = “MUJER” y estudios = “DIPLOMADOS” y que los nombres no sean nulos (dado que hemos detectado que en algunas ocasiones el campo “NOMBRE COMPLETO” tiene celdas vacías. La sentencia SQL sería esta:

"SELECT [GRUPO1$].[NOMBRE COMPLETO],[GRUPO1$].[EDAD], [GRUPO1$].[SEXO], [GRUPO1$].[ESTUDIOS], 'GRUPO1' AS GRUPO FROM [GRUPO1$] WHERE NOT [GRUPO1$].[NOMBRE COMPLETO] IS NULL AND [GRUPO1$].[SEXO]='MUJER' AND [GRUPO1$].[ESTUDIOS]='DIPLOMADOS' "

Del GRUPO2 personas con sexo = “HOMBRE” y estudios = “DIPLOMADOS” y que los nombres no sean nulos (dado que hemos detectado que en algunas ocasiones el campo “NOMBRE COMPLETO” tiene celdas vacías. La sentencia SQL sería esta:

SELECT [GRUPO2$].[NOMBRE COMPLETO],[GRUPO2$].[EDAD], [GRUPO2$].[SEXO], [GRUPO2$].[ESTUDIOS], 'GRUPO2' AS GRUPO FROM [GRUPO2$] WHERE NOT [GRUPO2$].[NOMBRE COMPLETO] IS NULL AND [GRUPO2$].[SEXO]='HOMBRE' AND [GRUPO2$].[ESTUDIOS]='DIPLOMADOS' "

Una vez definidas las consultas, introducimos la instrucción UNION ALL y componemos nuestro string para pasar la consulta en nuestro código, así sería completo:

obSQL = "SELECT [GRUPO1$].[NOMBRE COMPLETO],[GRUPO1$].[EDAD], [GRUPO1$].[SEXO], [GRUPO1$].[ESTUDIOS], 'GRUPO1' AS GRUPO FROM [GRUPO1$] WHERE NOT [GRUPO1$].[NOMBRE COMPLETO] IS NULL AND [GRUPO1$].[SEXO]='MUJER' AND [GRUPO1$].[ESTUDIOS]='DIPLOMADOS' UNION ALL " & _
"SELECT [GRUPO2$].[NOMBRE COMPLETO],[GRUPO2$].[EDAD], [GRUPO2$].[SEXO], [GRUPO2$].[ESTUDIOS], 'GRUPO2' AS GRUPO FROM [GRUPO2$] WHERE NOT [GRUPO2$].[NOMBRE COMPLETO] IS NULL AND [GRUPO2$].[SEXO]='HOMBRE' AND [GRUPO2$].[ESTUDIOS]='DIPLOMADOS' "

El resultado de ejecutar la consulta sería el siguiente:

REALIZAR CONSULTAS SQL DE UNION EN EXCEL1

Efectivamente, nuestras dos consultas se han unido en una única consulta. Mostrando un total de 3 mujeres en el GRUPO1 y 1 hombre en el GRUPO2. Indicar que tal y como queríamos hemos obviado las celdas en blanco del campo NOMBRE COMPLETO.

Ahora os dejo la macro completa:

Option Explicit
Sub GENERAR_CONSULTA()
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, fin As Integer, milibro As String, i As Long
Dim titulo As String
fin = Application.CountA(Sheets("UNION").Range("A:A"))
'Borramos datos de consultas anteriores
Sheets("UNION").Range("A2:E" & fin + 1).Clear
'construimos nuestras dos consultas y las unimos
obSQL = "SELECT [GRUPO1$].[NOMBRE COMPLETO],[GRUPO1$].[EDAD], [GRUPO1$].[SEXO], [GRUPO1$].[ESTUDIOS], 'GRUPO1' AS GRUPO FROM [GRUPO1$] WHERE NOT [GRUPO1$].[NOMBRE COMPLETO] IS NULL AND [GRUPO1$].[SEXO]='MUJER' AND [GRUPO1$].[ESTUDIOS]='DIPLOMADOS' UNION ALL " & _
"SELECT [GRUPO2$].[NOMBRE COMPLETO],[GRUPO2$].[EDAD], [GRUPO2$].[SEXO], [GRUPO2$].[ESTUDIOS], 'GRUPO2' AS GRUPO FROM [GRUPO2$] WHERE NOT [GRUPO2$].[NOMBRE COMPLETO] IS NULL AND [GRUPO2$].[SEXO]='HOMBRE' AND [GRUPO2$].[ESTUDIOS]='DIPLOMADOS' "
'Obtenemos el nombre del libro
milibro = ThisWorkbook.Name
'Creamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & milibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'pasamos información a la hoja UNION
Do Until Dataread.EOF
Dataread.MoveFirst
With Worksheets("UNION")
.Cells(2, 1).CopyFromRecordset Dataread
'Indicamos encabezados
For i = 0 To Dataread.Fields.Count - 1
titulo = Dataread.Fields(i).Name
.Cells(1, i + 1) = titulo
Next
End With
Loop
'ejecutamos el resto de consultas
Set Dataread = Nothing
Set cnn = Nothing
End Sub

Como podéis observar, la estructura del código es la misma que suelo publicar en los post en los que trabajo con ADO y SQL en Excel.

Y eso es todo. Es una forma útil cuando necesitamos consolidar información de varios archivos pero especificando criterios distintos en cada base de datos.

Descarga el archivo de ejemplo pulsando en: REALIZAR CONSULTAS SQL DE UNIÓN EN EXCEL CON ADO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

REALIZAR VARIAS CONSULTAS SQL CON ADO EN LA MISMA MACRO

Hola a todos!.

¿Qué tal os va?, espero que bien!. Hoy voy tratar el tema de las consultas SQL con ADO, en concreto voy a mostraros un método para poder realizar varias consultas SQL en una única macro.

Voy a partir de un post anterior para realizar el ejercicio: CRUZAR DOS TABLAS EN EXCEL USANDO SQL

En este post, mostraba la forma de realizar un cruce de dos bases de datos mediante consultas SQL usando ADO para obtener, altas, bajas, y movimientos entre los departamentos de unos grandes almacenes.

Si consultais las macros, veréis que hay cuatro en total, una para cada consulta. El motivo de hacerlo de esta forma es para se pueda comprender mejor la forma de usar ADO para quienes lo están empezando a programar y no me gusta complicar en exceso las programaciones dado que muchas veces, lo complicado en lugar de generar curiosidad, genera frustración.

Dicho esto, y una vez publicada la entrada anterior, ya puedo mostrar la forma de hacerlo en una única macro.

El código que vamos a utilizar es similar al ya utilizado, solo que vamos a escribir las cuatro sentencias SQL en el mismo código:

Sub GENERAR_CONSULTA()
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, fin As Integer, milibro As String, i As Long
Dim alta As String, baja As String, alta_seccion As String, baja_seccion As String
Dim consulta As Variant, titulo As String
fin = Application.CountA(Sheets("MOVIMIENTOS").Range("A:A"))
'Borramos datos de consultas anteriores
Sheets("MOVIMIENTOS").Range("A2:D" & fin + 1).Clear
'indicamos los parámetros de las consultas que necesitamos:
'buscamos empleados nuevos
alta = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'NUEVO EMPLEADO' AS ESTADO " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ANTERIOR$].[ID]) IS NULL)"
'buscamos empleados que han sido baja
baja = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA' " & _
"FROM [BBDD_ANTERIOR$] LEFT JOIN [BBDD_ACTUAL$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ACTUAL$].[ID]) IS NULL)"
'buscamos movimientos de alta en departamento
alta_seccion = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'ALTA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ACTUAL$].[SECCION]) NOT LIKE [BBDD_ANTERIOR$].[SECCION] "
'buscamos movimientos de baja en departamento
baja_seccion = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ANTERIOR$].[SECCION]) NOT LIKE [BBDD_ACTUAL$].[SECCION] "
'iniciamos loop por cada consulta SQL
For Each consulta In Array(alta, baja, alta_seccion, baja_seccion)
fin = Application.CountA(Sheets("MOVIMIENTOS").Range("A:A")) + 1
'Obtenemos el nombre del libro
milibro = ThisWorkbook.Name
'Creamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & milibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
Set Dataread = New ADODB.Recordset
With Dataread
.Source = consulta
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'pasamos información a la hoja movimientos
Do Until Dataread.EOF
Dataread.MoveFirst
With Worksheets("MOVIMIENTOS")
.Cells(fin, 1).CopyFromRecordset Dataread
'Indicamos encabezados
For i = 0 To Dataread.Fields.Count - 1
titulo = Dataread.Fields(i).Name
.Cells(1, i + 1) = titulo
Next
.Cells(1, Dataread.Fields.Count) = "ESTADO"
End With
Loop
'ejecutamos la siguiente consulta
Next consulta
Set Dataread = Nothing
Set cnn = Nothing
End Sub

Como podéis observar, reducimos significativamente el tamaño de nuestro código y lo hacemos más eficiente y compacto. La clave está es crear un loop sobre el que iremos pasando cada una de las consultas SQL. Esto se consigue indicando el nombre de cada consulta en un array:

For Each consulta In Array(alta, baja, alta_seccion, baja_seccion)

El objeto “consulta” contendrá la secuencia SQL de cada consulta, lo que nos va a permitir pasar esos parámetros al recordset:

.Source = consulta

De esta forma en cada ciclo del loop tendremos el resultado de cada consulta, luego solo tenemos que pasar la información a la hoja movimientos y utilizar la variable “fin” para indicar el final de los datos de cada consulta y que se pueda mostrar el resultado en conjunto.

Una vez ejecutada la macro, tenemos el siguiente resultado, el mismo que el post del que estamos haciendo referencia.

REALIZAR VARIAS CONSULTAS SQL EN LA MISMA MACRO CON ADO

Y esto es todo, un método muy sencillo (dentro de la complejidad de ADO), que seguro os ayudará a realizar códigos más reducidos y eficientes.

Descarga el archivo de ejemplo pulsando en: REALIZAR VARIAS CONSULTAS SQL CON ADO EN LA MISMA MACRO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

CRUZAR DOS TABLAS EN EXCEL USANDO SQL

Hola a todos!

Hace tiempo que no escribía una entrada, pero estos últimos días he estado bastante mal debido a una gastroenteritis que me ha dejado destrozado, y aún sigo en recuperación.

Aún así, he querido sacar tiempo para escribir un nuevo post. Esta vez me gustaría hablar sobre el cruce de archivos, algo tan corriente en Excel como el uso de función buscarv. Y es que realmente, lo que se pretende hacer cuando se cruzan archivos es buscar un valor y determinar si está o no en esa base de datos.

Existe una forma un poco más elaborada para realizar esta tarea, muy útil para trabajar con procesos definidos y con la misma estructura de datos. Por ejemplo, imaginad que tenéis la información de los empleados de unos grandes almacenes en dos momentos distintos, uno anterior y otro actual:

CRUZAR DOS TABLAS EN EXCEL USANDO SQL

Y ahora lo que queremos saber es, qué empleados están en la base de datos anterior y no en la actual (Bajas), y viceversa, es decir, (Altas). Estas dos consultas se puede realizar con SQL utilizando ADO:

Para las Altas:

obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'NUEVO EMPLEADO' AS ESTADO " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ANTERIOR$].[ID]) IS NULL)"

Para las Bajas:

obSQL = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA' " & _
"FROM [BBDD_ANTERIOR$] LEFT JOIN [BBDD_ACTUAL$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ACTUAL$].[ID]) IS NULL)"

Efectivamente, con estos códigos podemos extraer la información requerida. Pero imaginad que queremos obtener los movimientos que han sucedido entre “Secciones”, es decir qué empleados han dejado una sección y qué empleados han entrado en una nueva sección. Obviamente, los empleados son los mismos, lo único que cambia es la sección.

Para ello, solo debemos modificar levemente el código:

Altas en una sección:

obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'ALTA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ACTUAL$].[SECCION]) NOT LIKE [BBDD_ANTERIOR$].[SECCION] "

Bajas en una sección:

obSQL = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ANTERIOR$].[SECCION]) NOT LIKE [BBDD_ACTUAL$].[SECCION] "

Estos fragmentos de código solo son la consulta SQL específica. Para que funcione es necesario embeberla en entorno VBA. Os dejo aquí la primera de las cuatro consultas, no reproduciré el resto dado que ya las podéis ver en el archivo de descarga.

Sub ALTA()
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, fin As Integer, milibro As String
Application.ScreenUpdating = False
fin = Application.CountA(Sheets("MOVIMIENTOS").Range("A:A"))
'Borramos datos de consultas anteriores
If fin > 0 Then Sheets("MOVIMIENTOS").Range("A1:D" & fin).Clear
'indicamos los parámetros de la consulta SQL
obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'NUEVO EMPLEADO' AS ESTADO " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ANTERIOR$].[ID]) IS NULL)"
'Obtenemos el nombre del libro
milibro = ThisWorkbook.Name
'Creamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & milibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
Dataread.MoveFirst
'pasamos información a la hoja movimientos
With Worksheets("MOVIMIENTOS")
.Cells(2, 1).CopyFromRecordset Dataread
'mostramos encabezados
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
.Cells(1, i + 1) = dfecha
Next
End With
Loop
'ejecutamos el resto de consultas
Call BAJA
Call ALTA_SECCION
Call BAJA_SECCION
Set Dataread = Nothing
Set cnn = Nothing
End Sub

Como podéis observar, después de ejecutar la primera consulta, ejecutamos el resto de consultas en el mismo proceso.

El resultado de ejecutar el código es el siguiente:

CRUZAR DOS TABLAS EN EXCEL USANDO SQL_1

Y ya tenemos un resumen detallado de los nuevos empleados, las bajas producidas y los cambios de sección (anterior y actual).

Aunque es un proceso un poco largo, si se trata de consultas que siempre tendrán la misma estructura y periodicidad, es el método adecuado. Resulta también muy interesante además su uso conjunto con Access o archivos de texto (sería necesario realizar cambios en la programación).

Os recuerdo que es necesario marcar la referencia Microsoft ActiveX Data Object 2.8 Library (o la que corresponda) para que todos los componentes funcionen correctamente.

Para poder generar los nombres y confeccionar vuestras propias bases de datos os remito al siguiente post

Descarga el archivo de ejemplo pulsando en: CRUZAR DOS TABLAS EN EXCEL USANDO SQL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Hola a todos 🙂

Espero que os vaya muy bien!. En el post de hoy vamos a trabajar con ADO para dar formato de fecha a una cadena de texto.

El tema de las fechas aunque puede parecer sencillo, siempre es susceptible de complicarse. Y en muchas ocasiones el problema se debe al formato con el que llegan los datos que nos envían. En el post de hoy vamos a trabajar uno de esos formatos.

Vamos a ilustrarlo con un ejemplo, imaginad que abrís una petición en la red a favor del control de horarios y ruidos de los locales de ocio nocturno (¡todo un reto!). Y la aplicación que ha recogido los datos os envía un archivo con la siguiente información:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Efectivamente, los campos NOMBRE, PETICIÓN y FIRMA no tienen nada de particular, pero el campo FECHA DE FIRMA debería contar con un formato de fecha y si embargo nos llega como un texto. Esto es un problema, dado que a priori no vamos a poder trabajar fácilmente con este tipo de fechas y es más, necesitamos exportar algunas de las columnas del fichero a otra aplicación para generar otro tipo de informaciones.

Hemos elegido ADO para transferir la información de la hoja BASE a la hoja INFORMACIÓN y será en ese proceso en el que vamos a utilizar SQL para formatear las fechas y también controlar aquellas celdas que se encuentren vacías.

Para hacer el trabajo, os dejo esta macro que es capaz formatear solo las celdas que contienen la fecha:

Sub CONEXION_SQL_FECHAS()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, i As Long, MiLibro As String, Tit As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de hoja INFORMACION anteriores
Sheets("INFORMACION").Select
With Sheets("INFORMACION")
'Eliminamos datos de la consulTa SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:D" & Fin).ClearContents
'Realizamos consulta SQL, y componemos un string para crear los datos y darle formato de fecha
obSQL = "SELECT [BASE$].[NOMBRE], [BASE$].[PETICION], [BASE$].[FIRMA], " & _
"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA] " & _
"FROM [BASE$] "
MiLibro = ActiveWorkbook.Name
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pegamos datos y añadimos encabezados
Do Until Dataread.EOF
Dataread.MoveFirst
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
Loop
'Por seguridad formateamos la columna FECHA DE FIRMA a fecha
.Columns("D:D").NumberFormat = "m/d/yyyy"
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
Application.ScreenUpdating = True
End Sub

Como podéis observar,  usamos ADO para importar la información de una hoja a la otra, y en el proceso aprovechamos para realizar los cambios y controles necesarios. En concreto, en esta sentencia SQL:

"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA]

Donde controlamos que solo sean formateadas las celdas que contienen datos usando un IIF que valida si son nulas. Si no lo son, componemos una nueva cadena de texto creando la fecha y aplicamos la función CDate que nos devolverá una expresión tipo fecha, ¡Justo lo que queremos!.

Una vez ejecutada la macro, el resultado es el siguiente:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA1

Este es un ejemplo muy concreto donde los datos a formatear deben ser texto, de hecho, cuando no nos envían la información con formato de fecha, casi siempre viene así.

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 eso es todo, espero que os resulte de utilidad a la hora de trabajar con fechas en Excel 🙂

Descarga el archivo de ejemplo pulsando en: APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

 
¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

Hola a todos 🙂

Espero que todo vaya bien!. Llevo unos días con ganas de escribir este post, pero la verdad es que me habéis enviado bastantes consultas, y ya sabéis que lo primero es atender las dudas de los lectores y luego escribir las entradas del blog.

El post de hoy surge por una consulta en la que se me pedía una macro para extraer registros únicos de una hoja a otra. Así como para extraer los registros únicos dentro de la misma hoja podemos usar el filtro avanzado, y así lo publique en su momento: EXTRAER REGISTROS ÚNICOS CON FILTRO AVANZADO EN VBA, para pasarlo a otra hoja debemos utilizar otro tipo de código, de hecho podemos utilizar diferentes métodos.

Hoy veremos uno de ellos, utilizaremos ADO y una consulta SQL en la que incluyendo la palabra clave DISTINCT podremos extraer los registros únicos a otra hoja.

Vamos con el siguiente ejemplo, imaginad que tenemos nuestra hoja de Excel con una serie de registros duplicados:

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

Y queremos pasar a la hoja UNICOS todos los registros SIN duplicados. Para ello vamos utilizar el siguiente código:

Sub CONSULTA_SQL_UNICOS()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection, i As Integer, MiLibro As String
'Limpiamos hoja con los registros únicos
Eliminar = Application.CountA(Worksheets("UNICOS").Range("A:A"))
If Eliminar > 0 Then Worksheets("UNICOS").Range("A1:GG" & Eliminar).ClearContents
'realizamos consulta SQL incorporando la palabra clave Distinct
obSQL = "SELECT distinct * FROM [DATOS$] "
'Guardamos el nombre del libro activo
MiLibro = ActiveWorkbook.Name
'Realizamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
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 UNICOS
With Worksheets("UNICOS").Select
Worksheets("UNICOS").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("UNICOS").Cells(1, i + 1) = dfecha
Next
Loop
End Sub

Ya sabéis que para este tipo de método es necesario activar la referencia Microsoft ActiveX Data Object 2.8 Library en el editor de VBA:

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

Siguiendo con el código, lo realmente importante es que en la consulta SQL estamos utilizando la palabra clave DISTINCT, la cual elimina los registros que se encuentran duplicados. El resto del código es el que solemos utilizar en esta web para este tipo de consultas.

El resultado de aplicar la macro es el siguiente:

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT1

Como podéis ver, ya tenemos nuestros datos sin duplicados.

Y esto es todo, en próximos post, publicaré otra forma más sencilla de extraer registros únicos. Espero que os sea de utilidad, como siempre os dejo la macro:

Descarga el archivo de ejemplo pulsando en: EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

 

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

Hola a todos:

Hace unos días recibí una consulta acerca de cómo agrupar la información de varias hojas en una hoja específica del mismo libro.

Aunque tengo publicaciones con macros para agrupar información de varios libros,  el método utilizado fue mediante bucles (For – next). Para esta petición utilizaré otro método, trabajaré con ADO y realizaré una consulta de UNION para consolidar la información de las hojas seleccionadas.

Dado que el usuario tendrá siempre la misma estructura en la información de esas hojas y además serán tres, esta macro será perfecta para este trabajo. Con ADO conseguiremos mayor rapidez en la ejecución del código y, si cabe mayor claridad.

Veamos las tres hojas que vamos a consolidar, las llamaré TABLA1, TABLA2 y TABLA3 y la hoja que recibirá toda la información se llamará CONSOLIDADO:

La estructura sería esta:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION

Tal como podéis ver, cada una de las tablas tiene la misma estructura que la que muestro arriba. Un ID, Nombre, Estudios, Inglés, Vehículo, Provincia y Edad.

Antes de poner la macro, me gustaría volver a incidir en que es imprescindible que las tablas tengan las mismas columnas con los mismos nombres. Si esto no es así, la macro no funcionará.

Ahora sí, ya podéis pegar el siguiente código en un módulo estándar:

Sub CONSULTA_SQL_UNION()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection, i As Integer, MiLibro As String
'Limpiamos hoja que consolida, CONSOLIDADO
Eliminar = Application.CountA(Worksheets("CONSOLIDADO").Range("A:A"))
If Eliminar > 0 Then Worksheets("CONSOLIDADO").Range("A1:GG" & Eliminar).ClearContents
'realizamos consulta SQL de UNION y no tenemos en cuenta los registros nulos
obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"
'Guardamos el nombre del libro activo
MiLibro = ActiveWorkbook.Name
'Realizamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
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 CONSOLIDADO
With Worksheets("CONSOLIDADO").Select
Worksheets("CONSOLIDADO").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("CONSOLIDADO").Cells(1, i + 1) = dfecha
Next
Loop
End Sub

Antes de ejecutar el código debéis verificar que tenéis la referencia: Microsoft ActiveX Data Object 2.8 Library. En este archivo no es necesario que la marquéis (ya la he marcado yo), pero si pegáis el códido en un libro nuevo, sí que la tendréis que marcar.

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

El resultado de aplicar la macro es el siguiente:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION1

Y ya tenemos los registros de las hojas especificadas en la hoja CONSOLIDADO. En este código si existen duplicados, es decir dos filas o más idénticas, importará solo uno, y también si existen elementos nulos (null) no los tendrá en cuenta. Si queremos que tuviese en cuenta los duplicados, es decir, que los importase, debemos poner “ALL” después de UNION, de esta forma estaremos especificando que unimos todo (incluidos duplicados):

obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION ALL " & _ "SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION ALL " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"

Obviamente, sobre los nulos, no vamos a querer que se cuelen filas en blanco o con caracteres extraños, por eso resulta imprescindible incluir en la sentencia el famoso Not Is Null 🙂

Y esto ha sido todo, os dejo el archivo de ejemplo:

Descarga el archivo de ejemplo pulsando en: CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

 

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

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