クエリテーブルを利用して外部データベースを読み込む方法
外部データベースを読み込むには、QueryTable【ｸｴﾘﾃｰﾌﾞﾙ】オブジェクトを使用します。
QueryTable【ｸｴﾘﾃｰﾌﾞﾙ】オブジェクトは、Excelに外部データベースを読み込むために用意されている便利で応用範囲の広いオブジェクトでテキストファイルから各種データベースまで、さまざまなデータを読み込むことができます。
Query【ｸｴﾘ】は「質問する」や「問い合わせ」という意味があり、Table【ﾃｰﾌﾞﾙ】は「表」という意味があります。
QueryTables.Add【ｸｴﾘﾃｰﾌﾞﾙｽﾞｱﾄﾞ】メソッド
ワークシートに外部データベースの読み込み範囲を指定して外部データベースに接続するには
QueryTables【ｸｴﾘﾃｰﾌﾞﾙｽﾞ】コレクションのAdd【ｱﾄ】メソッドを使用します。
QueryTables.Add【ｸｴﾘﾃｰﾌﾞﾙｽﾞｱﾄﾞ】メソッドの書式と設定値(引数)の説明
[]内は省略可能です。
読み込み先のワークシート.QueryTables.Add( Connection, Destination[, Sql] )
- Connection【ｺﾈｸｼｮﾝ】（必須）
接続するデータベースの種類とデータベースの場所を表す
「接続文字列」を指定します。
「接続文字列」は先頭に読み込む対象を表す文字列を指定して、先頭の文字列（読み込む対象）に応じてその後の設定文字列を指定します。「接続文字列」は「&」アンパサンドで連結します。主な読み込む対象を表す先頭文字列 先頭文字列 読み込む対象 後に設定する文字列 OLEDB; 外部データベース プロバイダｰ名,場所を表す接続文字列 URL; Webデータベース 読み込むURLの文字列 TEXT; テキストファイル テキストファイルへのパス文字列 主なプロバイダｰ名 外部データベース プロバイダー名 Access2016/2013/2010/2007 Microsoft.ACE.OLEDB.12.0 Access2003/2002 Microsoft.Jet.OLEDB.4.0 SQLServer SQLOLEDB.1 Oracle MSDAORA - Destination【ﾃﾞｽﾃｨﾈｰｼｮﾝ】（必須）
読み込み結果を返すセル番地をRangeオブジェクトで指定します。 - Sql【ｴｽｷｭｰｴﾙ】（省略可）
抽出したいデータのフィールドをSQLのSELECT文を使用して設定します。省略した場合はすべてのフィールドが抽出されます。
QueryTableオブジェクトを使用したコード例リンク
QueryTableを使用してテキストファイルを読み込む
QueryTableを使用して外部データベースを読み込む
QueryTableを使用してWebページのテーブルを読み込む
QueryTableオブジェクトの主なメソッド一覧
| メソッド | 内容 |
|---|---|
| CancelRefresh 【ｷｬﾝｾﾙﾘﾌﾚｯｼｭ】 | 指定されたクエリ テーブルの バックグラウンド クエリをキャンセルします。 |
| Delete 【ﾃﾞﾘｲﾄ】 | オブジェクトを削除します。 |
| Refresh 【ﾘﾌﾚｯｼｭ】 | 外部データ範囲 (QueryTable) を更新して ワークシートにデータを表示します。 |
| ResetTimer 【ﾘｾｯﾄﾀｲﾏｰ】 | クエリ テーブルの更新間隔を RefreshPeriodプロパティで設定した 最新の値にリセットします。 |
| SaveAsODC 【ｻｰﾌﾞｱｽﾞｵｰﾃﾞｨｰｼｰ】 | クエリテーブルのキャッシュ ソースを Microsoft Office データ接続ファイル として保存します。 |
QueryTableオブジェクトの主なプロパティ一覧
| プロパティ | 内容 |
|---|---|
| AdjustColumnWidth 【ｱﾄﾞｼﾞｬｽﾄｶﾗﾑﾜｲｽﾞ】 | Falseの場合は 更新ごとに列幅を自動調節しません。 既定値はTrueです。 |
| BackgroundQuery 【ﾊﾞｯｸｸﾞﾗｳﾝﾄﾞｸｴﾘ】 | Trueの場合、クエリ テーブルのクエリを バックグラウンド (非同期) で実行します。 既定値はFalseで実行しません。 |
| CommandText 【ｺﾏﾝﾄﾞﾃｷｽﾄ】 | 指定されたデータ ソースで使用する コマンド文字列を取得または設定します。 バリアント型 (Variant) の値を使用します。 |
| CommandType 【ｺﾏﾝﾄﾞﾀｲﾌﾟ】 | XlCmdTypeクラスの定数を指定します。 |
| FieldNames 【ﾌｨｰﾙﾄﾞﾈｰﾑｽﾞ】 | Falseを指定した場合は データソースのフィールド名を 見出しとして使用しません。 既定値はTrueで使用します。 |
| FillAdjacentFormulas 【ﾌｨﾙｱﾄﾞｼﾞｪｾﾝﾄﾌｫｰﾐｭﾗｽﾞ】 | Trueの場合、指定された クエリ テーブルの右側の数式を 自動的に更新します。 既定値はFalseで更新されません。 |
| Name 【ﾈｰﾑ】 | オブジェクトの名前を 取得または設定します。 |
| Parameters 【ﾊﾟﾗﾒｰﾀｰｽﾞ】 | クエリ テーブルのパラメーターを表す Parametersコレクションを返します。 値の取得のみ可能です。 |
| PostText 【ﾎﾟｽﾄﾃｷｽﾄ】 | Web サーバーにデータを入力して Web クエリからデータを返す ポスト メソッドで使用する文字列を設定します。 |
| PreserveColumnInfo 【ﾌﾟﾚｻｰﾌﾞｶﾗﾑｲﾝﾌｫ】 | Falseの場合、クエリ テーブルが更新されるごとに 列の並べ替え、フィルター設定、および レイアウトに関する情報を保存しません。 既定値はTrueで情報は保存されます。 |
| PreserveFormatting 【ﾌﾟﾚｻｰﾌﾞﾌｫﾏｯﾃｨﾝｸﾞ】 | Falseの場合、クエリ テーブルに最後に適用した オートフォーマットがクエリ テーブルの 新しい行のデータに適用されます。 既定値はTrueで先頭の 5 行のデータに 共通する書式をクエリ テーブルの 新しい行のデータに適用します。 |
| QueryType 【ｸｴﾘﾀｲﾌﾟ】 | クエリ テーブルを作成するために Excel で使用されるクエリの種類を返します XlQueryTypeクラスの定数を使用します。 |
| Recordset 【ﾚｺｰﾄﾞｾｯﾄ】 | 指定されたクエリ テーブルの データ ソースとして使用する Recordsetオブジェクトを 取得または設定します。 |
| Refreshing 【ﾘﾌﾚｯｼﾝｸﾞ】 | 指定されたクエリ テーブルに対する バックグラウンド クエリが実行中である場合 このプロパティの値はTrueです。 値の取得のみ可能です。 |
| RefreshOnFileOpen 【ﾘﾌﾚｯｼｭｵﾝﾌｧｲﾙｵｰﾌﾟﾝ】 | Trueの場合、ブックを開くたびに ピボットテーブルのキャッシュまたは クエリ テーブルを自動的に更新します。 既定値はFalseで自動更新しません。 |
| RefreshPeriod 【ﾘﾌﾚｯｼｭﾋﾟﾘｱﾄﾞ】 | 更新間隔を分単位で取得または設定します。 長整数型 (Long) の値を使用しますが 設定値は0～32767の整数値です。 |
| RefreshStyle 【ﾘﾌﾚｯｼｭｽﾀｲﾙ】 | 指定されたワークシートで行を 追加または削除して、クエリが返す レコードセットの行数を受け取る方法を設定します。 XlCellInsertionModeクラスの定数を使用します。 |
| ResultRange 【ﾘｿﾞﾙﾄﾚﾝｼﾞ】 | 指定されたクエリ テーブルが 占有するワークシートの領域を表す? Rangeオブジェクトを返します。 値の取得のみ可能です。 |
| RobustConnect 【ﾛﾊﾞｽﾄｺﾈｸﾄ】 | クエリ テーブルをデータ ソースに 接続する方法を取得または設定します。 XlRobustConnectクラスの定数を使用します。 |
| RowNumbers 【ﾛｳﾅﾝﾊﾞｰｽﾞ】 | Trueの場合、行番号は指定された クエリ テーブルの最初の列として追加されます。 既定値はFalseで行番号は追加されません。 |
| SaveData 【ｻｰﾌﾞﾃﾞｰﾀ】 | Trueの場合、ピボットテーブル レポートの データをブックと共に保存します。 既定値はFalseでレポートの 定義のみを保存します。 |
| SavePassword 【ｻｰﾌﾞﾊﾟｽﾜｰﾄﾞ】 | Trueの場合、ODBC 接続文字列の パスワード情報は指定された クエリと共に保存されます。 既定値はFalseでパスワードは削除されます。 |
| Sort 【ｿｰﾄ】 | クエリ テーブルの範囲に対する 並べ替え条件を返します。 値の取得のみ可能です。 |
| SourceConnectionFile 【ｿｰｽｺﾈｸｼｮﾝﾌｧｲﾙ】 | クエリテーブルの作成に使用された Microsoft Office のデータ接続ファイル または同様のファイルを示す 文字列型 (String) の値を取得または設定します。 |
| SourceDataFile 【ｿｰｽﾃﾞｰﾀﾌｧｲﾙ】 | クエリ テーブルのソース データ ファイルを 表す文字列型 (String) の値を 取得または設定します。 |
| TextFileColumnDataTypes 【ﾃｷｽﾄﾌｧｲﾙｶﾗﾑﾃﾞｰﾀﾀｲﾌﾟ】 | テキスト ファイルをクエリ テーブルに インポートするときに ファイルの各列に適用されるデータ型を 指定する定数を XlColumnDataType 列挙型の定数または 値を使用して、配列形式で設定します。 |
| TextFileCommaDelimiter 【ﾃｷｽﾄﾌｧｲﾙｺﾏﾝﾄﾞﾃﾞﾘﾐﾀ】 | Trueの場合、クエリ テーブルに テキスト ファイルをインポートするときに カンマを区切り文字に使用します。 既定値はFalseで区切り文字に 別の文字を使用します。 |
| TextFileConsecutiveDelimiter 【ﾃｷｽﾄﾌｧｲﾙｺﾝｾｷｭﾃｨﾌﾞﾃﾞﾘﾐﾀ】 | Trueの場合、テキスト ファイルを クエリ テーブルにインポートするときに 連続する区切り文字を 1 つの区切り文字として扱います。 既定値はFalseです。 |
| TextFileDecimalSeparator 【ﾃｷｽﾄﾌｧｲﾙﾃﾞｼﾏﾙｾﾊﾟﾚｰﾀ】 | テキスト ファイルをクエリ テーブルに インポートするときに、Excel で使用される 小数点の記号を設定します。 既定値はシステムの小数点記号です。 |
| TextFileFixedColumnWidths 【ﾃｷｽﾄﾌｧｲﾙﾌｨｯｸｽﾄﾞｶﾗﾑﾜｲｽﾞ】 | クエリ テーブルにインポートする テキスト ファイルでの列の幅 (文字数) に 対応した数を配列として設定します。 有効な文字の幅の範囲は 1 ～ 32767 の値です。 |
| TextFileOtherDelimiter 【ﾃｷｽﾄﾌｧｲﾙｵｰｻﾞｰﾃﾞﾘﾐﾀ】 | テキスト ファイルをクエリ テーブルに インポートするときに、区切り文字として 使用される文字を設定します。 既定値はNullです。 |
| TextFileParseType 【ﾃｷｽﾄﾌｧｲﾙﾊﾟｰｽﾀｲﾌﾟ】 | クエリ テーブルにインポートする テキスト ファイルでのデータの列形式を 取得または設定します。 XlTextParsingTypeクラスの定数を使用します。 |
| TextFilePlatform 【ﾃｷｽﾄﾌｧｲﾙﾌﾟﾗｯﾄﾌｫｰﾑ】 | クエリ テーブルにインポートする テキスト ファイルのプラットフォームを設定します。 このプロパティにより データ インポート時に使用される コード ページが決定されます。 XlPlatformクラスの定数を使用します。 |
| TextFilePromptOnRefresh 【ﾃｷｽﾄﾌｧｲﾙﾌﾟﾛﾝﾌﾟﾄｵﾝﾘﾌﾚｯｼｭ】 | Trueの場合、クエリ テーブルが 更新されるたびにインポートする テキスト ファイル名を指定します。 [テキスト ファイルのインポート] ダイアログ ボックスでパスと ファイル名を指定します。 既定値はFalseです。 |
| TextFileSemicolonDelimiter 【ﾃｷｽﾄﾌｧｲﾙｾﾐｺﾛﾝﾃﾞﾘﾐﾀ】 | Trueの場合、テキスト ファイルを クエリ テーブルにインポートするときに TextFileParseTypeプロパティが xlDelimitedの場合は 区切り記号にセミコロンを設定します。 既定値はFalseです。 |
| TextFileSpaceDelimiter 【ﾃｷｽﾄﾌｧｲﾙｽﾍﾟｰｽﾃﾞﾘﾐﾀ】 | Trueの場合、テキスト ファイルを クエリ テーブルにインポートするときの 区切り文字にスペースを設定します。 既定値はFalseです。 |
| TextFileStartRow 【ﾃｷｽﾄﾌｧｲﾙｽﾀｰﾄﾛｳ】 | テキスト ファイルをクエリ テーブルに インポートするときに、テキストの 区切りを開始する行番号を設定します。 使用できる範囲は、1 ～ 32767 の整数です。 既定値は 1 です。長整数型 の値を使用します。 |
| TextFileTabDelimiter 【ﾃｷｽﾄﾌｧｲﾙﾀﾌﾞﾃﾞﾘﾐﾀ】 | Trueの場合、テキスト ファイルを クエリ テーブルにインポートするときの 区切り文字にタブ記号を設定します。 既定値はFalseです。 |
| TextFileTextQualifier 【ﾃｷｽﾄﾌｧｲﾙｸｵﾙﾌｧｲﾗ】 | テキスト ファイルをクエリ テーブルに インポートするときの文字列の 引用符を設定します。 引用符を使用することにより 囲まれたデータがテキスト形式で あることが示されます。 XlTextQualifierクラスの定数を使用します。 |
| TextFileThousandsSeparator 【ﾃｷｽﾄﾌｧｲﾙｻｳｻﾞﾝｽﾞｾﾊﾟﾚｰﾀ】 | テキスト ファイルをクエリ テーブルに インポートするときに、Excel で使用する 桁区切り記号を設定します。 既定値はシステムの桁区切り記号です。 |
| TextFileTrailingMinusNumbers 【ﾃｷｽﾄﾌｧｲﾙﾄﾚﾘﾝｸﾞﾏｲﾅｽﾅﾝﾊﾞｰｽﾞ】 | Trueの場合、テキストとして インポートされた数値が マイナス 記号で 始まるとき、それを負符号として扱います。 Falseの場合、テキストとして インポートされた数値が マイナス記号で 始まるとき、それをテキストとして扱います。 |
| TextFileVisualLayout 【ﾃｷｽﾄﾌｧｲﾙﾋﾞｼﾞｭｱﾙﾚｲｱｳﾄ】 | インポートするテキストの 表示の方向を左から右とするか 右から左とするかを指定する XlTextVisualLayoutType列挙体 を取得または設定します。 |
| WebConsecutiveDelimitersAsOne 【ｳｨﾌﾞｺﾝｾｸﾃｨﾌﾞﾃﾞﾘﾐﾀｰｽﾞｱｽﾞﾜﾝ】 | Trueの場合、Web ページの HTML ＜pre＞タグ内のデータを クエリ テーブルにインポートするときに そのデータが複数の列に区切られる場合は 連続する区切り文字を 1 つの区切り文字として使用します。 Falseの場合、連続する区切り文字を 複数の区切り文字として扱います。 既定値はTrueです。 |
| WebDisableDateRecognition 【ｳｨﾌﾞﾃﾞｨｾﾝﾎﾞｩﾃﾞｰﾀﾚｺｼﾞﾈｰｼｮﾝ】 | Trueの場合、Web ページを クエリ テーブルにインポートするときに 日付形式のデータをテキストとして解析します。 Falseの場合、日付認識を有効にします。 既定値はFalseです。 |
| WebDisableRedirections 【ｳｪﾌﾞﾃﾞｨｾﾝﾎﾞｩﾚｺｼﾞﾈｰｼｮﾝ】 | Trueの場合、QueryTableオブジェクトで Web クエリのリダイレクトが 無効になっています。 既定値はFalseです。 |
| WebFormatting 【ｳｨﾌﾞﾌｫｰﾏｯﾃｲﾝｸﾞ】 | Web ページをクエリ テーブルに インポートするときに どれだけの書式を適用するかを 示す値を設定します。 XlWebFormattingクラスの定数を使用します。 |
| WebPreFormattedTextToColumns 【ｳｨﾌﾞﾊﾟｰﾌｫﾏｯﾃｯﾄﾞﾃｷｽﾄﾄｩｶﾗﾑｽ】 | Web ページをクエリ テーブルに インポートするときに Web ページの HTML ＜pre＞ タグ内にある データを列に区切るかどうかを設定します。 既定値はTrueです。 |
| WebSelectionType 【ｳｨﾌﾞｾﾚｸｼｮﾝﾀｲﾌﾟ】 | クエリ テーブルに Web ページの すべてのテーブルをインポートするか Web ページの特定のテーブルのみを インポートするかを示す値を設定します。 XlWebSelectionTypeクラスの定数を使用します。 |
| WebSingleBlockTextImport 【ｳｨﾌﾞｼﾝｸﾞﾙﾌﾞﾛｰｸﾃｷｽﾄｲﾝﾎﾟｰﾄ】 | Trueの場合、指定された Web ページを クエリ テーブルにインポートするときに そのページの HTML ＜pre＞ タグ内にある データを一括処理します。 Falseの場合、データは連続する行から成る ブロックにインポートされ、タイトル行を ブロックの先頭として認識します。 既定値はFalseです。 |
| WebTables 【ｳｨﾌﾞﾃｰﾌﾞﾙｽﾞ】 | Web ページをクエリ テーブルに インポートするときに 表の名前やインデックス番号のカンマ区切り リストを取得または設定します。 文字列型 (String) の値を使用します。 |
| WorkbookConnection 【ﾜｰｸﾌﾞｯｸｺﾈｸｼｮﾝ】 | クエリ テーブルが使用する WorkbookConnectionオブジェクトを返します。 値の取得のみ可能です。 |
XlCmdTypeクラスの定数一覧表
| 定数 | 値 | 説明 |
|---|---|---|
| xlCmdCube | 1 | OLAP データ ソースで使用される キューブ名が含まれます。 |
| xlCmdSql | 2 | SQL ステートメントが含まれます。 |
| xlCmdTable | 3 | OLE DB データ ソースにアクセスする テーブル名が含まれます。 |
| xlCmdDefault | 4 | OLE DB プロバイダーが認識する コマンド テキストが含まれます。 |
| xlCmdList | 5 | データを一覧表示するための ポインターが含まれます。 |
| xlCmdTableCollection | 6 | テーブル コレクションの名前が含まれます。 |
| xlCmdExcel | 7 | Excel の式が含まれます。 |
| xlCmdDAX | 8 | Data Analysis Expressions (DAX) の 式が含まれます。 |
XlQueryTypeクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlODBCQuery | 1 | ODBC データ ソース |
| xlDAORecordset | 2 | DAO レコードセット クエリ (クエリ テーブル専用) |
| xlWebQuery | 4 | Web ページ (クエリ テーブル専用) |
| xlOLEDBQuery | 5 | OLE DB クエリ (OLAP データ ソースなど) |
| xlTextImport | 6 | テキスト ファイル (クエリ テーブル専用) |
| xlADORecordset | 7 | ADO レコードセット クエリ |
XlCellInsertionModeクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlOverwriteCells | 0 | ワークシートに新しいセルまたは行を追加しません。 オーバーフローしないように 周囲のセルのデータに上書きします。 |
| xlInsertDeleteCells | 1 | 新しいレコードセットに必要な行数と正確に 一致するように、一部の行を 挿入または削除します。 |
| xlInsertEntireRows | 2 | 必要な場合、オーバーフローしないように 行全体を挿入します。 ワークシートからセルまたは行を削除しません。 |
XlRobustConnectクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlAsRequired | 0 | 再接続する外部ソース情報を使用して接続します。 |
| xlAlways | 1 | 常に外部ソース情報を使用して再接続します。 |
| xlNever | 2 | 再接続するソース情報を使用することはありません。 |
XlColumnDataType 列挙型の定数
| 定数 | 値 | 内容 |
|---|---|---|
| xlGeneralFormat | 1 | 標準（既定値） |
| xlTextFormat | 2 | 文字列 |
| xlMDYFormat | 3 | MDY（月日年）日付形式 |
| xlDMYFormat | 4 | DMY（日月年）日付形式 |
| xlYMDFormat | 5 | YMD（年月日）日付形式 |
| xlMYDFormat | 6 | MYD（月年日）日付形式 |
| xlDYMFormat | 7 | DYM（日年月）日付形式 |
| xlYDMFormat | 8 | YDM（年日月）日付形式 |
| xlSkipColumn | 9 | この列は読み込まれません |
| xlEMDFormat | 10 | EMD（台湾年月日）日付形式 |
XlTextParsingTypeクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlDelimited | 1 | 区切り文字によってファイルが区切られます。（既定値） |
| xlFixedWidth | 2 | ファイルのデータが固定幅の列に配置されます。 |
XlPlatformクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlMacintosh | 1 | Macintosh |
| xlWindows | 2 | Microsoft Windows |
| xlMSDOS | 3 | MS-DOS |
XlTextQualifierクラスの定数一覧
| 定数 | 値 | 説明 |
|---|---|---|
| xlTextQualifierDoubleQuote | 1 | 二重引用符 (“) |
| xlTextQualifierSingleQuote | 2 | 一重引用符 (‘) |
| xlTextQualifierNone | -4142 | 引用符なし |
XlTextVisualLayoutType列挙体の定数一覧
| 定数 | 値 | 内容 |
|---|---|---|
| xlTextVisualLTR | 1 | 左から右 |
| xlTextVisualRTL | 2 | 右から左 |
XlWebFormattingクラスの定数
| 定数 | 値 | 内容 |
|---|---|---|
| xlWebFormattingAll | 1 | すべての書式設定がインポートされます。 |
| xlWebFormattingRTF | 2 | リッチ テキスト形式と互換性のある書式設定がインポートされます。 |
| xlWebFormattingNone | 3 | 書式設定はインポートされません。 |
XlWebSelectionTypeクラスの定数
| 定数 | 値 | 内容 |
|---|---|---|
| xlEntirePage | 1 | ページ全体 |
| xlAllTables | 2 | すべてのテーブル |
| xlSpecifiedTables | 3 | 指定されたテーブル |
以上で、クエリテーブルオブジェクトのメソッドとプロパティについての解説を終了します。
ありがとうございました。