Refresh - SOLVED

matthewj

New Member
Joined
Nov 6, 2002
Messages
10
**** 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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

vjkarnam

New Member
Joined
Oct 29, 2004
Messages
6
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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.
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top