PDA

View Full Version : [SOLVED] Putting a time limit on QueryTable.Refresh



paulked
04-26-2020, 07:33 AM
I have a workbook that 'scrapes' 4 different tables from websites. I have disabled Background Refresh for all of them as I need the tables loaded with new data before carrying on the code.

Sometimes, if the web page is not available for example, the code will just sit there waiting, for hours if I don't manually hit Break.

Is there a way to incorperate a timer, say for 60 seconds, that will stop the update if there is no responce/action?

code to update is



frmUpDt.Show
frmUpDt.lb1 = "Updating todays world data..." & vbLf & "Done 0 of 5"
DoEvents
Web.ListObjects("World").QueryTable.Refresh
frmUpDt.lb1 = "Updating yesterdays world data..." & vbLf & "Done 1 of 5"
DoEvents
Weby.ListObjects("WorldY").QueryTable.Refresh
frmUpDt.lb1 = "Updating todays USA data..." & vbLf & "Done 2 of 5"
DoEvents
WebUSA.ListObjects("USA").QueryTable.Refresh
frmUpDt.lb1 = "Updating yesterdays USA data..." & vbLf & "Done 3 of 5"
DoEvents
WebUSAy.ListObjects("USAy").QueryTable.Refresh
frmUpDt.lb1 = "Updating data tables and sheets..." & vbLf & "Done 4 of 5"
DoEvents

paulked
04-27-2020, 05:29 AM
I searched the dark corners of the web but couldn't find anything. So I've taken a different approach to this, and it has worked out for the better.

1. Turned background refresh back on for all the queries.
2. Have an individual routine for updating the workbook for each query.
3. Run the routine when the new information has been loaded from the web (detected by a Worsheet_Change event).

It's quicker and doesn't lock the workbook when updating, happy as Larry! Who was Larry btw?