Prev: Create a small table from a large table by skipping empty colu
Next: Auto Fill
From: sot on 23 Apr 2010 10:01
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

From: Rick Rothstein on 23 Apr 2010 10:54
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" <sot(a)discussions.microsoft.com> wrote in message
news:3008BC9F-A54F-4166-9E0D-33A4063129E7(a)microsoft.com...
> 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
>
From: EricG on 23 Apr 2010 12:55
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
>
 | 
Pages: 1
Prev: Create a small table from a large table by skipping empty colu
Next: Auto Fill