Refresh - SOLVED

[matthewj]

**** Used the following bit of code to pause long enough for the data to be refreshed

newhour = Minute(Now()) + 2
flag = False
While newhour<> Minute(Now())

If flag = False Then
ActiveWorkbook.RefreshAll
flag = True
Else
End If


Wend



**** Basically just asking how i can make VBA wait until the Activeworkbook.refreshall has finished executing before executing the rest of the code!


I have a workbook consisting of 3 sheets.

Sheets 2 and 3 have alot of formulae in them. Sheet 1 contains an external data source to an As400.

The probl;em is im using vba to copy data from the sheet 1 to sheets 2 and 3. At the start of the module i use a .refresh command but it doesnt finish refreshing before it executeds the code to copy th values to the other sheets. Ive tried using loops and wait commands to no avail. Also tried using the querytablke_afterrefresh event also to no avail.

Tisa means that the old data is being copied to sheets 2 and 3 even though sheet 1 is refreshing.
This message was edited by matthewj on 2002-11-08 05:36
This message was edited by matthewj on 2002-11-08 05:37
This message was edited by matthewj on 2002-11-08 07:35
This message was edited by matthewj on 2002-11-08 09:41

 

[vjkarnam]

Hi Matthe,

Thanks for this solution.
(Is this a solution or you asking this que?) )

But is there anyway by which I can do a refresh without hard codeing the time to pause like you used 2 min in above code.

Thanks,
Vj

 

[XLGibbs]

If you have formulas linked to an externally refreshed data table, you can disable background query from the properties menu of the MS Query...this will allow the table to refresh FIRST, followed by everything else.

I have a lot of queries that feed into numerous pivot tables from the same set of data, and when background query disabled, the external data refreshes before the pivot tables recalculate. This way the refresh all button works to effectively refresh everything in order.