Excel VBA を使用して SQL クエリを実行する

Excel の強力な機能の 1つは、データ処理とデータの視覚化です。データに関しては、構造化照会言語（SQL）は、データベースからデータをフェッチするために使用される主要なプログラミング言語であるため、非常に便利です。

通常、SQL プログラムには、クエリの結果を表示できる組み込みのデータ出力インターフェイスがあります。ただし、これらのデータ出力インターフェイスのいずれも、Excel が提供できる柔軟性と機能のレベルに匹敵するものはありません。

このチュートリアルでは、SQL のデータ抽出機能と Excel のデータ処理能力を組み合わせます。このスキームは通常、データセンターが設立されている業界で使用されており、迅速で簡単なデータ抽出の必要性は避けられません。

以下のコードは、リモートデータソースを介した接続を可能にする ADO オブジェクトを使用して Excel を SQL サーバーに接続します。このオブジェクトを使用すると、VBA はデータベースにアクセスして操作できます。

ただし、ADODB オブジェクトには、VBA で使用されるデフォルトのライブラリが自動的に付属していません。ADODB オブジェクトを有効にするには、References ツールバーで有効にする必要があります。

これを行うには、以下の手順に従ってください。

これですべての設定が完了しました。

以下のコードブロックには 8つのパラメータがあります。以下の表を参照してください。

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   |