Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

excel - How to call macro after Refresh or Refresh All button pressed?

Ultimately, I would like to run a macro after anyone refreshes the workbook, specifically using the Refresh button under the Data tab in Excel.

For the time being, I would be satisfied just getting the BeforeRefresh or AfterRefresh QueryTable events to fire upon pressing the Refresh button.

In addition to the documentation on the Microsoft Dev Center website, the relevant posts I have read include:

Here is what I have:

Under Class Modules (qtclass)

Option Explicit

Private WithEvents qt As Excel.QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qt_AfterRefresh called sucessfully."
    If Success = True Then
        Call Module2.SlicePivTbl
        MsgBox "If called succesfully."
    End If

End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    MsgBox "qt_BeforeRefresh called."
End Sub

Under the ThisWorkbook module

Private Sub Workbook_Open()

    Dim qtevent As qtclass
    Dim qt As QueryTable
    Set qt = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable
    Set qtevent = New qtclass

End Sub

I have tried variations of the second code block under specific worksheets as well, but have yet to find anything that works. Do I need to somehow dim the QueryTable in question in the Worksheet module?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You haven't actually connected the querytable to the class instance. Revised qtclass

Option Explicit

Private WithEvents qt As Excel.QueryTable
Public Property Set HookedTable(q As Excel.QueryTable)
    Set qt = q
End Property

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qt_AfterRefresh called sucessfully."
    If Success = True Then
        Call Module2.SlicePivTbl
        MsgBox "If called succesfully."
    End If

End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    MsgBox "qt_BeforeRefresh called."
End Sub

New ThisWorkbook code:

Dim qtevent As qtclass
Private Sub Workbook_Open()

    Set qtevent = New qtclass
    Set qtevent.HookedTable = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable

End Sub

Note that this is quite closely coupled. It would be more re-usable if you were to raise events in the class and declare your qtevent variable WithEvents.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...