エクセルVBAのQueryTables.Addメソッドでクエリテーブルを作成する方法


querytable
みなさん、こんにちは!
フジタニ(@libartweb)です。

エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法をシリーズでお伝えしていきます。

前回の記事はこちら

エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法
エクセルVBAを使用したCSVの取り込みは、ループを使用した方法が一般的です。今回紹介するQuertyTableオブジェクトはループを一切使用せずしかも爆速でCSVの取り込みが可能です。概要を説明します。

一般的に使われている、ループを使用したCSVの取り込みの他に、QueryTableオブジェクトを使用して、高速にCSVを取り込む方法があることをお伝えしました。

QueryTableオブジェクトの概要と、その速さを少しはご理解いただけたと思います。

QueryTableオブジェクトにはたくさんのメソッドやプロパティが存在しますので、CSV取り込みでよく使うもののみを取り上げて行きます。

今回はまず最初にエクセルVBAにクエリテーブルを作成する方法を解説します!

QueryTableオブジェクトでCSVを取り込む方法

QueryTableオブジェクトでCSVを取り込む場合は、クエリテーブルの作成が必要です。

クエリテーブルとは

Microsoftの公式ページでは下記の通り解説されています。

Excel では、新しいデータベース クエリを作成し、データをそのワークシートに返した際、ワークシート内にクエリ テーブルが作成されます。

Excel のクエリ テーブルで動的レポートを作成する

「クエリテーブルの作成」とは、エクセルと外部ファイル(今回はCSV)をリンクさせることです。

リンクすることでWorksheet上のクエリテーブルにCSVが取り込まれます。このクエリテーブルは、仮想のテーブルで、実際にセルなどには表示されない見えないテーブルです。

それでは、クエリテーブルを作成する方法を解説していきます。

QueryTables.Addメソッドとは

最初にサンプルコードを。

Worksheetオブジェクト.QueryTables

クエリテーブルを作成するには、WorksheetオブジェクトのQueryTablesプロパティを使って、QueryTableオブジェクトの集まりであるQueryTablesコレクションを参照し、これをオブジェクトとして扱います。

QueryTablesコレクション.Add(Connection,Destination,Sql)

QueryTablesコレクションのAddメソッドを使用して、WorksheetにQueryTableオブジェクトを追加します。

Addメソッドにより、QueryTableオブジェクトが返ります。追加されたQueryTableオブジェクトに対して、CSVの型など様々なプロパティを指定します。(プロパティは次回以降の記事で紹介します)

  1. 新しいクエリテーブルを作成
  2. クエリテーブルにCSVを取り込む
  3. 出力先のセル位置を取得する

以上の流れを実現するメソッドです。図で説明すると下記の通りです。
QueryTables

QueryTables.Addメソッドの引数

引数は3つあります。そのうちのConnectionとDestinationについて解説していきます。Sqlはクエリ文字列を指定しますが、CSVの取り込みでは使用しないので省略します。

引数 内容
Connection 取り込むCSVのパス
Destination 取り込み先セル位置
Sql クエリ文字列

ConnectionでCSVのパスを指定する

CSVのパス名を指定しますが、CSVを取り込む場合は必ず「TEXT;」から記載します。CSVはテキストファイルの一種だからです。「TEXT;」は、エクセルの画面上だと以下の箇所を意味します。

テキストファイル取り込み

TEXT;に続いてファイルのパスを指定します。これで、どのCSVを取り込むか?の指定が完了します。

Connectionはエクセルの画面上だと以下のファイル選択ダイアログを表します。

connection

Destinationで出力先セルを指定する

Destinationは、取り込むCSVファイルをどのWorksheetのどのセル位置を開始位置として取り込むか?を指定できます。

ここで指定するWorksheetは、クエリテーブルを作成したWorksheetじゃないとエラーになります。

サンプルコードではクエリテーブルの作成先、CSVの出力先両方にSheet1オブジェクトが指定されていますが、これはイコールになっている必要があります。

異なるシートを指定すると以下のようなエラーとなりますのでご注意ください。
エラー

ここで指定したセル位置への出力は、次回以降紹介するQueryTable.Refreshメソッドで行います。

この段階ではセル位置を取得するだけです。

実行結果

では実行結果を見ていきましょう。

今回はConnectionで取り込むCSVのパスを指定、Destinationで出力先のセルを指定しました。

イミディエイトウィンドウで、正しく取り込まれたかを確認します。

※サンプルコードを実行した結果です。

実行結果

CSVのパス、出力先セル(B1セルを指定していたので、1行目2列目です)ともに正しく取り込まれていますね!

まとめ・次回予告

以上、クエリテーブルを作成して、そこにCSVを取り込む方法と出力先のセル位置を取得する方法をお伝えしました。

次回はクエリテーブルに取り込んだCSVを、指定したセル位置に出力するQueryTable.Refreshメソッドについて解説します!

どうぞ、お楽しみに!

連載目次:エクセルVBAのQueryTableで高速にCSVを取り込む

一般的なループを使用したCSV取り込みよりも、更に高速にCSVを取り込むことのできるQueryTableオブジェクトに関する連載です。サイズの大きなCSVを取り込む際に非常に便利です。
  1. エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法
  2. エクセルVBAのQueryTables.Addメソッドでクエリテーブルを作成する方法
  3. エクセルVBAのQueryTable.RefreshメソッドでCSVをシートに出力する方法
  4. エクセルVBAのQueryTableオブジェクトのプロパティの基本とCSVをカンマ区切りする方法
  5. エクセルVBAのQueryTableオブジェクトであらゆる種類のCSVを取り込む方法
  6. エクセルVBAで高速にCSVを取り込むQueryTableオブジェクトの最終処理
  7. エクセルVBAでCSVを高速に取り込むQueryTableオブジェクト使用時の注意点

  投稿者プロフィール

フジタニ
フジタニLibArt代表
手作業でやってるものは何でも自動化したくなる性格。毎日VBAで何でもかんでも自動化しています。
電話が大嫌いなのでビジネスチャットを布教したい!
海外へよく行きます。自転車が大好き。走れるエンジニアです!

コメント