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/2007Microsoft.ACE.OLEDB.12.0
    Access2003/2002Microsoft.Jet.OLEDB.4.0
    SQLServerSQLOLEDB.1
    OracleMSDAORA

  • 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クラスの定数一覧表

定数説明
xlCmdCube1OLAP データ ソースで使用される
キューブ名が含まれます。
xlCmdSql2SQL ステートメントが含まれます。
xlCmdTable3OLE DB データ ソースにアクセスする
テーブル名が含まれます。
xlCmdDefault4OLE DB プロバイダーが認識する
コマンド テキストが含まれます。
xlCmdList5データを一覧表示するための
ポインターが含まれます。
xlCmdTableCollection6テーブル コレクションの名前が含まれます。
xlCmdExcel7Excel の式が含まれます。
xlCmdDAX8Data Analysis Expressions (DAX) の
式が含まれます。

XlQueryTypeクラスの定数一覧

定数説明
xlODBCQuery1ODBC データ ソース
xlDAORecordset2DAO レコードセット クエリ (クエリ テーブル専用)
xlWebQuery4Web ページ (クエリ テーブル専用)
xlOLEDBQuery5OLE DB クエリ (OLAP データ ソースなど)
xlTextImport6テキスト ファイル (クエリ テーブル専用)
xlADORecordset7ADO レコードセット クエリ

XlCellInsertionModeクラスの定数一覧

定数説明
xlOverwriteCells0ワークシートに新しいセルまたは行を追加しません。
オーバーフローしないように
周囲のセルのデータに上書きします。
xlInsertDeleteCells1新しいレコードセットに必要な行数と正確に
一致するように、一部の行を
挿入または削除します。
xlInsertEntireRows2必要な場合、オーバーフローしないように
行全体を挿入します。
ワークシートからセルまたは行を削除しません。

XlRobustConnectクラスの定数一覧

定数説明
xlAsRequired0再接続する外部ソース情報を使用して接続します。
xlAlways1常に外部ソース情報を使用して再接続します。
xlNever2再接続するソース情報を使用することはありません。

XlColumnDataType 列挙型の定数

定数内容
xlGeneralFormat1標準(既定値)
xlTextFormat2文字列
xlMDYFormat3MDY(月日年)日付形式
xlDMYFormat4DMY(日月年)日付形式
xlYMDFormat5YMD(年月日)日付形式
xlMYDFormat6MYD(月年日)日付形式
xlDYMFormat7DYM(日年月)日付形式
xlYDMFormat8YDM(年日月)日付形式
xlSkipColumn9この列は読み込まれません
xlEMDFormat10EMD(台湾年月日)日付形式

XlTextParsingTypeクラスの定数一覧

定数説明
xlDelimited1区切り文字によってファイルが区切られます。(既定値)
xlFixedWidth2ファイルのデータが固定幅の列に配置されます。

XlPlatformクラスの定数一覧

定数説明
xlMacintosh1Macintosh
xlWindows2Microsoft Windows
xlMSDOS3MS-DOS

XlTextQualifierクラスの定数一覧

定数説明
xlTextQualifierDoubleQuote1二重引用符 (“)
xlTextQualifierSingleQuote2一重引用符 (‘)
xlTextQualifierNone-4142引用符なし

XlTextVisualLayoutType列挙体の定数一覧

定数内容
xlTextVisualLTR1左から右
xlTextVisualRTL2右から左

XlWebFormattingクラスの定数

定数内容
xlWebFormattingAll1すべての書式設定がインポートされます。
xlWebFormattingRTF2リッチ テキスト形式と互換性のある書式設定がインポートされます。
xlWebFormattingNone3書式設定はインポートされません。

XlWebSelectionTypeクラスの定数

定数内容
xlEntirePage1ページ全体
xlAllTables2すべてのテーブル
xlSpecifiedTables3指定されたテーブル

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

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

フォローする

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