You can use the event below. Put in Thisworkbook VBA sheet.

Private Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' Query completed successfully
Else
' Query failed or was cancelled
End If
End Sub


You have two ways of handling the event

1) Put the code you want in the event handler
2) Set what is called a semiphore in the event handler when the query is
updated. Make a variable Public like this

Public EventCounter as integer
Public LastCount as integer

Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' Query completed successfully
EventCounter = EventCounter + 1
Else
' Query failed or was cancelled
End If
End Sub

Then in a public module
sub MyModule

'Initialize counters
OldEventCounter = 0
LastCount = 0

'wait 5 minutes
'Application.OnTime Now + TimeValue("00:05:00"), "QueryUpdate"

End sub


sub QueryUpdate()

'only execute your code if the counter has changed
if LastCount < EventCounter then
'Enter your query code here

update counter
LastCount = EventCounter
end if
'wait again for next update
'Application.OnTime Now + TimeValue("00:05:00"), "QueryUpdate"

end sub


"Ken McLennan" wrote:

G'day there One & All,

I recently posted here requesting assistance to determine when an
OLAP query had finished. I've since had a chance to look further at the
spreadsheets in the office, and they're starting to get a bit clearer.

To reiterate, I'm trying to build a report which is intended to be
printed. I have the layout done, and some of the data is obtainable
through simple links to cells. I'll have to search various pages to
obtain the remainder, but that's not my problem at the moment.

First off I need to set various parameters on the data sheets from
within my report. Each sheet then rebuilds from the OLAP cube and
refreshes itself. Only then do I have valid data to use. I need to find
a way to determine when these refreshes are complete and then build my
report from the now updated data.

I've poked about at work and found that there are no code modules.
in the data sheets. Each workbook consists entirely of PivotTables with
some setup code in ThisWorkBook.

That being the case, I can't think of anyway to programatically
determine when an update has completed. It's simple enough to do it
manually; the status bar stops showing messages. Unfortunately, that's
not much good to me in this case. Unless, perhaps, I monitor the status
bar but that seems a bit clunky. It's possible it may remain static
while the network slows down my requests. (I think our network operates
on damp string rather than copper wire).

I'm not a programmer, and have zero experience with databases &
reports. This is my first attempt at using them. If anybody knows how I
can find when the refreshes are finished, I'll be happy to hear from
you.

Thanks for listening,
Ken McLennan
Qld, Australia