使用 Excel VBA 執行 SQL 查詢

Excel 的強大功能之一是資料處理和資料視覺化。在資料方面，結構化查詢語言 (SQL) 非常有用，因為它是用於從資料庫中獲取資料的主要程式語言。

通常，SQL 程式具有內建的資料輸出介面，你可以在其中檢視查詢結果。但是，這些資料輸出介面中沒有一個能夠與 Excel 可以提供的靈活性和功能水平相匹配。

在本教程中，我們將結合 SQL 的資料提取能力和 Excel 的資料處理能力。該方案通常用於建立資料中心的行業，並且不可避免地需要快速簡便的資料提取。

下面的程式碼使用允許通過遠端資料來源連線的 ADO 物件將 Excel 與 SQL 伺服器連線起來。有了這個物件，VBA 可以訪問和運算元據庫。

但是，ADODB 物件不會隨 VBA 使用的預設庫自動提供。要啟用 ADODB 物件，我們需要在 References 工具欄中啟用它。

為此，請按照以下步驟操作。

現在一切就緒。

下面的程式碼塊有八個引數；請參閱下表：

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

現在，假設我們有以下關於你的資料庫和資料庫連線的資訊：

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

database1、table1 內容：

|   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 查詢：

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

要執行此 SQL 查詢並輸出 Excel 工作簿第 1 列和第 1 行的 Sheet1 上的條目，請使用以下程式碼塊：

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 輸出：

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