Excel VBA QueryTableを使用して外部データベースを読み込む

スポンサーリンク
スポンサーリンク

QueryTableを使用して外部データベースを読み込む方法

外部データベースをワークシートに読み込む方法は
ActivX Data Objects【アクティブエックス データ オブジェクツ】などを
使用しても読み込むことができますが
ここでは、QueryTable【クエリテーブル】オブジェクトを使用して
更新可能な外部データベースの読み込みについて解説します。

QueryTableを使用して外部データベースを読み込む手順

1.QueryTabls【クエリーテブルズ】コレクションのAdd【アド】メソッド
使用して、新しいデータベースクエリ(QueryTableオブジェクト)を作成します。
Query【クエリ】は「問い合わせ」という意味があり
Table【テーブル】は「表」という意味があります。


2.作成されたQueryTable【クエリテーブル】オブジェクトに対して
外部データベースを設定するプロパティを使用して読み込みの設定をします。


3.QueryTable【クエリテーブル】オブジェクトの
Refresh【リフレッシュ】メソッドを使用して
QueryTable【クエリテーブル】オブジェクトを更新して
ワークシートに内容を表示します。


Accessの「商品マスター」テーブルを読み込んでワークシートに表示する例

「商品マスター」テーブルをブックを開くたびに更新して読み込むコード例

Sub 外部データベース読み込み()
Dim 接続文字列 As String
接続文字列 = "OLEDB;" & _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "¥Database1.accdb"
With ActiveSheet.QueryTables.Add(接続文字列, Range("A1"))
.Name = "商品マスター"
.CommandType = xlCmdTable
.CommandText = "商品マスター"
.RefreshOnFileOpen = True
.Refresh
End With
End Sub

コードの解説

2行目
【Dim 接続文字列 As String】

QueryTable【クエリテーブル】オブジェクトを追加する
QueryTables【クエリテーブルズ】コレクションの
Add【アド】メソッドの接続先を表す引数
Connection【コネクション】に設定する
接続文字列を格納する変数「接続文字列」を
文字列型(String)で宣言します。


3行目~5行目
【接続文字列 = “OLEDB;” & _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & ThisWorkbook.Path & “¥Database1.accdb”】

接続するデータの種類とデータの場所を
接続文字列として指定して変数「接続文字列」に代入します。
接続文字列の指定方法についてはこちらをご覧ください。
データベースの場所を設定するDate Source【データソース】には
WorkbookオブジェクトのPath【パス】プロパティを使用して
「ThisWorkbook」つまり、このコードが記述されている
ブックと同じ場所(パス)にある
「Database1.accdb」を指定しています。


6行目
【With ActiveSheet.QueryTables.Add(接続文字列, Range(“A1”))】
QueryTables【クエリテーブルズ】コレクションの
Add【アド】メソッドを使用してQueryTable【クエリテーブル】オブジェクトを
追加します。
第一引数の接続先を表すConnection【コネクション】には
変数「接続文字列」を指定し
第二引数の抽出先を表すDestnation【ディスティネーション】には
A1セルを指定してWithステートメントで指定します。


7行目
【.Name = “商品マスター”】

QueryTableオブジェクトの
Name【ネーム】プロパティを使用して
この接続のデータ範囲に名前を設定します。


8行目
【.CommandType = xlCmdTable】

QueryTableオブジェクトの
CommandType【コマンドタイプ】プロパティを使用して
実行するデータベースのタイプを指定します。
タイプはXlCmdTypeクラスの定数から設定します。


9行目
【.CommandText = “商品マスター”】

QueryTableオブジェクトの
CommandText【コマンドテキスト】プロパティを使用して
抽出する外部データベースのテーブル名を指定します。


10行目
【.RefreshOnFileOpen = True】

QueryTableオブジェクトの
RefreshOnFileOpen【リフレッシュオンフィルオープン】プロパティに
Trueを指定して、ブックを開くたびに
外部データベースに接続してデータを更新する
設定にします。


11行目
【.Refresh】

QueryTableオブジェクトの
Refresh【リフレッシュ】メソッドを使用して
各プロパティで設定した内容を反映して
QueryTableオブジェクトを更新して
セルにデータを書き込みます。


「外部データ範囲のプロパティ」ダイアログボックスと各プロパティ


※チェックボックスはプロパティに「True」を設定するとチェックされ
「False」を設定するとチェックが外れます。
QueryTableオブジェクトのプロパティの詳細はこちらをご覧ください。


実行結果


以上で
QueryTableを使用して外部データベースを読み込む方法についての
解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク