【ExcelVBA】別ブックのセルの値をSQLのSelect文を使って取得する方法について

9分

SHARE

この記事では、別ブックのセルの値をSQLのSelect文を使って取得する方法についてご説明します。

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

【動画】別ブックのセルの値をSQLのSelect文を使って取得する実際の動き

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


生徒情報が保存されているExcelファイルに対して、マクロがSQLのSelect文を使って該当する生徒情報を検索・抽出してシートに書き出しています。

マクロ作成の流れ

STEP.1
ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続する
ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続します。
STEP.2
検索・抽出したいデータが存在するExcelファイルのシート名を全て取得する
検索・抽出したいデータが存在するExcelファイルのシート名を全て取得します。
STEP.3
データを検索・抽出するSelect文を生成する
データを検索・抽出するSelect文を生成します。
Select文のFrom句には、STEP.2で取得したシート名を指定します。
STEP.4
抽出したデータをシートに書き出す
抽出したデータをシートに書き出します。
STEP.5
データを検索・抽出する対象のシートが複数ある場合はSTEP.3とSTEP.4を繰り返す
データを検索・抽出する対象のシートが複数ある場合はSTEP.3とSTEP.4を繰り返します。
全てのシートに対して処理が終わったら本マクロの処理は終了です。
目次へ戻る

Excelファイルの例

マクロ側

マクロ側は次のExcelファイルを用意しました。

今回は生徒情報を検索・抽出したいので、黄色のセルに検索したい生徒の名称を入力します。

生徒の名称を入力した後にマクロを実行すると、検索対象の生徒の科目(国語や数学など5項目)の点数がG列からK列の書き出されます。
(E列はシート名、F列は生徒名を出力しています)

マクロ実行後は下のように検索対象の生徒の科目点数が書き出されます。

テストデータ(生徒情報)

テストデータ(生徒情報)は次のExcelファイルを用意しました。

生徒の科目(国語や数学など5項目)の点数が入力された、1月から6月の6つのシートを用意しています。

補足

テストデータ(生徒情報)の表はテーブルに変換していません。

目次へ戻る

コードの例

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

Option Explicit

Private Sub btn_getDirFileList_Click()

    Dim dataFile            As String               '抽出対象のデータが登録されたExcelファイル
    Dim oCon                As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim sgRs                As ADODB.Recordset      'レコードセット用変数(シート名取得用)
    Dim dtRs                As ADODB.Recordset      'レコードセット用変数(データ取得用)
    Dim lastRow             As Long                 '最終行
    Dim shtNMCnt            As Integer              'シート名用カウンタ
    Dim ws                  As Variant              'Worksheet用変数
    Dim sqlStr              As String               'SQL文
    Dim rDataExistFlg       As Boolean              'データ有無判定
    Dim rng                 As Range                'Rangeオブジェクト格納用変数
    Dim sheetNM()           As Variant              'シート名
            
    '抽出対象のデータが登録されたExcelファイル
    dataFile = ActiveWorkbook.Path & "\data.xlsm"

    'インスタンスの生成
    Set oCon = New ADODB.Connection
    
    With oCon
    
        '接続情報の取得
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & dataFile & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With
    
    'Excelのファイルのスキーマ情報を取得するためのインスタンスを生成する
    Set sgRs = oCon.OpenSchema(adSchemaTables)
    
    Do Until (sgRs.EOF)

        ReDim Preserve sheetNM(shtNMCnt)
    
        'シート名を取得する
        sheetNM(shtNMCnt) = sgRs.Fields("TABLE_NAME").Value
    
        shtNMCnt = shtNMCnt + 1
        
        sgRs.MoveNext
        
        DoEvents
    
    Loop
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set dtRs = New ADODB.Recordset
    
    '最終行に1を設定する(処理の最初は1)
    lastRow = 2
        
    'シート名用カウンタの初期値を設定
    shtNMCnt = 0
    
    'フォルダ名とファイル名貼り付け用シート有無のチェック
    For Each ws In sheetNM
                    
        'データを取得するSQL文を作成する
        sqlStr = "select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " from"
        sqlStr = sqlStr & " [" & ws & "]"
        sqlStr = sqlStr & " where "
        sqlStr = sqlStr & " [名前]" & " = '" & Range("searchStr").Value & "'"
        
        'Recordsetを開く
        dtRs.Open sqlStr, oCon, adOpenStatic
        
        If dtRs.RecordCount > 0 Then
        
            'レコード件数が1件以上ある場合
            
            'シート名をシート「work」に貼り付ける
            Sheets("top").Range("E" & lastRow).Value = Replace(Replace(ws, "$", ""), "'", "")
        
            'データをシート「work」に貼り付ける
            Sheets("top").Range("F" & lastRow).CopyFromRecordset dtRs
            
        End If
            
        '最終行(+1)を取得する
        lastRow = Worksheets("top").Cells(Rows.Count, 6).End(xlUp).Row + 1
                
        'RecordsetをCloseする
        dtRs.Close
    
    Next ws
    
    '後処理
    
    Set sgRs = Nothing
    Set dtRs = Nothing
    Set oCon = Nothing
        
    MsgBox "完了"

End Sub

コードの解説

注目すべきコード①

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

    With oCon
    
        '接続情報の取得
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & dataFile & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With

以上は、別ブックのテストデータのExcelファイルにマクロが接続するためのコードです。

変数「dataFile」にはテストデータのExcelファイル名(フルパス)が格納されています。

31行目のOpenメソッドを実行すると、マクロがテストデータのExcelファイルに接続します。

注目すべきコード②

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

    'Excelのファイルのスキーマ情報を取得するためのインスタンスを生成する
    Set sgRs = oCon.OpenSchema(adSchemaTables)
    
    Do Until (sgRs.EOF)

        ReDim Preserve sheetNM(shtNMCnt)
    
        'シート名を取得する
        sheetNM(shtNMCnt) = sgRs.Fields("TABLE_NAME").Value
    
        shtNMCnt = shtNMCnt + 1
        
        sgRs.MoveNext
        
        DoEvents
    
    Loop

以上のコードは、テストデータのExcelファイルのシート名全てを取得するコードです。

なぜシート名を取得するのかというと、Select文のFrom句にはシート名を指定する必要があるからです。

データベースだとFrom句にはテーブル名を指定しますが、Excelの場合はFrom句にはシート名を指定します。

なので、以上のコードを実行してシート名を取得しないといけないんです。

シート名を取得するにはまず、36行目でExcelのファイルのスキーマ情報を取得するためのインスタンスを生成します。

スキーマ情報とは、Excelファイルのシート名やExcelファイルの作成日、更新時間などの情報のことです。

ちなみに、Excelファイルのシート名のスキーマ名は「TABLE_NAME」です。

「TABLE_NAME」をFieldsコレクションに指定することで、Valueプロパティからシート名を取得することができます。(43行目)

シート名の取得は、Excelのデータファイルに存在する全て行います。(38行目でループ指定(sgRs.EOFがTrueになるまでループ(EOFがTrueは、最後まで到達したの意味)))

注目すべきコード③

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

        'データを取得するSQL文を作成する
        sqlStr = "select"
        sqlStr = sqlStr & " *"
        sqlStr = sqlStr & " from"
        sqlStr = sqlStr & " [" & ws & "]"
        sqlStr = sqlStr & " where "
        sqlStr = sqlStr & " [名前]" & " = '" & Range("searchStr").Value & "'"

以上は、生徒情報が保存されているExcelファイルに対して、生徒データを検索・抽出Select文を作成するコードです。

select文のフィールド名(上記では「*」を指定)は67行目で、From句の指定は68行目と69行目で、Where句の指定は70行目と71行目で指定しています。

コードだけではSelect文が分かりづらいと思うので、Select文の例を以下にお見せします。

select
    *
from
    ['1月$']
where
    [名前] = '生徒3'

フィールド名

今回はすべてのフィールド名の値を取得したいので「*」を指定しています。

フィールド名を個別で指定したい場合、例えば「国語」というフィールド名の場合は次のようなSelect文になります。

select
    [国語]
from
    ['1月$']
where
    [名前] = '生徒3'

From句

今回のSelect文のFROM句には、シート名を指定します。

上記のSelect文ではFrom句に、シート名「1月」を指定しています。

Where句

今回のSelect文のWhere句には、生徒情報が保存されているExcelファイルの「名前」の列にある「生徒3」を指定しています。

【参考】生徒情報が保存されているExcelファイルの「名前」の列

注目すべきコード④

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

        'Recordsetを開く
        dtRs.Open sqlStr, oCon, adOpenStatic

OpenメソッドにSelect文を引数に指定して実行するとSelect文が実行されて、データを検索・抽出することができます。

注目すべきコード⑤

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

        If dtRs.RecordCount > 0 Then
        
            'レコード件数が1件以上ある場合
            
            'シート名をシート「work」に貼り付ける
            Sheets("top").Range("E" & lastRow).Value = Replace(Replace(ws, "$", ""), "'", "")
        
            'データをシート「work」に貼り付ける
            Sheets("top").Range("F" & lastRow).CopyFromRecordset dtRs
            
        End If

上記コードは、生徒情報が保存されているExcelファイルから抽出したデータを、マクロ側のシートに貼り付ける処理です。

46行目でデータが存在しているかRecordCountプロパティの値で判定します。

データが1件以上の場合は、54行目で抽出したデータをシートに貼り付けます。(51行目は参考にシート名を貼り付けています)

注目すべきコード⑥

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

        'RecordsetをCloseする
        dtRs.Close

上記のコードは、RecordsetをCloseする処理です。

Select文を実行するときにはRecordsetをOpenしますが、1度Select文を実行したら次にSelect文を実行するのに1度RecordsetをCloseする必要があります。

RecordsetがOpenされたまま(再度Select文を実行しようとして)続けてRecordsetをOpenするとエラーになるので、忘れずにRecordsetをCloseします。

目次へ戻る

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

目次へ戻る

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

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

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

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

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

    Dim oCon                As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim sgRs                As ADODB.Recordset      'レコードセット用変数(シート名取得用)
    Dim dtRs                As ADODB.Recordset      'レコードセット用変数(データ取得用)

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

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

目次へ戻る

最後に

本記事では、別ブックのセルの値をSQLのSelect文を使って取得する方法についてご説明しました。

処理の流れをおさらいしておくと次の通りです。

STEP.1
ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続する
ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続します。
STEP.2
検索・抽出したいデータが存在するExcelファイルのシート名を全て取得する
検索・抽出したいデータが存在するExcelファイルのシート名を全て取得します。
STEP.3
データを検索・抽出するSelect文を生成する
データを検索・抽出するSelect文を生成します。
Select文のFrom句には、STEP.2で取得したシート名を指定します。
STEP.4
抽出したデータをシートに書き出す
抽出したデータをシートに書き出します。
STEP.5
データを検索・抽出する対象のシートが複数ある場合はSTEP.3とSTEP.4を繰り返す
データを検索・抽出する対象のシートが複数ある場合はSTEP.3とSTEP.4を繰り返します。
全てのシートに対して処理が終わったら本マクロの処理は終了です。

別ブックのセルの値を取得する方法に一つに、SQLのSelect文を使う方法があることを覚えておくと便利かと思います。

別ブックのセルの値を取得したい時は参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】Excelのマクロ・コマンドプロンプトを使…

次の記事

【ExcelVBA】Excelのシートの値をSQLのInser…