ExcelVBA～パワークエリの更新に使えるかは不明だけど、QueryTableのAfterRefreshイベントを使うには？もしかしたら、「QueryTable.Refreshing プロパティ」で、DoEventsでループするチェック方法の代用になるかも？～「イベントを持つオブジェクト」なのに、でもVBEにイベント表示されない場合、そのオブジェクトのイベントを発生させる方法～ | 無駄なお金と時間がない方・零細企業様・独立したい方・個人事業主の方・営業さん・経理さん・学生さん・就活生さん・小学生のお子様、中高生の子たちのための、　　　　「小さな会社の兼任なんちゃって社内SE・パソコン管理術・の基礎知識」

ExcelVBA～パワークエリの更新に使えるかは不明だけど、QueryTableのAfterRefreshイベントを使うには？もしかしたら、「QueryTable.Refreshing プロパティ」で、DoEventsでループするチェック方法の代用になるかも？～「イベントを持つオブジェクト」なのに、でもVBEにイベント表示されない場合、そのオブジェクトのイベントを発生させる方法～
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
●コードを（クラスモジュールではなくて）ブックモジュールやシートモジュールに書いた場合の事例の図

※補足
パワークエリの更新に使えるかは不明だけど、パワークエリの結果の表は「テーブル機能」の形式で、その中にQueryTableオブジェクトが埋め込まれています。MicrosoftQueryも同様です。
そこまで検証していませんが、もしかしたら、それらのイベントも補足できるかも？
ダメ元で試してみてください。
使えるとすごくいいですよね。パワークエリも更新が完了するまで待てる？かも？
特に基本的なVBAでの更新技がうまくいかないときとか？？
わかんないですけど・・・。

　　
以下本文です。
　　

QueryTableオブジェクトは、AfterRefreshとBeforeRefreshという２つのイベントを持っています。
でも、VBA画面には、QueryTableオブジェクトをシートに生成したとしても、そのイベントがプロシージャボックスに表示されません。
（VBEの各部名称は以下のURLをご参考に。http://infoseek_rip.g.ribbon.to/t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_page03.htm）

なので、ここでは、
『「イベントを持つオブジェクト」なのに、でもVBEにイベント表示されない場合、そのオブジェクトのイベントを発生させる方法』
のヒントをご紹介したいと思います。

大別して、
(01)クラスモジュールにコードを書く方法と、
(02)ブックモジュールやシートモジュールに書く方法の
２つがあります。
標準モジュールには、書いても書いたそばからエラー表示になるので作れません。

ブックモジュールやシートモジュールはクラスモジュールに近い性格を持つのでエラーにはなりません。

ブックモジュールやシートモジュールのグローバル変数として、（WithEvents を必ず使用して）、オブジェクト変数を宣言すると、オブジェクトとして認識してもらえるようにで、そうなると、自動的にそのオブジェクトのイベントが使えるようになるようです。

『グローバル変数として、（WithEvents を必ず使用して）、オブジェクト変数を宣言する』とおいうところが、クラスモジュールの場合と、ブックモジュールやシートモジュールの場合とで異なってきます。
後述の「Sub Initialize_It()」をどこで実行するかなども変わってきます。

なお、QueryTable.AfterRefresh イベントは、
もしかしたら、「QueryTable.Refreshing プロパティ」で、DoEventsでループするチェック方法の代用になるかも？

また、BeforeRefreshイベントもあるので、何かに使えるかも。

●「QueryTable.AfterRefresh イベント」の使い方

https://www.excel.studio-kazu.jp/kw/20021112092707.html
にあるようにクラスモジュールを使う方法がひとつ。

あとはクラスモジュールを使わなくても、上記のようなコードとほぼ同じ内容で、
シートモジュールやブックモジュールでやっても同じことができるようです。

　　
例えば、Sheet1シートにQueryTableオブジェクトが「１つ」だけ生成されている場合、
Sheet1モジュール、あるいは、他のシートモジュール、あるいは、ブックモジュールに、
（つまり標準モジュールとクラスモジュール以外に）
以降のように少し内容を変えて書くと、QueryTableオブジェクトのAfterRefreshイベントを発生させ、動かすことができます。

（※注：当然ですが、本記事のサンプルは既に「Sheet1シートにQueryTableオブジェクトが「１つ」だけ生成」されていないと動きません。生成されていない場合の分岐も作ってないので。クラスモジュールの場合もブックモジュールやシートモジュールの場合もです。）

'
'

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 Public WithEvents qt2 As QueryTable   '標準モジュールに「WithEvents」を書くとエラーになる。
                                       'シートモジュールやブックモジュールではエラーにならない。
                                       'シートモジュールやブックモジュールはクラスモジュールに近い性質を持つので。
                                       '標準モジュールだけ、ちょっと性質が異なるのでエラーになります。
 

' Dim X As New Class1                   'シートモジュールやブックモジュールでは不要。
'                                       'クラスモジュールで使うコードです。



'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'Sheet1のQueryTableオブジェクトを更新する前に、以下のコードを実行して、
'そのSheet1のQueryTableオブジェクトをグローバル変数のオブジェクト変数に代入して、
'「ファイルが開いている間中は常時存在している」オブジェクトと化します。

Sub Initialize_It()
'     Set X.qt = ThisWorkbook.Sheets("Sheet1").QueryTables(1)  'クラスモジュールではこちらのコードが必要。シートモジュールやブックモジュールでは不要。
     Set qt2 = ThisWorkbook.Sheets("Sheet1").QueryTables(1)   'シートモジュールやブックモジュールではこちらのコードが必要。 クラスモジュールでは不要。
 End Sub



'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'Sheet1のQueryTableオブジェクトが更新されると、グローバル変数宣下の「WithEvents」により、
'このプロシージャ（＝イベントプロシージャ）を通る。
'  ＝「AfterRefresh」イベントが発生して、そのイベントのコードが実行される。
'もしかしたら、「QueryTable.Refreshing プロパティ」で、DoEventsでループするチェック方法の代用になるかも？
 
 Private Sub qt2_AfterRefresh(ByVal Success As Boolean)
     If Success Then
         MsgBox "クエリが正常に終了しました｡"
     Else
         MsgBox "クエリが失敗したか､またはキャンセルされました｡
     End If
 End Sub



'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


'
'

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Public WithEvents qt2 As QueryTable '標準モジュールに「WithEvents」を書くとエラーになる。

'シートモジュールやブックモジュールではエラーにならない。

'シートモジュールやブックモジュールはクラスモジュールに近い性質を持つので。

'標準モジュールだけ、ちょっと性質が異なるのでエラーになります。

' Dim X As New Class1 'シートモジュールやブックモジュールでは不要。

' 'クラスモジュールで使うコードです。

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

'Sheet1のQueryTableオブジェクトを更新する前に、以下のコードを実行して、

'そのSheet1のQueryTableオブジェクトをグローバル変数のオブジェクト変数に代入して、

'「ファイルが開いている間中は常時存在している」オブジェクトと化します。

Sub Initialize_It()

' Set X.qt = ThisWorkbook.Sheets("Sheet1").QueryTables(1) 'クラスモジュールではこちらのコードが必要。シートモジュールやブックモジュールでは不要。

Set qt2 = ThisWorkbook.Sheets("Sheet1").QueryTables(1) 'シートモジュールやブックモジュールではこちらのコードが必要。クラスモジュールでは不要。

End Sub

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

'Sheet1のQueryTableオブジェクトが更新されると、グローバル変数宣下の「WithEvents」により、

'このプロシージャ（＝イベントプロシージャ）を通る。

' ＝「AfterRefresh」イベントが発生して、そのイベントのコードが実行される。

'もしかしたら、「QueryTable.Refreshing プロパティ」で、DoEventsでループするチェック方法の代用になるかも？

Private Sub qt2_AfterRefresh(ByVal Success As Boolean)

If Success Then

MsgBox "クエリが正常に終了しました｡"

Else

MsgBox "クエリが失敗したか､またはキャンセルされました｡

End If

End Sub

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

●実行方法

まずQueryTableオブジェクトをSheet1に作成します。
その後、「Sub Initialize_It()」を実行します。
（これを実行しないとイベントが使えません）

すると、ファイルが開いている間はメモリ内に存在するオブジェクトとして「Sheet1のQueryTableオブジェクト」が設定されるので、あとは、
「Sheet1のQueryTableオブジェクト」を右クリックして「更新」メニューを押します。

すると、正常に更新が完了すれば、「"クエリが正常に終了しました｡"」とメッセージ表示されます。

「Sub Initialize_It()」は、クラスモジュールにコードを書いた場合は、
もしQueryTableオブジェクトが（いったん作成したら当分のあいだ削除せずに）いつもよく使うものであるなら、標準モジュールのAuto_Openプロシージャや、ブックモジュールのWorkbook_Open()プロシージャなどに書くと、ファイルを開いたときに自動実行されるのでラクになると思います。

で、以上の、コードを（クラスモジュールではなくて）ブックモジュールやシートモジュールに書いた場合の事例の図はこちらです。
イベントがVBEのプロシージャボックスに表示されていることに注目してください。
単にQueryTableオブジェクトを作っただけだとこうはならないのですが、クラスモジュールなどでWithEventsなどを駆使すると、これができるようになるようです。
↓

その他の参考
https://okwave.jp/qa/q8781490.html
http://rucio.a.la9.jp/main/shokyu/jugyou29.htm

以下、https://www.excel.studio-kazu.jp/kw/20021112092707.html が消えるといけないので、
引用して覚え書き。

'
'
///////////////////////////////////////////////////////////////////////
'ヘルプの使用例の誤記　覚書
Private Sub QueryTable_AfterRefresh(Success As Boolean)
    If Success
        ' クエリが正常に終了しました。
    Else
        ' クエリが失敗したか、またはキャンセルされました。
    End If
End Sub
　
'引数は「ByVal Success As Boolean」が正解のようである。
'「If Success」のあとの　「Then」 が抜けている。
///////////////////////////////////////////////////////////////////////
　
　
以下単純な例。
Classモジュールを挿入し、下記コード入力
　
'///////////////////////////////////////////////
 Public WithEvents qt As QueryTable
 Private Sub qt_AfterRefresh(ByVal Success As Boolean)
     If Success Then
         MsgBox "クエリが正常に終了しました｡"
     Else
         MsgBox "クエリが失敗したか､またはキャンセルされました｡"
     End If
 End Sub
'///////////////////////////////////////////////
　
標準モジュールへ下記コード入力。
'///////////////////////////////////////////////
 Dim X As New Class1
 Sub Initialize_It()
     Set X.qt = ThisWorkbook.Sheets(1).QueryTables(1)
 End Sub
'///////////////////////////////////////////////
　
Initialize_Itプロシージャを実行後、クエリの更新を行うと、
AfterRefreshイベントマクロが実行されます。
　
なお、私自身もClassモジュールの扱いが不慣れなため、この書き方には
自信がありません。「これが正解」という点があれば、ご指摘願います。
'
'

///////////////////////////////////////////////////////////////////////

'ヘルプの使用例の誤記　覚書

Private Sub QueryTable_AfterRefresh(Success As Boolean)

If Success

' クエリが正常に終了しました。

Else

' クエリが失敗したか、またはキャンセルされました。

End If

End Sub

'引数は「ByVal Success As Boolean」が正解のようである。

'「If Success」のあとの　「Then」が抜けている。

///////////////////////////////////////////////////////////////////////

以下単純な例。

Classモジュールを挿入し、下記コード入力

'///////////////////////////////////////////////

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

If Success Then

MsgBox "クエリが正常に終了しました｡"

Else

MsgBox "クエリが失敗したか､またはキャンセルされました｡"

End If

End Sub

'///////////////////////////////////////////////

標準モジュールへ下記コード入力。

'///////////////////////////////////////////////

Dim X As New Class1

Sub Initialize_It()

Set X.qt = ThisWorkbook.Sheets(1).QueryTables(1)

End Sub

'///////////////////////////////////////////////

Initialize_Itプロシージャを実行後、クエリの更新を行うと、

AfterRefreshイベントマクロが実行されます。

なお、私自身もClassモジュールの扱いが不慣れなため、この書き方には

自信がありません。「これが正解」という点があれば、ご指摘願います。

'
'
'
'

'
'
'
'

'
'
'
'

'
'
'
'

投稿タグ: ExcelVBA, Excel連携VBA, MicrosoftQuery, QueryTable, マクロ, 独学, 自動化

MENU

関連記事

最近の投稿

カテゴリー

MENU

関連記事

最近の投稿

カテゴリー

タグ