refresh macro
 

Windows XP
Office 2003

I have written a simple macro that refreshes all my external data (database
queries) in my spreadsheet. This macro is supposed to refresh everything.
Most of the time this is the case. Sometimes for some reason a dataset or two
that should get refreshed does not get refreshed. If one or two has not
updated after the macro runs, I can manually tell it to refresh and it will
work. Anyone have any idea why the refresh all macro sometimes skips a data
set and/or how to correct it? Is there some sort of a timeout or something
that might be causing this?

Here is my macro:
Sub Macro3()

Application.DisplayAlerts = False

Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True
MsgBox("Refresh Complete")

End Sub

Thanks,
-Lou Sanderson

refresh macro
 

Each query should be set so it does not perform a backgroundquery. then no
queries wil be "skipped".

--
Regards,
Tom Ogilvy


"Lou Sanderson" wrote:

Windows XP
Office 2003

I have written a simple macro that refreshes all my external data (database
queries) in my spreadsheet. This macro is supposed to refresh everything.
Most of the time this is the case. Sometimes for some reason a dataset or two
that should get refreshed does not get refreshed. If one or two has not
updated after the macro runs, I can manually tell it to refresh and it will
work. Anyone have any idea why the refresh all macro sometimes skips a data
set and/or how to correct it? Is there some sort of a timeout or something
that might be causing this?

Here is my macro:
Sub Macro3()

Application.DisplayAlerts = False

Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True
MsgBox("Refresh Complete")

End Sub

Thanks,
-Lou Sanderson

refresh macro
 

Saved from a Tom Ogilvy post:

Your problem is your backgroundquery option for your queries. Set these to
false and your code will wait for the query update to finish.

If you don't want to change that parameter in the query itself you can
override it with the refresh command (but not the refresh all command)

Activesheet.queryTables(1).Refresh Backgroundquery:=false

as an example.



Lou Sanderson wrote:

Windows XP
Office 2003

I have written a simple macro that refreshes all my external data (database
queries) in my spreadsheet. This macro is supposed to refresh everything.
Most of the time this is the case. Sometimes for some reason a dataset or two
that should get refreshed does not get refreshed. If one or two has not
updated after the macro runs, I can manually tell it to refresh and it will
work. Anyone have any idea why the refresh all macro sometimes skips a data
set and/or how to correct it? Is there some sort of a timeout or something
that might be causing this?

Here is my macro:
Sub Macro3()

Application.DisplayAlerts = False

Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True
MsgBox("Refresh Complete")

End Sub

Thanks,
-Lou Sanderson

--

Dave Peterson