Refreshing Web Queries and DisplayAlerts
 

Im working with a series of macros (Excel 2003) that schedule hourly updates
of a bunch of web queries, and then process each hour's query results. The
scheduling macro calls up the refresh and processing macros.

I have been using Application.DisplayAlerts = False in the beginning of both
the scheduling macro, and the refresh macro. None of the referenced macros
include Application.DisplayAlerts = True. However, when one of the queried
websites cannot be opened, a pop-up box states:

€œUnable to open http://[web address]. Cannot download information you
requested.€�

This box stops the macros from continuing until it is clicked by a person.
How can I stop alerts that occur during web query refreshes? Some of the
code Im using follows:

Sub SetSchedule ()
Application.DisplayAlerts = False
Sheets(€œData€�).Select
Range(€œg5:z5€�).Select
For Each Item in Selection
Application.OnTime Item.Value, €œRefresh€�
Application.OnTime Item.Value + TimeValue(€œ00:10:00€�), €œMacro3€�
Application.OnTime Item.Value + TimeValue(€œ00:11:00€�), €œMacro4€�
Next Item
End Sub

Sub Refresh ()
Windows(€œfilename.xls€�).Activate
Application.StatusBar = €œProcessing Refresh€�
Application.DisplayAlerts = False
Sheets(€œData€�).Select
Range(€œc3€�).Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue(€œ00:02:30€�), €œMacro5€�
End Sub

Sub Macro5 ()
Application.ScreenUpdating = True
Application.StatusBar = €œScheduled Updates are Running€�
End Sub

Refreshing Web Queries and DisplayAlerts
 

Try this:
Sub Refresh ()
On Error Resume Next
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Mike F
"DJ" wrote in message
...
I'm working with a series of macros (Excel 2003) that schedule hourly
updates
of a bunch of web queries, and then process each hour's query results.
The
scheduling macro calls up the refresh and processing macros.

I have been using Application.DisplayAlerts = False in the beginning of
both
the scheduling macro, and the refresh macro. None of the referenced
macros
include Application.DisplayAlerts = True. However, when one of the
queried
websites cannot be opened, a pop-up box states:

"Unable to open http://[web address]. Cannot download information you
requested."

This box stops the macros from continuing until it is clicked by a person.
How can I stop alerts that occur during web query refreshes? Some of the
code I'm using follows:

Sub SetSchedule ()
Application.DisplayAlerts = False
Sheets("Data").Select
Range("g5:z5").Select
For Each Item in Selection
Application.OnTime Item.Value, "Refresh"
Application.OnTime Item.Value + TimeValue("00:10:00"), "Macro3"
Application.OnTime Item.Value + TimeValue("00:11:00"), "Macro4"
Next Item
End Sub

Sub Refresh ()
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Sub Macro5 ()
Application.ScreenUpdating = True
Application.StatusBar = "Scheduled Updates are Running"
End Sub

Refreshing Web Queries and DisplayAlerts
 

Thanks, I'll give it a try . . .

"Mike Fogleman" wrote:

Try this:
Sub Refresh ()
On Error Resume Next
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Mike F
"DJ" wrote in message
...
I'm working with a series of macros (Excel 2003) that schedule hourly
updates
of a bunch of web queries, and then process each hour's query results.
The
scheduling macro calls up the refresh and processing macros.

I have been using Application.DisplayAlerts = False in the beginning of
both
the scheduling macro, and the refresh macro. None of the referenced
macros
include Application.DisplayAlerts = True. However, when one of the
queried
websites cannot be opened, a pop-up box states:

"Unable to open http://[web address]. Cannot download information you
requested."

This box stops the macros from continuing until it is clicked by a person.
How can I stop alerts that occur during web query refreshes? Some of the
code I'm using follows:

Sub SetSchedule ()
Application.DisplayAlerts = False
Sheets("Data").Select
Range("g5:z5").Select
For Each Item in Selection
Application.OnTime Item.Value, "Refresh"
Application.OnTime Item.Value + TimeValue("00:10:00"), "Macro3"
Application.OnTime Item.Value + TimeValue("00:11:00"), "Macro4"
Next Item
End Sub

Sub Refresh ()
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Sub Macro5 ()
Application.ScreenUpdating = True
Application.StatusBar = "Scheduled Updates are Running"
End Sub

Refreshing Web Queries and DisplayAlerts
 

Hi Mike,
On Error Resume Next does not prevent web query pop-up windows when a site
is not available.

Any other ideas?


"Mike Fogleman" wrote:

Try this:
Sub Refresh ()
On Error Resume Next
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Mike F
"DJ" wrote in message
...
I'm working with a series of macros (Excel 2003) that schedule hourly
updates
of a bunch of web queries, and then process each hour's query results.
The
scheduling macro calls up the refresh and processing macros.

I have been using Application.DisplayAlerts = False in the beginning of
both
the scheduling macro, and the refresh macro. None of the referenced
macros
include Application.DisplayAlerts = True. However, when one of the
queried
websites cannot be opened, a pop-up box states:

"Unable to open http://[web address]. Cannot download information you
requested."

This box stops the macros from continuing until it is clicked by a person.
How can I stop alerts that occur during web query refreshes? Some of the
code I'm using follows:

Sub SetSchedule ()
Application.DisplayAlerts = False
Sheets("Data").Select
Range("g5:z5").Select
For Each Item in Selection
Application.OnTime Item.Value, "Refresh"
Application.OnTime Item.Value + TimeValue("00:10:00"), "Macro3"
Application.OnTime Item.Value + TimeValue("00:11:00"), "Macro4"
Next Item
End Sub

Sub Refresh ()
Windows("filename.xls").Activate
Application.StatusBar = "Processing Refresh"
Application.DisplayAlerts = False
Sheets("Data").Select
Range("c3").Select
ActiveCell = Now()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:02:30"), "Macro5"
End Sub

Sub Macro5 ()
Application.ScreenUpdating = True
Application.StatusBar = "Scheduled Updates are Running"
End Sub