Use Excel VBA para ejecutar consultas SQL

Una de las capacidades sólidas de Excel es el procesamiento de datos y la visualización de datos. Cuando se trata de datos, el lenguaje de consulta estructurado (SQL) es muy útil, ya que es el principal lenguaje de programación utilizado para obtener datos de la base de datos.

Normalmente, los programas SQL tienen interfaces de salida de datos integradas donde puede ver los resultados de sus consultas. Sin embargo, ninguna de estas interfaces de salida de datos igualará el nivel de flexibilidad y funcionalidad que podría ofrecer Excel.

En este tutorial, combinaremos la capacidad de extracción de datos de SQL y la destreza de procesamiento de datos de Excel. Este esquema generalmente se usa en industrias donde se establecen centros de datos, y la necesidad de una extracción de datos rápida y fácil es inevitable.

El siguiente código conecta Excel con el servidor SQL mediante un objeto ADO que permite la conexión a través de una fuente de datos remota. Con este objeto, VBA puede acceder y manipular la base de datos.

Sin embargo, el objeto ADODB no viene automáticamente con la biblioteca predeterminada utilizada por VBA. Para habilitar el objeto ADODB, debemos habilitarlo en la barra de herramientas Referencias.

Para hacer esto, siga los pasos a continuación.

Ya está todo listo.

El bloque de código a continuación tiene ocho parámetros; Consulte la tabla de abajo:

Sub getData(Sql As String, nRow As Integer, nCol As Integer, sheetDes As String, usrID as String, pssWrd as String, sidStr as string, hst as String)

Dim Connct As ADODB.Connection
Dim RcrdSet As ADODB.Recordset
Dim RcrdVal As Variant
Dim reference_x As Integer
Dim reference_y As Integer
Dim records_count As Integer

    
'CONNECTION STRING
connection_string = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hst)(PORT = 1521))(CONNECT_DATA = (SID = sidStr)))"
user_id = usrID
pass_word = pssWrd
    
'EXCEL PRINT LOCATION
reference_col = nCol
reference_row = nRow
    
' start ------ default connection setup & SQL execution code (Do not Edit entries)
cs = "Provider=OraOLEDB.Oracle;Data Source=" & connection_string & ";User Id=" & user_id & ";Password=" & pass_word & ";"
Set Connct = New ADODB.Connection
    Set RcrdSet = New ADODB.Recordset
    With Connct
        .CursorLocation = adUseClient
        .Open cs
        .CommandTimeout = 0
    RcrdSet.CursorType = asOpenForwardOnly
    RcrdSet.Open (Sql), Connct
    records_count = CInt(RcrdSet.RecordCount)
    ' end ------ default connection setup & SQL execution code (Do not Edit entries)

    ' start ------ default data print setup & close procedures (Do not Edit)
    If records_count > 0 Then
        RcrdSet.MoveFirst
        For x = 0 To RcrdSet.Fields.Count - 1
            ThisWorkbook.Sheets(sheetDes).Cells(reference_row, x + reference_col) = RcrdSet.Fields(x).Name
        Next
        ThisWorkbook.Sheets(sheetDes).Cells(reference_row + 1, reference_col).CopyFromRecordset RcrdSet
    End If
    End With
    RcrdSet.Close
    Connct.Close
    Set RcrdSet = Nothing
    Set Connct = Nothing
    'end ------ default data print setup & close procedures (Do not Edit)

End Sub

Ahora, supongamos que tenemos la siguiente información sobre su base de datos y la conexión de la base de datos:

Host: database1
SID: database1@server.com
Username: username123
Password: pw123

database1, table1 contenido:

|   Names    |    Age   |    Gender    |    Sports    |
|------------|----------|--------------|--------------|
|   Juan     |    17    |    Male      |     Chess    |
|   Pedro    |    19    |    Male      |    Badminton |
|   Maria    |    25    |   Female     |  Volleyball  |
|  Rodolfo   |    29    |    Male      | Basketball   |
|   Cathy    |    18    |   Female     |    Chess     |
|  Michelle  |    21    |   Female     |   Swimming   |
|     Glen   |    24    |    Male      | Billiards    |

Consulta SQL:

Select Names, Gender, Sports
from table1@database1
where Age <= 25

Para ejecutar esta consulta SQL y generar las entradas en Sheet1 en la columna 1 y la fila 1 de un libro de Excel, use el bloque de código a continuación:

Sub getData(Sql As String, nRow As Integer, nCol As Integer, sheetDes As String, usrID As String, pssWrd As String, sidStr As String, hst As String)

Dim Connct As ADODB.Connection
Dim RcrdSet As ADODB.Recordset
Dim RcrdVal As Variant
Dim reference_x As Integer
Dim reference_y As Integer
Dim records_count As Integer

    
'CONNECTION STRING
connection_string = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hst)(PORT = 1521))(CONNECT_DATA = (SID = sidStr)))"
user_id = usrID
pass_word = pssWrd
    
'EXCEL PRINT LOCATION
reference_col = nCol
reference_row = nRow
    
' start ------ default connection setup & SQL execution code (Do not Edit entries)
cs = "Provider=OraOLEDB.Oracle;Data Source=" & connection_string & ";User Id=" & user_id & ";Password=" & pass_word & ";"
Set Connct = New ADODB.Connection
    Set RcrdSet = New ADODB.Recordset
    With Connct
        .CursorLocation = adUseClient
        .Open cs
        .CommandTimeout = 0
    RcrdSet.CursorType = asOpenForwardOnly
    RcrdSet.Open (Sql), Connct
    records_count = CInt(RcrdSet.RecordCount)
    ' end ------ default connection setup & SQL execution code (Do not Edit entries)

    ' start ------ default data print setup & close procedures (Do not Edit)
    If records_count > 0 Then
        RcrdSet.MoveFirst
        For x = 0 To RcrdSet.Fields.Count - 1
            ThisWorkbook.Sheets(sheetDes).Cells(reference_row, x + reference_col) = RcrdSet.Fields(x).Name
        Next
        ThisWorkbook.Sheets(sheetDes).Cells(reference_row + 1, reference_col).CopyFromRecordset RcrdSet
    End If
    End With
    RcrdSet.Close
    Connct.Close
    Set RcrdSet = Nothing
    Set Connct = Nothing
    'end ------ default data print setup & close procedures (Do not Edit)

End Sub

Sub testSQLVBAConnection()

Dim sqlStr As String

sqlStr = "Select Names, Gender, Sports "
sqlStr = sqlStr & " from table1@database1 "
sqlStr = sqlStr & " where Age <= 25 "

Call getData(sqlStr, 1, 1, "Sheet1", "username123", "pw123", "database1@server.com", "database1")
End Sub

Salida testSQLVBAConnection:

|   Names    |    Gender    |    Sports    |
|------------|--------------|--------------|
|   Juan     |    Male      |     Chess    |
|   Pedro    |    Male      |    Badminton |
|   Maria    |   Female     |  Volleyball  |
|   Cathy    |   Female     |    Chess     |
|  Michelle  |   Female     |   Swimming   |