0013

The way I would do it is in a standard module

Sub RefreshQry()

Dim qt As QueryTable

For Each qt In Sheet1.QueryTables
qt.Refresh False
Next qt

'do logic here

End Sub

Using the False argument will suspend code execution until the query is
refreshed.

If you want to use the event, you could do it with a standard class module
like this: In a standard module

Dim clsqt() As Class1

Sub ClassRefresh()

Dim smqt As QueryTable
Dim i As Long

ReDim clsqt(1 To Sheet1.QueryTables.Count)

For i = 1 To Sheet1.QueryTables.Count
Set clsqt(i) = New Class1
Set clsqt(i).qt = Sheet1.QueryTables(i)
Sheet1.QueryTables(i).Refresh True
Next i

End Sub

In a class module named Class1

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

Dim oqt As QueryTable

Debug.Print Me.qt.ResultRange.Address

For Each oqt In Sheet1.QueryTables
If oqt.Refreshing Then
Exit Sub
End If
Next oqt

MsgBox "do logic here"

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


0013 wrote:
I have a worksheet with several QueryTable objects on it. I want to loop
through the worksheet's QueryTables collection and call refresh on each
QueryTable. Then I want a specific function to be called only after they
have all finished refreshing. How would I do this last part?

I tried the following without any luck inside the specific worksheet
object:

private WithEvents qt as Excel.QueryTable
private qtRefreshCount as integer

public sub RefreshQueryTables()
qtRefreshCount = 0
for each qt in Me.QueryTables
qt.Refresh(true)
next
end sub

private sub qt_AfterRefresh(ByVal Success As Boolean)
qtRefreshCount = qtRefreshCount + 1
if qtRefreshCount < Me.QueryTables.Count then
exit sub
end if
' do logic here...
end sub