Excel VBA クエリテーブルオブジェクトのメソッドとプロパティ

スポンサーリンク

クエリテーブルを利用して外部データベースを読み込む方法

外部データベースを読み込むには、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 指定されたテーブル

以上で、クエリテーブルオブジェクトのメソッドとプロパティについての解説を終了します。
ありがとうございました。

スポンサーリンク

関連記事・広告