【Excel VBA】QueryTableを使ってxlsxファイルからデータを取得・更新する方法【備忘録】

今回はQueryTableを使ってxlsx形式のファイルからデータを持ってくる方法を備忘録も兼ねて紹介したいと思います。
勉強中の為拙い所は多々ございますが、もし参考になれば幸いです。

今回やること
コードの紹介
コードの説明
1. 接続情報の設定
2. データ出力
まとめ

今回やること

QueryTableを使用して別階層に保存しているExcelファイルの「データ」シートのセル内容をマクロを実行したブックの「クエリ」シートへ持ってくるようにします。

ちなみに「データ」シートには以下のように会員番号やら名前といった情報が入っています。

コードの紹介

それでは早速コードを紹介します。
なお、格納先xlsxファイルのパスはご自身の環境に合わせて変更してください。

Sub test_QueryTable()

    Dim target_Sht               As Worksheet
    Dim target_Path              As String
    Dim qt_CSV                   As QueryTable

    '書き出し先のシートを取得
    Set target_Sht = ThisWorkbook.Worksheets("クエリ")
    
    '「クエリ」シートをリセット
    target_Sht.Cells.Delete
    
    '格納先xlsxファイルのパスを指定
    target_Path = "C:\Test\はりぼな_データベース.xlsx"
    
    '接続情報の設定
    Set qt_CSV = target_Sht.QueryTables.Add(Connection:="ODBC;DSN=Excel Files;DBQ=" & target_Path, _
                                            Destination:=target_Sht.Range("A1"), _
                                            Sql:="SELECT * FROM[データ$];")
    
    'データ出力
    With qt_CSV
        .BackgroundQuery = False
        .AdjustColumnWidth = False
        .Refresh
        .Delete
    End With

End Sub

実行すると「クエリ」シートにデータが反映されます。

取得先のファイルを編集してから実行すれば、更新された分も反映されます。

コードの説明

次にコードについて説明します。

ポイントになるのは接続情報の設定をする部分（Set qt_CSV ...）とデータ出力の部分（With qt_CSV ...）になります。

'接続情報の設定
Set qt_CSV = target_Sht.QueryTables.Add(Connection:="ODBC;DSN=Excel Files;DBQ=" & target_Path, _
                                        Destination:=target_Sht.Range("A1"), _
                                        Sql:="SELECT * FROM[データ$];")

'データ出力
With qt_CSV
    .BackgroundQuery = False
    .AdjustColumnWidth = False
    .Refresh
    .Delete
End With

それぞれ分けて説明していきます。

接続情報の設定

1つ目は接続情報の設定です。
Set qt_CSV = target_Sht.QueryTables.Add(...)でクエリテーブルを追加します。

今回使用するQueryTables.Addでは引数を3つ指定するので、それぞれ分けて説明します。

Connection

Connectionは接続するデータソースの形式やファイルパスを指定します。

Connection:="【接続先情報を指定】"

まず今回はODBCを使用してデータを取得するのでODBC;を指定します。

Connection:="ODBC;"

その後ろにDSN（使用するデータソース名）を指定します。
今回はxlsxファイルから取得するので、Excel Filesを指定しています。

Connection:="ODBC;DSN=Excel Files;"

最後にDBQ（データソースのパス）を指定します。
ここには初めに設定したtarget_Pathを指定します。

'格納先xlsxファイルのパスを指定
target_Path = "C:\Test\はりぼな_データベース.xlsx"

Connection:="ODBC;DSN=Excel Files;DBQ=" & target_Path

以上でConnectionの設定は完了です。

Destination

Destinationは出力先のセルを指定します。
今回は「クエリ」シートのA1セルに出力するのでtarget_Sht.Range("A1")としています。

Destination:=target_Sht.Range("A1")

以上でDestinationの設定は完了です。

Sql

SqlはSQL文（条件）を指定します。
今回は全てのデータを「データ」シートから取得するのでSELECT * FROM[データ$];としています。

Sql:="SELECT * FROM[データ$];"

データ出力

次にデータ出力について説明します。

'データ出力
With qt_CSV
    .BackgroundQuery = False
    .AdjustColumnWidth = False
    .Refresh
    .Delete
End With

.Refreshで出力し、.Deleteでクエリテーブルを削除するといった感じです。

注意点として、.BackgroundQueryはバグの原因になり兼ねないので、基本的にFalseにしておきましょう。

まとめ

今回はQueryTableを使ってxlsx形式のファイルからデータを持ってくる方法を紹介しました。

QueryTableを使用することで外部データファイルからデータを高速で取得＆出力することができるので、是非参考にしていただければと思います。

このブログではVBAに関する記事を不定期で投稿していますので、是非ご覧ください！