Consulta SQL en Excel mediante Microsoft ActiveX Data Objects (ADO)

En Excel podemos tratar una hoja como una tabla de datos y crear consultas SQL mediante Microsoft ActiveX Data Objects (ADO). Aunque presenta ciertas limitaciones, puede resultar de utilidad en algunas ocasiones:

Evitamos conectar Excel con Access.
Evitamos crear un tabla dinámica intermedia.

Ejemplo

1. Descargamos el libro Tablas Neptuno.
2. Creamos una hoja de destino, que nombramos como Destination, donde irán los resultados.
3. Abrimos el editor de Visual Basic y en el menú de Herramientas clic en referencias añadimos: Microsoft ActiveX Data Objects 6.0 Library.

4. Insertamos un módulo en el que añadimos el siguiente código.

Sub Excel_QueryTable()

    Sheets("Destination").Cells.ClearContents
    
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    
    Dim qt As QueryTable
    
    ' Cadena de conexión
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _
        ";Extended Properties=Excel 8.0;Persist Security Info=False"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open

    ' Consulta en SQL
    SQL = "SELECT [Ciudad], [País] FROM [Clientes$]" & _
          "GROUP BY [Ciudad], [País]"    
    
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open

    ' Hoja de destino
    Set qt = Sheets("Destination").QueryTables.Add(Connection:=oRS, _
    Destination:=Sheets("Destination").Range("A1"))
    
    qt.Refresh       
    
    If oRS.State <> adStateClosed Then
    oRS.Close
    End If
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

5. Ejecutamos la subrutina
6. Guardamos el fichero como *.xlsm si queremos conservar el código.

Resultado

El resultado en la hoja de destino serán 70 registros con sus encabezados de columna.

Notas

• Es necesario especificar el nombre de las hojas entre corchetes y con el símbolo dolar al final de la misma: [Clientes$]
• A menos que la hoja activa sea la hoja de destino, es necesarios especificar explícitamente la misma:

   Set qt = Sheets("Destination").QueryTables.Add(Connection:=oRS, _
      Destination:=Sheets("Destination").Range("A1"))

• Empleamos una QueryTable en lugar de copyfromrecordset para obtener los encabezados de las columnas. Si no, emplearíamos en lugar de qt:

Sheets("Destination").Range("A1").CopyFromRecordset oRS

Referencias

• Cómo usar ADO con datos de Excel desde Visual Basic o desde VBA