今回はQueryTableを使ってxlsx形式のファイルからデータを持ってくる方法を備忘録も兼ねて紹介したいと思います。
勉強中の為拙い所は多々ございますが、もし参考になれば幸いです。
今回やること
QueryTableを使用して別階層に保存しているExcelファイルの「データ」シートのセル内容をマクロを実行したブックの「クエリ」シートへ持ってくるようにします。
![今回やること_1](https://masashi-blog418.com/wp-content/uploads/2024/01/image-9-1024x399.png)
ちなみに「データ」シートには以下のように会員番号やら名前といった情報が入っています。
![今回やること_2](https://masashi-blog418.com/wp-content/uploads/2024/01/image-8-1024x594.png)
コードの紹介
それでは早速コードを紹介します。
なお、格納先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
実行すると「クエリ」シートにデータが反映されます。
![コードの紹介_1](https://masashi-blog418.com/wp-content/uploads/2024/01/image-11-1024x940.png)
取得先のファイルを編集してから実行すれば、更新された分も反映されます。
![コードの紹介_2](https://masashi-blog418.com/wp-content/uploads/2024/02/image-1012x1024.png)
![コードの紹介_3](https://masashi-blog418.com/wp-content/uploads/2024/02/image-1.png)
コードの説明
次にコードについて説明します。
ポイントになるのは接続情報の設定をする部分(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に関する記事を不定期で投稿していますので、是非ご覧ください!
コメント