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:

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

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.



¡¡Muchas gracias!!