SQL EN VBA CON ADO. UTILIZAR SENTENCIA JOIN Y AÑADIR VALORES AL OPERADOR IN

Hola a todos!:

Hoy toca publicar algo de VBA y me ha parecido interesante hacerlo con ADO (Activex Data Object).

Existen muchos ejemplos en esta web sobre ADO pero hoy me gustaría hacerlo para tratar la sentencia JOIN y el operador IN.

Para el caso de JOIN realizaré el ejemplo con INNER JOIN y con el operador IN automatizaré la forma de incluir varios valores automáticamente.

Por ejemplo, imaginad que tenemos esta base de datos:

Y queremos programar una serie de consultas, por ejemplo que únicamente se tengan en cuenta las secciones que se especifiquen en una columna y las edades de otra columna:

La idea es que cuando pulsemos el botón se muestre una ventana de diálogo para seleccionar el archivo denominado «BASE DATOS». y el resultado se nos devuelva en la misma hoja de los criterios:

Este sería el resultado:

Y como podéis comprobar, los datos devueltos son los que hemos indicado en la consulta.

Por lo tanto, solo queda que os muestre el código que he programado:

Option Explicit
Sub CONSULTAS_SQL_ADO()
    'Definimos variables
    Dim Dataread    As ADODB.Recordset, cnn As ADODB.Connection, Titulos As String
    Dim i           As Double, fin As Double, narchivos As Variant, obSQL As String
    Dim MiLibro     As String, MisCasos As String, MiCadena As String, sCadena As String
    'Seleccionamos archivos
    Application.ScreenUpdating = False
    narchivos = Application.GetOpenFilename(filefilter:="Excel (*.xls*),*.xls", _
                Title:="SELECCIONAR ARCHIVO", MultiSelect:=False)
    'Si no hay datos salimos del proceso
    If narchivos = False Then Exit Sub
    'Borramos datos en la hoja en RESULTADOS
    With Sheets("CRITERIOS")
    fin = Application.CountA(.Range("F:F"))
    If fin > 1 Then .Range("F2:K" & fin).ClearContents
    'Componemos path a CRITERIOS
    MiLibro = ThisWorkbook.FullName
    MisCasos = "[" & MiLibro & "].[CRITERIOS$]"
    'Crear string con las edades
      fin = Application.CountA(.Range("B:B"))
        For i = 2 To fin
            MiCadena = MiCadena & ", " & .Cells(i, 2)
        Next i
        sCadena = Mid(MiCadena, 2, Len(MiCadena))
    'Definimos consulta
    obSQL = " Select [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO],[Hoja1$].[SECCION], [Hoja1$].[EDAD], [Hoja1$].[2 º IDIOMA], [Hoja1$].[ESTUDIOS] " & _
    "FROM [Hoja1$] INNER JOIN " & MisCasos & " A On [Hoja1$].[SECCION]= A.[SECCION] WHERE [Hoja1$].[EDAD] in (" & sCadena & ")"
    'Iniciamos la conexión con la base de datos
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Connectionstring = "DATA SOURCE=" & narchivos
        .Properties("Extended Properties") = "Excel 8.0"
        .Open
    End With
    'Grabamos los datos
    Set Dataread = New ADODB.Recordset
    With Dataread
        .Source = obSQL
        .ActiveConnection = cnn
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open
    End With
        .Select
        'Pasamos datos a la hoja
        .Cells(2, 6).CopyFromRecordset Dataread
        'Incluimos encabezados
        For i = 0 To Dataread.Fields.Count - 1
            If IsDate(Dataread.Fields(i).Name) Then
                Titulos = CDate(Dataread.Fields(i).Name)
            Else
                Titulos = Dataread.Fields(i).Name
            End If
            .Cells(1, i + 5 + 1) = Titulos
        Next i
    End With
    'Liberamos variables
    Dataread.Close: Set Dataread = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Si nos centramos en la cláusula SQL:

obSQL = " Select [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO],[Hoja1$].[SECCION], [Hoja1$].[EDAD], [Hoja1$].[2 º IDIOMA], [Hoja1$].[ESTUDIOS] " & _
    "FROM [Hoja1$] INNER JOIN " & MisCasos & " A On [Hoja1$].[SECCION]= A.[SECCION] WHERE [Hoja1$].[EDAD] in (" & sCadena & ")"

Podemos ver en primer lugar la sentencia JOIN:

FROM [Hoja1$] INNER JOIN " & MisCasos & " A On [Hoja1$].[SECCION]= A.[SECCION]

Donde en la variable «MisCasos» se guarda la ruta al archivo actual desde el que estoy realizando la consulta. La sintaxis es la habitual del Inner Join donde únicamente mostraremos aquellas secciones coincidentes en ambas tablas. Obviamente podríamos utilizar Left Join o Right Join en caso de requerirlo el ejercicio.

En cuanto al operador IN:

WHERE [Hoja1$].[EDAD] in (" & sCadena & ")"

Para componer el string de edades utilizamos un loop:

For i = 2 To fin
            MiCadena = MiCadena & ", " & .Cells(i, 2)
        Next i
        sCadena = Mid(MiCadena, 2, Len(MiCadena))

Y el resultado de lo que queda almacenado en sCadena es:

WHERE [Hoja1$].[EDAD] in ( 51, 23, 62, 25, 35)

Y así es cómo podemos incluir en nuestras consultas SQL en Excel estos elementos y facilitarnos así nuestras tareas.

Por supuesto, debéis activar las referencias a ADO:

Espero que os haya resultado de interés 🙂

Puedes descargar aquí el archivo:

y también os dejo la base de datos para hacer pruebas:

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



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