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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,163
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top