Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refreshing web queries | Excel Programming | |||
DisplayAlerts | Excel Programming | |||
Problems refreshing internal queries in excel | Excel Discussion (Misc queries) | |||
DisplayAlerts (Which Ones?) | Excel Programming | |||
DisplayAlerts | Excel Programming |