Code for timed query refresh

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Apr 13, 2004
Messages
384
I still get a compile error after fixing worksheet(s). Now the Subquerytablerefresh is still yellow but the worksheets error is fixed.
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384

ADVERTISEMENT

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Joel

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

Code:
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

Active Member
Joined
Apr 13, 2004
Messages
384

ADVERTISEMENT

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Apr 13, 2004
Messages
384
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,045
Members
412,359
Latest member
misstoffeepenny
Top