クエリをVBAで操作・取得する[Excel2016]

Excel2016の「クエリ」をデータベースのテーブルとして「VBA」でデータを取得してエクセルへ表示するサンプルです。クエリをVBAで操作するのは最初は無理かと思いましたが実現できました。

※クエリ系のVBAには一部不具合がある可能性があります。(2015/10/8)

事前準備(クエリの作成)

図のようにエクセルに値を入力してください。

次にA1からB4のセルを選択して「データ」タブの「テーブルから」でクエリを作成します。

※クエリの作成方法の詳細は「テーブルからクエリ/クエリツールの使い方」をご覧ください。

そのクエリの表の下あたりにVBAの「ボタン」(ActiveXコントロール)を作成します。

これで準備完了です。

OLEDBの接続文字列の取得

ワークブックにある「OLE DB」データソースの接続文字列の一覧を取得

Private Sub CommandButton1_Click()
  
  Dim con As WorkbookConnection

  For Each con In ThisWorkbook.Connections ' ワークブックの接続の一覧
    ' 接続文字列の取得
    Debug.Print con & " -" & con.OLEDBConnection.Connection
  Next
End Sub

[結果]

クエリ - テーブル1 -OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=テーブル1;Extended Properties=""

この接続文字列を確認するにはリボンの「データ」の「接続」を開きます。

次に「プロパティ」を押して「定義」タブを押すと表示されます。

接続文字列が同じなのが確認できますね。

OLEDBの接続の更新

ワークブックにある「OLE DB」接続を更新。

Private Sub CommandButton1_Click()
  
  Dim con As WorkbookConnection

  For Each con In ThisWorkbook.Connections
    ' OLE DB接続を更新
    con.OLEDBConnection.Refresh
  Next
End Sub

OLE DBでデータを取得

本来は「OLEDBConnection.ADOConnection」でデータを取得できるはずなのですが、現在では「ADOConnection」は空となっておりデータを取得する事はできません。

どうするかと言いますと自力でADO接続します。ワークブック内のクエリ(データベース)に対してADO経由でデータを取得することになります。

次はコードとなります。

Private Sub CommandButton1_Click()

  Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim adoErr As Object ' ADOエラーコレクション
  Dim SQL As String    ' SQL
  Dim i As Long
    
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

    ' SQL文
  SQL = "SELECT * FROM [テーブル1]"
  
  ' 接続
  adoCon.Open "Provider=Microsoft.Mashup.OleDb.1;Data Source=ファイル名.xlsm;Location=テーブル1;"

  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' 次の行(必須) ※adoRs.Move 1でも良い
  adoRs.MoveNext

  i = 5
  Do Until adoRs.EOF
    Cells(i, 4) = adoRs!NO
    Cells(i, 5) = adoRs!種類

    i = i + 1
    ' 次の行に移動する
    adoRs.MoveNext
  Loop
 
  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    ' ADO系エラー
    For Each adoErr In adoCon.Errors
      Debug.Print adoErr
    Next
  
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (err.Description)
End Sub

結果

まず、21行目の「Execute」メソッドで2-3分エクセルが固まります。
※暗号化などのセキュリティの為か、非常に遅いです。

そして「別のプログラムでのOLEの操作が完了するまで待機します。」というメッセージが表示されます。

OKボタンを押すとVBAが実行されて次のようになります。

D5からE6にクエリのデータが設定されていますね。

ソースコードの解説

18行目の接続文字列は次のようになります。

※「Microsoft.Mashup.OleDb.1」は恐らくクエリ用のプロバイダなのかも知れません。また、DataSourceに$Workbook$と定義しても「接続を更新できませんでした。この問題は、接続が別のブックからコピーされた場合またはブックが新しいバージョンの Excel で作成された場合に発生することがあります。」というエラーになります。

24行目の「MoveNext」で次のレコードに進んでいます。よってデータの一行目が表示されません。

この24行目の「MoveNext」を削除して実行すると「BOFとEOFのいずれかがTrueになっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。」というエラーが表示されます。

削除した24行目の所に次の確認用コードを入力すると

  Debug.Print adoRs.BOF
  Debug.Print adoRs.EOF
  Debug.Print adoRs.RecordCount

BOFはFalse、EOFはFalse、レコード件数は-1と表示されます。

BOF/EOFに異常なく、レコード件数はプロバイダやカーソルによっては-1と表示される為、3つの確認コードには異常がない事となります。

なぜ、24行目の「MoveNext」がないと実行できないのかは不明です。もしかすると「バグ」なのかもしれません。

ちなみに「MoveFirst」「MovePrevious」「MoveLast」「Move 0」のメソッドを使用すると「Restarting a rowset is not supported」というエラーが発生します。SQLの実行をADODB.Connection.ExecuteではなくADODB.Recordset.Openで実行してその引数でカーソルの種類を動的、静的カーソルに切り替えても同様なエラーが発生しました。

まとめ

クエリをデータベースのテーブルとしてVBAでアクセスすると

私のコードに誤りがない場合は、今後「バグ修正」により高速にアクセスして全てのレコードが取得できる可能性があります。

私のコードに誤りがあった場合は ご め ん な さ い :-)

参考サイト(マイクロソフト)

OLEDBConnection メンバー (Excel)
BOF プロパティおよび EOF プロパティ (ADO)
Open メソッド (ADO Connection)
Open メソッド (ADO Recordset)
Execute メソッド (ADO Connection)
RecordCount プロパティ (ADO)
CursorTypeEnum

Excel2016

[初級]
・操作アシスト機能
[グラフ]
・ウォーターフォール
・ツリーマップ
・サンバースト
・ヒストグラム
・パレート図
・箱ひげ図
[予測]
・予測パレット
[クエリ]
・新しいクエリ/クエリの結合・追加
・テーブルからクエリ/クエリツール
・クエリエディター
・クエリとVBA

エクセル講座のクイックリンク

ホーム 新機能 基本(初級) 基本(中級) 基本(上級) 関数 マクロ VBA TIPS

• Twitter
• Facebook
• はてブ

• Pocket
• LINE

関連記事