Verwenden von Excel VBA zum Ausführen einer SQL-Abfrage

Eine der starken Fähigkeiten von Excel ist die Datenverarbeitung und Datenvisualisierung. Wenn es um Daten geht, ist die Structured Query Language (SQL) sehr nützlich, da sie die wichtigste Programmiersprache ist, die zum Abrufen von Daten aus der Datenbank verwendet wird.

Normalerweise haben SQL-Programme eingebaute Datenausgabeschnittstellen, wo Sie die Ergebnisse Ihrer Abfragen anzeigen können. Keine dieser Datenausgabeschnittstellen erreicht jedoch das Maß an Flexibilität und Funktionalität, das Excel bieten könnte.

In diesem Lernprogramm kombinieren wir die Datenextraktionsfunktion von SQL und die Datenverarbeitungsfähigkeit von Excel. Dieses Schema wird typischerweise in Branchen verwendet, in denen Rechenzentren eingerichtet sind und die Notwendigkeit einer schnellen und einfachen Datenextraktion unvermeidlich ist.

Der folgende Code verbindet Excel mit dem SQL-Server unter Verwendung eines ADO-Objekts, das eine Verbindung über eine entfernte Datenquelle ermöglicht. Mit diesem Objekt kann VBA auf die Datenbank zugreifen und sie manipulieren.

Das Objekt ADODB wird jedoch nicht automatisch mit der von VBA verwendeten Standardbibliothek geliefert. Um das Objekt ADODB zu aktivieren, müssen wir es in der Symbolleiste Referenzen aktivieren.

Führen Sie dazu bitte die folgenden Schritte aus.

Sie sind jetzt fertig.

Der folgende Codeblock hat acht Parameter; siehe folgende Tabelle:

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

Angenommen, wir haben die folgenden Informationen über Ihre Datenbank und die Datenbankverbindung:

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

database1, table1 Inhalt:

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

SQL-Abfrage:

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

Um diese SQL-Abfrage auszuführen und die Einträge auf Sheet1 in Spalte 1 und Zeile 1 einer Excel-Arbeitsmappe auszugeben, verwenden Sie den folgenden Codeblock:

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

testSQLVBAConnection Ausgabe:

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