【ExcelVBA】閉じた3つのExcelファイルにある表をLeft Outer Joinを使って結合しデータを取得するには

この記事では、閉じた3つのExcelファイルにある表をLeft Outer Joinを使って結合しデータを取得する方法についてご説明します。

ちなみに、表が全て同一シートに存在している場合はこちらの記事を参考にしてください。

9月 7, 2022【ExcelVBA】Left Outer Joinを使ってExcelの3つの表を結合しデータを取得するには

表が全て別のシートずつに存在している場合はこちらの記事を参考にしてください。

1月 16, 2023【ExcelVBA】Left Outer Joinを使ってExcelの3つのシートの表を結合しデータを取得するには

すぐにソースコードを見たい場合はこちらをクリック

【動画】閉じた3つのExcelファイルにある表をLeft Outer Joinを使って結合しデータを取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

まずは結合したい表が存在する閉じているexcelファイルを、結合結果を出力したいexcelファイルに取り込みます。(閉じているexcelファイルごとに1シートずつ作成)

取り込みが終わったら、SQLのLeft Outer Joinを使って各表を結合させて、結合結果を出力したいexcelファイルに出力しています。

今回は表「社員情報」の所属部署IDと表「所属部署」のIDを結合させ、表「社員情報」の役職IDと表「役職」のIDを結合させてシートに出力しています。

マクロ作成の流れ

STEP.1

表が存在する閉じているexcelファイル名を全て取得する

表が存在する閉じているexcelファイル名を全て取得します。

STEP.2

STEP.1で取得したexcelファイル名を、ファイル名で並び替えてrecordsetに保存する

STEP.1で取得したexcelファイル名を、ファイル名で並び替えてrecordsetに保存します。

STEP.3

excelのファイルの表を、結合結果を出力したいexcelファイルに取り込む

excelのファイルの表を、結合結果を出力したいexcelファイルに取り込みます。
取り込んだら、新たにシートを作成してそのデータをシートに出力します。
なお、今回はシート名はexcelのファイル名と同じにします。

STEP.4

ADOを使用して結合結果を出力したいexcelファイルに接続する

ADOを使用して結合結果を出力したいexcelファイルに接続します。

STEP.5

取り込んだデータが存在する表を結合させるためのLeft Outer JoinのSQL文を用意する

取り込んだデータが存在する表を結合させるためのLeft Outer JoinのSQL文を用意します。
なお、Left Outer Joinには3回テーブルの指定を行いますが、STEP.2で並び替えたファイル名と同じ順番に合わせて指定します。

STEP.6

OpenメソッドにSTEP.5のSQL文を指定して実行する

OpenメソッドにSTEP.5のSQL文を指定して実行します。

STEP.7

STEP.6で実行したSQL文の結果をシートに出力する

STEP.6で実行したSQL文の結果をシートに出力します。

目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

結合させた表を出力させるシートがあるexcelファイル

結合させたい表が存在するexcelファイルを黄色のセルに入力します。

結合させた表を出力させるシートです。

結合したい表が存在する閉じているexcelファイル

ファイル「01_社員情報.xlsx」

ファイル「02_所属部署.xlsx」

ファイル「03_役職.xlsx」

以上の3つのファイルのうち、結合させる元のexcelファイルの名前が3つのファイルの中で昇順の一番先頭になるように名前を付ける必要があります。

今回のサンプルでは結合させたい元の表が「社員情報」なので、「社員情報」の表が存在するexcelファイルの先頭に「01」を付けています(01_社員情報.xlsx)

続けて表「所属部署」があるexcelに「02」を(02_所属部署.xlsx)、表「役職」があるexcelに「03」を(03_役職.xlsx)、と名前を付けています。

適当な順番でexcelファイルを取り込むとLeft Outer Joinで想定通りに結合させることができなくなるので、必ず昇順となるようファイル名を付けておきます。(どの表が元でどの表と結合させるのかが分からなくなる)

この名前の順番でソート処理を行います(ファイル名で昇順に取り込ませるため)。

今回のサンプルでは下の順番でexcelファイルを取り込むことを想定しています。

01_社員情報.xlsx
02_所属部署.xlsx
03_役職.xlsx

ソート処理については「注目すべきコード②」の説明をご覧ください。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_dataImport_Click()

    Dim filePath            As String               'データが入力されているExcelファイルの格納先
    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数
    Dim conn                As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim aryRs               As ADODB.Recordset      'レコードセット用変数(配列のソート用)
    Dim exdRs               As ADODB.Recordset      'レコードセット用変数(excelの表データ用)
    Dim f                   As Object               'ファイル
    Dim pstDdSheetNM        As String               'データを貼り付ける先のシート名
    Dim excelFile           As String               'excelのファイル名
    Dim ws                  As Worksheet            'Worksheet用変数
    Dim shtExistFlg         As Boolean              'シート存在確認フラグ
    Dim sqlStr              As String               'SQL文用変数
    Dim cnt                 As Integer              'カウンタ
    Dim tblRangAry()        As String               '表の範囲用配列
    Dim excelFlLstColPos    As String               'excelファイルにある表の最後列の位置
    Dim rng                 As Range                'Rangeオブジェクト格納用変数
    
    'データが入力されているExcelファイルの格納先を取得する
    filePath = Worksheets("top").Range("folderNM").Value
    
    'FileSystemObjectのインスタンスを生成する
    Set fso = New FileSystemObject
    
    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection
    
    'Recordsetオブジェクトのインスタンスを生成する(配列のソート用)
    Set aryRs = New ADODB.Recordset
    
    'Recordsetオブジェクトのインスタンスを生成する(excelの表データ用)
    Set exdRs = New ADODB.Recordset
        
    'クライアントサイドカーソルに変更
    conn.CursorLocation = adUseClient
        
    'excelのファイル用のフィールドを追加する(フィールド名、フィールドの型、フィールドのサイズ)
    aryRs.Fields.Append "ExcelFile", adVarChar, 1000
    
    'Recordsetをオープンする
    aryRs.Open
        
    'フォルダ配下にあるデータが入力されているExcelファイルの数分ループさせる
    For Each f In fso.GetFolder(Worksheets("top").Range("folderNM").Value).files
    
        '新しいレコードを作成する
        aryRs.AddNew
        
        '取得したexcelのファイル名を追加する
        aryRs.Fields(0) = f
        
        '追加したデータ(ファイル名)を保存する
        aryRs.Update
        
    Next f
    
    'excelのファイルで昇順でソートする
    aryRs.Sort = "ExcelFile ASC"

    'Excelファイルの数分ループさせる
    Do Until aryRs.EOF
        
        'データを貼り付ける先のシート名を取得する
        pstDdSheetNM = Replace(Split(aryRs.Fields(0).Value, ".")(0), filePath & "\", "")
        
        'パスを除いたexcelファイル名を取得する
        excelFile = pstDdSheetNM & "." & Split(aryRs.Fields(0).Value, ".")(1)
        
        'データを貼り付ける先のシート有無のチェック
        For Each ws In Worksheets
    
            If ws.Name = pstDdSheetNM Then
    
                'データを貼り付ける先のシート名が存在している場合
    
                '変数「shtExistFlg」にTrueを設定する
                shtExistFlg = True
    
            End If
    
        Next ws
    
        If shtExistFlg = False Then
    
            'データを貼り付ける先のシートが存在しない場合
    
            'データを貼り付ける先のシートを新規作成する
            Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                           .Name = pstDdSheetNM
                           
            'シートを作成したのでフラグをfalseで初期化する
            shtExistFlg = False
                               
        Else
    
            'データを貼り付ける先のシートが存在する場合
    
            'データを貼り付ける先のシートのセル全てをクリアする
            Worksheets(pstDdSheetNM).Cells.Clear
    
        End If
        
        With conn
        
            '接続情報の取得(取り込みたいExcelファイルに接続する)
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source = " & aryRs.Fields(0).Value & _
            ";Extended Properties =Excel 12.0;"
            
            'データソースへの接続を開く
            .Open
            
        End With
            
        'Recordsetを開く(表の件数と列数取得)
        exdRs.Open " [" & "work$" & "]", conn, adOpenStatic
        
        '配列の最後の次元の要素数を変更する
        '→今回は要素数を1つ拡張する
        ReDim Preserve tblRangAry(cnt)
        
        'excelファイルにある表の最後列の位置
        excelFlLstColPos = Split(Sheets("work").Cells(1, exdRs.Fields.Count).Address, "$")(1)
        
        'excelの表の範囲を取得する
        tblRangAry(cnt) = "[" & pstDdSheetNM & "$A1:" & excelFlLstColPos & exdRs.RecordCount + 1 & "]"
                
        With Worksheets(pstDdSheetNM)
            
            '表の範囲を取得する
            Set rng = .Range(.Cells(1, 1), .Cells(exdRs.RecordCount + 1, exdRs.Fields.Count))
        
            'データを貼り付ける先のシートにデータを貼り付ける
            rng.Formula = "=if(ISBLANK('" & filePath & "\[" & excelFile & "]work'!A1), """",'" & filePath & "\[" & excelFile & "]work'!A1)"
                    
            '取得先のExcelファイルを参照している計算式がセルに入力されているので、値そのものを再入力する
            rng.Copy: rng.PasteSpecial Paste:=xlPasteValues
        
        End With
        
        'recordsetを閉じる
        exdRs.Close
        
        'Connectionを切断する
        conn.Close
    
        '次のレコードに移動する
        aryRs.MoveNext
        
        cnt = cnt + 1
        
    Loop
    
    With conn

        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"

        'データソースへの接続を開く
        .Open

    End With
    
    '左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
    sqlStr = "select"
    sqlStr = sqlStr & "  a.項番 as 項番"
    sqlStr = sqlStr & " ,a.名前 as 名前"
    sqlStr = sqlStr & " ,a.社員番号 as 社員番号"
    sqlStr = sqlStr & " ,a.年齢 as 年齢"
    sqlStr = sqlStr & " ,a.出身 as 出身"
    sqlStr = sqlStr & " ,a.入社年 as 入社年"
    sqlStr = sqlStr & " ,b.名称 as 所属部署ID"
    sqlStr = sqlStr & " ,c.名称 as 役職ID"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " ( "
    sqlStr = sqlStr & " " & tblRangAry(0) & " as a"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & tblRangAry(1) & " as b"
    sqlStr = sqlStr & " ON  a.所属部署ID = b.ID"
    sqlStr = sqlStr & " ) "
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & tblRangAry(2) & " as c"
    sqlStr = sqlStr & " ON  a.役職ID = c.ID"
    sqlStr = sqlStr & " )"

    'Recordsetを開く
    exdRs.Open sqlStr, conn, adOpenStatic

    'データを出力するセルをクリアする
    Worksheets("work").Cells.Clear

    '表の列数分だけ処理をループする
    For cnt = 0 To exdRs.Fields.Count - 1

        '表の列名をセルに出力する
        Worksheets("work").Cells(1, 1 + cnt).Value = exdRs.Fields.Item(cnt).Name

    Next cnt

    '先ほど貼り付けた列名の下の行にデータを貼り付ける
    Worksheets("work").Cells(1, 1).Offset(1, 0).CopyFromRecordset exdRs

    'recordsetを閉じる
    aryRs.Close
    exdRs.Close
    
    'Connectionを切断する
    conn.Close

    '後処理
    Set fso = Nothing
    Set f = Nothing
    Set conn = Nothing
    Set aryRs = Nothing
    Set exdRs = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは25行目から34行目です。

    'FileSystemObjectのインスタンスを生成する
    Set fso = New FileSystemObject
    
    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection
    
    'Recordsetオブジェクトのインスタンスを生成する(配列のソート用)
    Set aryRs = New ADODB.Recordset
    
    'Recordsetオブジェクトのインスタンスを生成する(excelの表データ用)
    Set exdRs = New ADODB.Recordset

以上のコードは、各インスタンスを生成するコードです。

25行目では、excelファイルに関する情報を取得するために必要なFileSystemObjectオブジェクトのインスタンスを生成しています。

28行目では、マクロがExcelファイルに接続するのに必要なConnectionオブジェクトのインスタンスを生成しています。

31行目では、配列のソートするのに必要なrecordsetオブジェクトのインスタンスを生成しています。

34行目では、excelの表のデータを取得するために必要なrecordsetオブジェクトのインスタンスを生成しています。

注目すべきコード②

次に見て頂きたいのは40行目から60行目です。

    'excelのファイル用のフィールドを追加する(フィールド名、フィールドの型、フィールドのサイズ)
    aryRs.Fields.Append "ExcelFile", adVarChar, 1000
    
    'Recordsetをオープンする
    aryRs.Open
        
    'フォルダ配下にあるデータが入力されているExcelファイルの数分ループさせる
    For Each f In fso.GetFolder(Worksheets("top").Range("folderNM").Value).files
    
        '新しいレコードを作成する
        aryRs.AddNew
        
        '取得したexcelのファイル名を追加する
        aryRs.Fields(0) = f
        
        '追加したデータ(ファイル名)を保存する
        aryRs.Update
        
    Next f
    
    'excelのファイルで昇順でソートする
    aryRs.Sort = "ExcelFile ASC"

以上のコードは、取り込む複数のexcelファイルのファイル名を配列に格納する前に、ファイル名でソートさせる処理のコードです。

今回のサンプルではどのようにソートさせるのかというと、recordsetを使います。

まずは40行目でrecordsetにフィールドを一つ追加します。(取り込むexcelファイルの名前を保持するため用のrecordset)

今回はrecordsetオブジェクトのAppendメソッドの引数に、フィールド名、フィールドの型、フィールドのサイズの3つを指定しています。

フィールド名：excelのファイル名を保持するためのフィールドの名前を適当に付けます
フィールドの型：excelのファイル名が文字列なので「adVarChar」(文字列型)を指定
フィールドのサイズ：サイズはexcelの(フルパス付きの)ファイル名の長さが下回らないよう値を指定

43行目ではrecordsetを開きます。

46行目では、取り込むexcelファイルの数分ファイル名を取得する処理を繰り返すFor文を用意します。

セル「folderNM」に入力された、取り込むexcelファイルの置き場からGetFolderメソッドを使ってすべてのファイル名を取り出します。

取得したファイル名はフルパスで変数「f」に格納されます。

取得したファイル名をrecordsetに保持するため、AddNewメソッドで保持するためのレコードを追加し、取得したファイル名を52行目でrecordsetに追加します。

追加したら、55行目のUpdateメソッドで保存します。

このUpdateメソッドが完了したら無事にrecordsetにファイル名が保存されたので、残りのファイルの数分同じ処理を繰り返します。(ファイル名の取得からUpdateまで)

残りのファイルの数分同じ処理が終わったら、60行目でSortメソッドを実行しソート処理を行います。

今回のサンプルでは昇順でソートしたいので、「ASC」を指定しています。

注目すべきコード③

次に見て頂きたいのは63行目です。

    'Excelファイルの数分ループさせる
    Do Until aryRs.EOF

このコードは、Excelファイルの数分ループさせるループを行うコードです。

注目すべきコード②で行ったソート済みのファイルの順に処理が繰り返されます。

注目すべきコード④

次に見て頂きたいのは66行目から69行目です。

        'データを貼り付ける先のシート名を取得する
        pstDdSheetNM = Replace(Split(aryRs.Fields(0).Value, ".")(0), filePath & "\", "")
        
        'パスを除いたexcelファイル名を取得する
        excelFile = pstDdSheetNM & "." & Split(aryRs.Fields(0).Value, ".")(1)

以上のコードは、取り込んだexcelファイルから拡張子を除いたファイル名と、パスを除いたexcelファイル名を取得する処理のコードです。

66行目では、取り込んだexcelファイルから拡張子を除いたファイル名を取得しています。

拡張子を除いたファイル名は、取り込み先のexcelファイルを貼り付ける際のシートの名前に使います。

例えば取り込むexcelファイルが「01_社員情報.xlsx」なら「01_社員情報」をシート名に使います。

69行目では、パスを除いたexcelファイル名を取得します。

ここでは、66行目で取得した文字列と拡張子を結合させています。(例えば「01_社員情報」と「.xlsx」)

注目すべきコード⑤

次に見て頂きたいのは72行目から103行目です。

        'データを貼り付ける先のシート有無のチェック
        For Each ws In Worksheets
    
            If ws.Name = pstDdSheetNM Then
    
                'データを貼り付ける先のシート名が存在している場合
    
                '変数「shtExistFlg」にTrueを設定する
                shtExistFlg = True
    
            End If
    
        Next ws
    
        If shtExistFlg = False Then
    
            'データを貼り付ける先のシートが存在しない場合
    
            'データを貼り付ける先のシートを新規作成する
            Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                           .Name = pstDdSheetNM
                           
            'シートを作成したのでフラグをfalseで初期化する
            shtExistFlg = False
                               
        Else
    
            'データを貼り付ける先のシートが存在する場合
    
            'データを貼り付ける先のシートのセル全てをクリアする
            Worksheets(pstDdSheetNM).Cells.Clear
    
        End If

以上のコードは、取り込んだexcelファイルの表を、取り込み先のexcelファイルに新規でシートを作成してそのソートに出力させるコードの処理です。

すでにシートが存在している場合はシートは作成しません。

72行目で取り込み先のexcelファイルのシートを全て処理を繰り返すFor文を呼び出し、74行目で取り込み先のexcelファイルのシート全てに対し一つ一つ同じ名前のシートがないか判定します。

もしすでに同じシートが存在する場合は変数「shtExistFlg」にTrueを設定します。

この変数「shtExistFlg」はTrueならすでにシートが存在する、Falseならシートは存在しないことを判定するためのフラグになります。

この変数「shtExistFlg」を85行目で判定するのに使います。

「shtExistFlg」がFalseなら90行目と91行目でシートを作成します。

「shtExistFlg」がTrueならシートの作成処理は行わずに、そのシートをクリアします。(データを取り込むために)

注目すべきコード⑥

次に見て頂きたいのは105行目から128行目です。

        With conn
        
            '接続情報の取得(取り込みたいExcelファイルに接続する)
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source = " & aryRs.Fields(0).Value & _
            ";Extended Properties =Excel 12.0;"
            
            'データソースへの接続を開く
            .Open
            
        End With
            
        'Recordsetを開く(表の件数と列数取得)
        exdRs.Open " [" & "work$" & "]", conn, adOpenStatic
        
        '配列の最後の次元の要素数を変更する
        '→今回は要素数を1つ拡張する
        ReDim Preserve tblRangAry(cnt)
        
        'excelファイルにある表の最後列の位置
        excelFlLstColPos = Split(Sheets("work").Cells(1, exdRs.Fields.Count).Address, "$")(1)
        
        'excelの表の範囲を取得する
        tblRangAry(cnt) = "[" & pstDdSheetNM & "$A1:" & excelFlLstColPos & exdRs.RecordCount + 1 & "]"

以上のコードは、マクロが取り込むExcelファイルにADO接続し、recordsetを開く処理です。

28行目で生成したADO接続するために必要なconnectionオブジェクトのインスタンスに対してADO接続するために108行目から110行目で接続情報を設定します。

【参考：28行目のconnectionオブジェクトのインスタンス】

    'Connectionインスタンスの生成
    Set conn = New ADODB.Connection

なお、109行目では取り込むExcelファイルをフルパスで指定しています。

ファイル「01_社員情報.xlsx」

ファイル「02_所属部署.xlsx」

ファイル「03_役職.xlsx」

次に、118行ではRecordsetを開いているのですが、この処理の目的は取り込んだexcelファイルの表の行数と列数を取得することです。

なぜ表の行数と列数を取得する必要があるのかというと、表のデータをどの列の何行目までセルを貼り付ければいいのかがマクロは分からないからです。

なので、Recordsetを開く処理を行い、行数と列数を取得して貼り付け先の範囲を指定する必要があります。

なお、行数と列数の取得は次の通りです。

行数：exdRs.RecordCount
列数：exdRs.Fields.Count

※exdRs：今回のサンプルで用意したRecordsetオブジェクトのインスタンス変数

以上の列数を元に、125行目で列数を列のアルファベットに変換して表の最終列を特定(例えば3列目ならC)して変数「excelFlLstColPos」に格納し、128行目で取り込むexcelの表の範囲を特定します。

最終的には『[シート名$表の範囲]』という形式で配列「tblRangAry」に格納されます。

ファイル「01_社員情報.xlsx」

ファイル「02_所属部署.xlsx」

ファイル「03_役職.xlsx」

注目すべきコード⑦

次に見て頂きたいのは130行目から141行目です。

        With Worksheets(pstDdSheetNM)
            
            '表の範囲を取得する
            Set rng = .Range(.Cells(1, 1), .Cells(exdRs.RecordCount + 1, exdRs.Fields.Count))
        
            'データを貼り付ける先のシートにデータを貼り付ける
            rng.Formula = "=if(ISBLANK('" & filePath & "\[" & excelFile & "]work'!A1), """",'" & filePath & "\[" & excelFile & "]work'!A1)"
                    
            '取得先のExcelファイルを参照している計算式がセルに入力されているので、値そのものを再入力する
            rng.Copy: rng.PasteSpecial Paste:=xlPasteValues
        
        End With

以上のコードは、取り込み先のシートに取り込んだシートのデータを貼り付ける処理のコードです。

133行目で、閉じているExcelファイルの表の範囲を変数「rng」に格納しています。

この表の範囲に対して136行目で数式を出力しています。

以上のコードではどんな数式なのか分かりにくいので、実際の数式をお見せします。

コード

=if(ISBLANK(‘C:\work\10_勉強\10_VBA関連\0185\data\[03_役職.xlsx]work’!A1), “”,’C:\work\10_勉強\10_VBA関連\0185\data\[03_役職.xlsx]work’!A1)

これだと長すぎるので引数ごとに改行させると次の通りになります。

改行させたコード

=if(ISBLANK(‘C:\work\10_勉強\10_VBA関連\0185\data\[03_役職.xlsx]work’!A1)
, “”
,’C:\work\10_勉強\10_VBA関連\0185\data\[03_役職.xlsx]work’!A1)

計算式の意味

フォルダ「C:\work\10_勉強\10_VBA関連\0185\data」にあるexcelファイル「03_役職.xlsx」のシート「work」のセルA1がブランク(正)かブランクでない(偽)かを判定します。
ブランクの場合は第2引数を、ブランクでない場合は第3引数を返します。

返す値

フォルダ「C:\work\10_勉強\10_VBA関連\0185\data」にあるexcelファイル「03_役職.xlsx」のシート「work」のセルA1がブランクならブランクを返し、ブランクではない場合はフォルダ「C:\work\10_勉強\10_VBA関連\0185\data」にあるexcelファイル「03_役職.xlsx」のシート「work」のセルA1の値を返します。

補足

上記の計算式だとセルA1だけしかブランクかブランクでないかを判定していないように見えますが、133行目で表の範囲を取得したrngに対してこの計算式を設定しているので、この計算式はA1のセルだけでなく表のセル全てに適応されます。

以上のコードに出てくるISBLANK関数をなぜわざわざ使っているのかというと、空白のセルは必ずブランクにする必要があるからです。

なぜ空白のセルは必ずブランクにするのかというと、ブランクのセルが含まれる表をそのまま取り込んで取り込み先のセルに出力すると、そのブランクのセルが0で出力されてしまうからです。

取り込むexcelファイルの表

取り込み先のexcelファイルの表(取り込み後)※ISBLANK関数を使わなかった場合

以上のように値が0で出力されてしまうと、取り込んだ表のセルがブランクなのか初めから0が入力されているセルなのか判断が付きません。

そのためこの事象の回避策としてISBLANK関数を使い、取り込んだ表のブランクのセルは必ずブランクだと認識させるよう対応し、取り込み先の表のセルも同じくブランクとなるようにしておきます。

取り込むexcelファイルの表

取り込み先のexcelファイルの表(取り込み後)※ISBLANK関数を使った場合

次に139行目のコードですが、このコードは閉じているExcelファイルから取り込んだマクロ側のセルに対して、値そのものを再入力している処理です。

なぜこの処理を行うのかというと、この処理を行わないと値を参照する閉じているExcelファイルが削除されたり移動された場合に値が参照できすに参照エラーになるからです。

以下は139行目のコードを実行する前のセルの数式は以下の画像の通りです。

以上の画像の赤枠で示した数式を見ると閉じているExcelファイルを参照しています。

このままだと、閉じているExcelファイルが削除されたり移動された場合に値が参照できすに参照エラーになってしまいます。

なので、値そのものを再入力してこの数式を値で上書きするようにすることで、閉じているExcelファイルが削除されたり移動された場合でも問題が起きることが無くなります。

注目すべきコード⑧

次に見て頂きたいのは144行目から150行目です。

        'recordsetを閉じる
        exdRs.Close
        
        'Connectionを切断する
        conn.Close
    
        '次のレコードに移動する
        aryRs.MoveNext

以上のコードは、recordsetとconnectionを閉じる処理と、recordsetのレコードを次のレコードに移動する処理です。

recordsetとconnectionは1つのexcelファイルに対して1回Openし、処理が終わったら必ずCloseさせる必要があります。(Openしている状態でもう一度Openを繰り返さない)

今回は同じrecordsetとconnection(変数exdRsと変数conn)を使いまわしているので、次のexcelファイルに対してrecordsetとconnectionをOpenするときはかならずCloseさせた状態でOpenさせないとエラーになります。(Openしているのに何でまたOpenするの？と怒られます)

なので、かならずこのClose処理を行います。(144行目と147行目)

150行目では、recordsetにある次のデータ(ここでは取り込むexcelファイル)を参照するためにMoveNextメソッドを実行します。

注目すべきコード⑨

次に見て頂きたいのは159行目から166行目です。

    With conn

        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"

        'データソースへの接続を開く
        .Open

    End With

以上のコードは、マクロが自分自身のExcelファイルにADO接続するためのコードになります。

ADO接続するために必要なインスタンス(変数conn)に対してADO接続するために159行目から161行目で接続情報を設定します。

なお、160行目では自分自身のExcelファイルをフルパスで指定しています。

自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。

注目すべきコード⑩

次に見て頂きたいのは169行目から189行目です。

    '左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
    sqlStr = "select"
    sqlStr = sqlStr & "  a.項番 as 項番"
    sqlStr = sqlStr & " ,a.名前 as 名前"
    sqlStr = sqlStr & " ,a.社員番号 as 社員番号"
    sqlStr = sqlStr & " ,a.年齢 as 年齢"
    sqlStr = sqlStr & " ,a.出身 as 出身"
    sqlStr = sqlStr & " ,a.入社年 as 入社年"
    sqlStr = sqlStr & " ,b.名称 as 所属部署ID"
    sqlStr = sqlStr & " ,c.名称 as 役職ID"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " ( "
    sqlStr = sqlStr & " " & tblRangAry(0) & " as a"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & tblRangAry(1) & " as b"
    sqlStr = sqlStr & " ON  a.所属部署ID = b.ID"
    sqlStr = sqlStr & " ) "
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & tblRangAry(2) & " as c"
    sqlStr = sqlStr & " ON  a.役職ID = c.ID"
    sqlStr = sqlStr & " )"

以上のコードは、表「社員情報」と、表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させるためのSQL文を用意しているコードです。

以上のコードは、1番目に取り込んだexcelファイルの表と、2番目と3番目に取り込んだexcelファイルの表をSQLのLeft Outer Joinを使って結合させるためのSQL文を用意しているコードです。

今回のサンプルでは、1番目に取り込むexcelファイルが「01_社員情報.xlsx」、2番目と3番目に取り込むのが「02_所属部署.xlsx」「03_役職.xlsx」です。

上記のSQL文では配列tblRangAryが使われていますが、この配列にはこのexcelファイル名が格納されています。

1番目のexcelファイル「01_社員情報(.xlsx)」：tblRangAry(0)
2番目のexcelファイル「02_所属部署(.xlsx)」：tblRangAry(1)
3番目のexcelファイル「03_役職(.xlsx)」：tblRangAry(2)

tblRangAry(0)には「01_社員情報」が格納されていることを想定しています。(「注目すべきコード②」で説明しているとおり、ファイル名でソートしているため「01_社員情報(.xlsx)」が一番最初に来る)

なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLを以下にお見せします。

select
    a.項番 as 項番,
    a.名前 as 名前,
    a.社員番号 as 社員番号,
    a.年齢 as 年齢,
    a.出身 as 出身,
    a.入社年 as 入社年,
    b.名称 as 所属部署ID,
    c.名称 as 役職ID
from
    (
        (
            [01_社員情報$A1:H16] as a
            LEFT OUTER JOIN
                [02_所属部署$A1:B7] as b
            ON  a.所属部署ID = b.ID
        )
        LEFT OUTER JOIN
            [03_役職$A1:B6] as c
        ON  a.役職ID = c.ID
    )

今回の表の場合、表「01_社員情報」と表「02_所属部署」を結合するのに参照するキー列は、表「01_社員情報」側は所属部署IDで、表「02_所属部署」側はIDになります。

ONの後に「a.所属部署ID = b.ID」と記述し、所属部署IDとIDが同じものを条件に結合しています。

また、表「社員情報」と表「役職」を結合するのに参照するキー列は、表「社員情報」側は役職IDで、表「役職」側はIDになります。

ONの後に「a.役職ID = c.ID」と記述し、役職IDとIDが同じものを条件に結合しています。

注目すべきコード⑪

次に見て頂きたいのは192行目です。

    'Recordsetを開く
    exdRs.Open sqlStr, conn, adOpenStatic

以上のコードは、recordsetのOpenメソッドにSQL文を引数に指定して実行してデータを取得するコードです。

なお、recordsetのOpenメソッドを実行するには事前にRecordsetオブジェクトのインスタンスを生成する必要があります。(生成していないとエラーになる)

今回は34行目でRecordsetオブジェクトのインスタンスを生成しています。

    'Recordsetオブジェクトのインスタンスを生成する(excelの表データ用)
    Set exdRs = New ADODB.Recordset

注目すべきコード⑫

次に見て頂きたいのは198行目から206行目です。

    '表の列数分だけ処理をループする
    For cnt = 0 To exdRs.Fields.Count - 1

        '表の列名をセルに出力する
        Worksheets("work").Cells(1, 1 + cnt).Value = exdRs.Fields.Item(cnt).Name

    Next cnt

    '先ほど貼り付けた列名の下の行にデータを貼り付ける
    Worksheets("work").Cells(1, 1).Offset(1, 0).CopyFromRecordset exdRs

以上のコードは、表「01_社員情報」と、「02_所属部署」「03_役職」を、SQLのLeft Outer Joinを使って結合させたデータをセルに出力する処理とそのヘッダ(列名)をセルに出力する処理を行っているコードです。

198行目から203行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力しています。

201行目のFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。

ヘッダ(列名)をセルに出力し終わったら206行目で、ヘッダ(列名)の1つ下のセル位置に結合させたデータをセルに貼り付けています。

目次へ戻る

動作確認

マクロ実行前

今回はExcelファイルの例のexcelファイルを使います。

マクロ実行後

マクロ実行後は、下の画面の通りに表が作成されます。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Windows Script Host Object Model(wshom.ocx)
Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの6行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを、7行目から9行目の「ADODB.Connection」「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数

    Dim conn                As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim aryRs               As ADODB.Recordset      'レコードセット用変数(配列のソート用)
    Dim exdRs               As ADODB.Recordset      'レコードセット用変数(excelの表データ用)

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「wshom.ocx」と「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「FileSystemObject」「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、閉じた3つのExcelファイルにある表をLeft Outer Joinを使って結合しデータを取得する方法についてご説明しました。

閉じたexcelファイルにある表を、データベースのテーブルのようにSQLのLeft Outer Joinを使って結合させることができます。

閉じたexcelファイルにある表を取り込むのに必要な事前準備があったり(昇順を意識してファイル名を付ける)、結合させるのにキーとなる列が必要になるなどの条件が必要ですが、表の集計の一つにSQLのLeft Outer Joinを使う方法があることを覚えておくと便利かもしれないので参考にしてみてくださいね。