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

 

[joelnichols]

O.K. that was the prob. The sheet name was "query" but it said "sheet1" next to it so I assumed either/or would work (duh). 1 more question. If i have 5 sheets with queries what do I need to change so they all update?

Thanks for all your help

 

[Richard Schollar]

Probably easiest to do this:

Sub QueryTableRefresh()
Dim qt As QueryTable, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    For Each qt In ws.QueryTables
    
    qt.Refresh
    
    Next qt

Next ws
End Sub

This will cycle thru all the workbooks sheets and refresh all the query tables therein (it doesn't matter if there are no querytables in any worksheet, it will simply skip to the next worksheet).

Regards

Richard

 

[joelnichols]

I think i put a line in the wrong spot and it errors out. Now excel shuts down without even letting me see the code lol.

Any ideas how to get to the code now?

 

[Richard Schollar]

Joel

Do you mean that if you open Excel up and go to the VBE you cannot click on the module containing the code without Excel closing down? You did copy that code into a standard module (probably called "Module1")?

Richard

 

[joelnichols]

I mean when I open the book it errors and excel shuts off.