Excel: Wait Until External Data Is Refreshed

[ak10702]

I have a workbook that imports data from a web page. I want to refresh the data every time the workbook is open and then execute code when the data is completely refreshed. I want to be sure the data is refreshed completely before the code runs. I know how to execute code on startup of a workbook but I cant get the code to wait until the refresh data is finished. I'd rather not use

Sub Wait5Sec()
Application.OnTime Now + TimeValue("00:00:05&quot, "SayHello"
End Sub

Sub SayHello
Msgbox "Hello!"
End Sub

which I saw in another post because the data refresh takes a different amount of time every time. How can I wait until excel is ready for input before any more code is executed. Thanks

 

[xlbo]

You'll need to create a class module. This will then allow you to create an "after refresh" event which is triggered when the data has completely refreshed

Insert a new class module

Within that class module you will need:

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
Call The_Sub_To_Run_After_Refresh
End Sub

Within a standard module, you will need:

Dim X As New Class1

this goes in the declarations at the top of the module and

Sub Initialize_Class()
Set X.qt = Sheets("Sheet_With_Querytable&quot.QueryTables(1)
End Sub

this will need to be run each time BEFORE the query is refreshed

so pseudocode is

Run initialise_class
Run Queryrefresh
AfterRefresh event kicks in and runs another sub that can only be run when the data has been refreshed

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ak10702]

Thanks for the reply I actually found out how to do it today also.

Sheets("Sheet1&quot.QueryTables(1).BackgroundQuery = False

will stop the macro from continueing until the refresh is completely finished.

 

[xlbo]

We'll wait and see on that one - Not sure about web queries but I ewas trying different methods for doing this for database queries and the background refresh = false didn't make any difference in that instance but lets hope you're right 'cos it's a lot less hassle

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ak10702]

This is my complete sub

Private Sub WorkBook_Activate()
Dim StrTime As String
StrTime = Time()

Call RefreshDavoxData

Sheets("Davox&quot.QueryTables(1).BackgroundQuery = False

'any code enter here will not be run until the refresh is finished

Sheet3.Range("O3&quot.Value = StrTime

End Sub

Sub RefreshDavoxData()
'Refreshes the Davox Data
On Error GoTo ErrHandler

CurrentTime = Time()
ActiveWorkbook.RefreshAll
Sheet3.Range("O3&quot.Value = CurrentTime


Exit Sub
ErrHandler:
Message = MsgBox("An Error Has Occurred CANNOT REFRESH DAVOX Please Try Again", vbOKOnly, "Error&quot
End Sub