Code for timed query refresh

[joelnichols]

I was given this code for a timed query refresh but it give an error. Can anyone correct it?

This is in module1:

Sub QueryTableRefresh()
Dim qt As QueryTable
Application.OnTime Now + TimeValue("00:00:05"), "QueryTableRefresh"
For Each qt In Worksheet("Sheet1").QueryTables
qt.Refresh
Next qt

This is in "this workbook" under "workbook" and "open"

Private Sub Workbook_Open()


QueryTableRefresh

 

[Richard Schollar]

Hi Joel

Apologies if you replied to the other topic - I was in bed!

I originally missed an 's' off of the end of the 'Worksheets'- below is the corrected code:

Sub QueryTableRefresh()
Dim qt As QueryTable
Application.OnTime Now + TimeValue("00:00:30"), "QueryTableRefresh"
For Each qt In Worksheets("Sheet1").QueryTables
qt.Refresh
Next qt
End Sub

Regards

Richard

 

[Richard Schollar]

I also noticed that as stated, you were setting your query tables to refresh every 5 seconds - I think that might become a pain if done so frequently (it can interrupt you/your users and slow the spreadsheet down). Something to bear in mind if it does become a problem.

Richard

 

[joelnichols]

I still get a compile error after fixing worksheet(s). Now the Subquerytablerefresh is still yellow but the worksheets error is fixed.

 

[joelnichols]

Hate to bum this again but still can't get this working

 

[Richard Schollar]

Joel

Does the macro work if all it does it refresh the query tables ie:

Sub QueryTableRefresh() 
Dim qt As QueryTable 

For Each qt In Worksheets("Sheet1").QueryTables 
qt.Refresh 
Next qt 
End Sub

assuming you do have at least one query table on sheet1. If it doesn't work, what error are you getting?

Regards

Richard

 

[joelnichols]

The first line "subquerytablerefresh()" is highlighted yellow. The error message is "Compile error Sub or function not defined"

 

[Richard Schollar]

Is the code typed exactly as I showed it in my post (with a space between "sub" and "QueryTableRefresh()")?

I can run the code successfully under both XL2002 and XL97.

Maybe try copying the code in my post (the bit without the On.Time) and pasting it into your module then try running it (making any adjustments for differing sheet names of course).

Richard

 

[joelnichols]

I re-copied the code and the original error is gone but I now get another error :x .It now says "Run-time error '9' subscript out of range"

 

[Richard Schollar]

Such an error sounds af if the worksheet referenced may not exist - have you amended the bit referenced in bold to whatever your sheet name is?


For Each qt In Worksheets("Sheet1").QueryTables