Excel VBA : ループ処理で任意の数のテーブルをマージするSQLを作成

はじめに

Excel VBAを用いてSQL文を実行し、
シート上のテーブルを結合する方法について紹介します。
(未だ制限の多い関数となっており、さらなる改良が必要です。詳しい方がいらっしゃれば、是非ご教授ください。）

「master」と「tbl_one」「tbl_two」「tbl_three」をマージしています。

コードの細かい部分は以下をご参照ください。

• テーブルの部位を特定する
• 【VBA】ADOを使用してExcel表をDB操作する方法のまとめ（範囲指定、書き込みなど）
• VBAでSQLを使用する際にListObjects(テーブル)をFrom句に指定する方法
• 3つのテーブルをADODBでJOINするSQL記載方法

VBA

関数の準備

Public Function sql_master_left_join(ByVal sheetname As String, _
                                                            ByVal masterName As String, ByVal tblNames As Variant, _
                                                            ByVal leftcol As String, ByVal rightcol As String) As String
        '''
        ' 「マスタテーブルの名前」、「結合するテーブル名が格納された配列」、「結合につかうカラム名」を受けて、
        ' LEFT JOINするSQL文を作成
        '
        ' Parameters
        ' ------------
        ' sheetname: 各種テーブルが配置されたシートの名前（今回は同じシート上にテーブルがある想定）
        ' masterName: マスタとなるテーブルの名前（特になければ、結合するテーブル・カラムの値のユニークな値で作る？）
        ' tblNames: 結合するテーブルを格納した配列
        ' leftcol: 結合で使用するカラム名（マスタ側）
        ' rightcol: 結合で使用するカラム名（結合されるテーブル側）
        '
        ' Returns
        ' --------
        ' SQL文. String
        '''
        Dim strSQL As String ' 最終的に得られるSQL文
        Dim selectSQL As String 'ループ処理で作成するSQLのパーツ①
        Dim frombraSQL As String 'ループ処理で作成するSQLのパーツ②
        Dim frombodySQL As String 'ループ処理で作成するSQLのパーツ③
        
        Dim i As Integer
        i = 1
        
        ''（１）初期値を設定(ループ処理で更新していく)
        '' SELECTの部分
        selectSQL = "SELECT " & masterName & ".*, "
        
        '' 複数くっつける際に使うFROMの前のbra(cket)
        frombraSQL = "["
        
        '' FROMの中身
        frombodySQL = Worksheets(sheetname).ListObjects(masterName).Range.Address(False, False) & "] as " & masterName & " "
        
        '' （２）ループ処理で初期値を更新していく
        Dim tblName As Variant
        For Each tblName In tblNames
            '' テーブルを格納した配列における最初、真ん中、最後で処理を分岐
            
            '' 最初
            If i = 1 Then
                ' SELECT
                selectSQL = selectSQL & tblName & "." & rightcol & ", "
                
                ' FROMの前のbracket
                frombraSQL = "(" & frombraSQL
                
                ' FROMの中身
                frombodySQL = frombodySQL & "LEFT JOIN "  '今回はLEFT JOIN
                frombodySQL = frombodySQL & "[" & Worksheets(sheetname).ListObjects(tblName).Range.Address(False, False)
                frombodySQL = frombodySQL & "] as " & tblName & " "
                frombodySQL = frombodySQL & "ON "
                frombodySQL = frombodySQL & masterName & "." & leftcol & " = " & tblName & "." & leftcol
                
            '' 真ん中
            ElseIf i <> UBound(tblNames) - LBound(tblNames) + 1 Then
                ' SELECT
                selectSQL = selectSQL & tblName & "." & rightcol & ", "
                
                ' FROMの前のbracket
                frombraSQL = "(" & frombraSQL
                
                ' FROMの中身
                frombodySQL = frombodySQL & ")LEFT JOIN "
                frombodySQL = frombodySQL & "[" & Worksheets(sheetname).ListObjects(tblName).Range.Address(False, False)
                frombodySQL = frombodySQL & "] as " & tblName & " "
                frombodySQL = frombodySQL & "ON "
                frombodySQL = frombodySQL & masterName & "." & leftcol & " = " & tblName & "." & leftcol
            
            '' 最後
            Else
                'SELECT
                selectSQL = selectSQL & tblName & "." & rightcol & " "
                
                ' FROMの前のbracket
                frombraSQL = "(" & frombraSQL
                
                ' FROMの中身
                frombodySQL = frombodySQL & ")LEFT JOIN "
                frombodySQL = frombodySQL & "[" & Worksheets(sheetname).ListObjects(tblName).Range.Address(False, False)
                frombodySQL = frombodySQL & "] as " & tblName & " "
                frombodySQL = frombodySQL & "ON "
                frombodySQL = frombodySQL & masterName & "." & leftcol & " = " & tblName & "." & leftcol & ")"
                
            End If
            i = i + 1
        Next
        
        '' 結合
        strSQL = ""
        strSQL = strSQL & selectSQL
        strSQL = strSQL & "FROM "
        strSQL = strSQL & frombraSQL
        strSQL = strSQL & frombodySQL
                                    
        sql_master_left_join = strSQL
End Function

実行

Sub run_left_join()
    '' ■sql_master_left_joinを実行し、「master」「tbl_one」「tbl_two」「tbl_three」をマージして出力
    
    ''（１）SQL文の作成
    Dim strSQL As String
    Dim masterName As String
    Dim tblNames() As Variant
    Dim sheetname As String
    Dim leftcol As String
    Dim rightcol As String
    
    sheetname = "Sheet1"
    masterName = "master"
    tblNames = Array("tbl_one", "tbl_two", "tbl_three")
    leftcol = "名前"
    rightcol = "体重"
    
    strSQL = sql_master_left_join(sheetname, masterName, tblNames, leftcol, rightcol)
    
    ''（２）接続
    Dim objCn As ADODB.Connection
    Set objCn = New ADODB.Connection
    With objCn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0 Macro; data sorce= "
        .Open ThisWorkbook.FullName '現在のワークシート上
    End With

    ''（３）読み込み
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    objRS.Open strSQL, objCn, adOpenStatic
    
    ''（４）出力
    With Worksheets(sheetname)
        .Range(.Cells(10, 2), .Cells(10 + .ListObjects(masterName).ListRows.Count - 1, 2 + UBound(tblNames) - LBound(tblNames) + 1)) = objRS.GetRows()
    End With
    
    ''（５）閉じる
    objRS.Close
    Set objRS = Nothing
    
    objCn.Close
    Set objCn = Nothing
End Sub

さいごに

まだまだ実用に耐えない関数ですが、皆さんのご意見いただければと思い投稿させていただきました。