Macro to execute on WEB Query Refresh

  • Thread starter Thread starter Donnie Stone
  • Start date Start date
D

Donnie Stone

I have a macro I want to run after my WEB Query Refresh takes place. Any
assistance would greatly be appreciated.

Regards,

Donnie
 
Hi,

To execute some codes after Query refresh or before Query refresh;

1. add a class module to your VBA project. (assumed name is class1)
2. copy following line into your class module.

Public WithEvents qtQueryTable As QueryTable
Sub InitQueryEvent(QT As Object)
Set qtQueryTable = QT
End Sub

3. if you click Object Combobox to drop down, you'll see qtQueryTable on the
list. select it. there will be two events available for Query Table.
"AfterRefresh and BeforeRefresh". Write your code there.

4. before using these events, you need to set up your query table's
AfterRefresh and BeforeRefresh events using following code. (It could be in
worksheet's code or workbook's code)

Dim clsQueryTable As New Class1

Sub RunInitQTEvent(Optional FakeArg As Integer)
clsQueryTable.InitQueryEvent QT:=Sheet1.QueryTables(1) 'assumed
your query table in sheet1
End Sub

Private Sub Worksheet_Activate()
RunInitQTEvent
End Sub

'///// or /////
Dim clsQueryTable As New Class1

Sub RunInitQTEvent(Optional FakeArg As Integer)
clsQueryTable.InitQueryEvent QT:=Sheet1.QueryTables(1) 'assumed
your query table in sheet1
End Sub
Private Sub Workbook_Open()
RunInitQTEvent
End Sub


Good luck

Kind regards.


Haldun Alay
 
Back
Top