Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Problem
Basically I've tried to create a macro that opens up all the files in one of
my folders and refreshes the querys in them which bring though new data from Access. But Excel is determined to try rushing through the steps and save/close the files before the refresh/calc has finished! Is there a way around this. See below for current Macro. Workbooks.Open Filename:= _ "S:\Planning\DP\AW2010 Planning\B+M Tierings\Latest View\AW10 Tiering.xls" ActiveWorkbook.RefreshAll ActiveWorkbook.Save ActiveWindow.Close |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Problem
I've not done what you are asking before, so this is only a guess. Try
putting a DoEvents statement immediately after the RefreshAll line... Workbooks.Open Filename:= _ "S:\Planning\DP\AW2010 Planning\B+M Tierings\Latest View\AW10 Tiering.xls" ActiveWorkbook.RefreshAll DoEvents ActiveWorkbook.Save ActiveWindow.Close -- Rick (MVP - Excel) "sot" wrote in message ... Basically I've tried to create a macro that opens up all the files in one of my folders and refreshes the querys in them which bring though new data from Access. But Excel is determined to try rushing through the steps and save/close the files before the refresh/calc has finished! Is there a way around this. See below for current Macro. Workbooks.Open Filename:= _ "S:\Planning\DP\AW2010 Planning\B+M Tierings\Latest View\AW10 Tiering.xls" ActiveWorkbook.RefreshAll ActiveWorkbook.Save ActiveWindow.Close |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Problem
You might need to turn off background query refreshes if they are turned on.
That will force Excel to complete each query refresh before doing anything else. Bring up the connection properties dialog for each query, and uncheck the "Enable background refresh" check box. Or whip up a quick macro that will do it for all queries in the workbook. HTH, Eric "sot" wrote: Basically I've tried to create a macro that opens up all the files in one of my folders and refreshes the querys in them which bring though new data from Access. But Excel is determined to try rushing through the steps and save/close the files before the refresh/calc has finished! Is there a way around this. See below for current Macro. Workbooks.Open Filename:= _ "S:\Planning\DP\AW2010 Planning\B+M Tierings\Latest View\AW10 Tiering.xls" ActiveWorkbook.RefreshAll ActiveWorkbook.Save ActiveWindow.Close |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL Macro problem | Excel Programming | |||
excel macro problem | Excel Programming | |||
Excel-Macro Problem | Excel Discussion (Misc queries) | |||
Excel 2k3 Macro Problem | Excel Programming | |||
Excel XP macro problem | Excel Programming |