For
Each
ws
In
ThisWorkbook.Worksheets
For
Each
lo
In
ws.ListObjects
On
Error
Resume
Next
lo.QueryTable.Refresh
On
Error
GoTo
0
Next
lo
Next
ws
End
Sub
Just wrap the QueryTable.Refresh in an On Error pair and don’t sweat it, that was my stance, at least for a day or two. If the ListObject had a QueryTable it would get refreshed. If it didn’t, it wouldn’t.
As the time got closer to hand it off to other people – people who might be bummed if their data didn’t refresh for reasons I hadn’t anticipated – I took a more prudent approach. I wrote some code to check if the ListObject actually had a QueryTable. This allows me to isolate the “ListOject with no QueryTable” error from all the others that might
fly in under the radar
.
I ended up with a simple function that’s now in my code library. But before we get to that, I’ll show you three lesser ListObject QueryTable tests, from bad to better:
#1 – Testing with Err.Number
In case you’re not familiar with On Error statements, I should clarify that On Error Resume Next let’s your code run willy-nilly through any and all errors. The madness only ends when an On Error Goto 0 statement is encountered. On Error Goto 0 also resets Err.Number to 0.
Sub
One()
Dim
ws
As
Excel.Worksheet
Dim
lo
As
Excel.ListObject
Dim
qt
As
Excel.QueryTable
For
Each
ws
In
ThisWorkbook.Worksheets
For
Each
lo
In
ws.ListObjects
On
Error
Resume
Next
Set
qt = lo.QueryTable
If
Err.Number = 0
Then
qt.Refresh
End
If
On
Error
GoTo
0
Next
lo
Next
ws
End
Sub
I would never do this (not even in a really old Google Groups answer, I hope). It doesn’t fix the basic problem. The refresh is still happening with On Error set to Resume Next. It’s even worse if you have Else clauses. You could blunder through them as well before getting back to On Error Go To 0. The only way I can see it working is with another On Error Go To 0 right inside the IF clause before the refresh, and that’s just ugly.
#2 – Using an ErrorNum variable
Sub
Two()
Dim
ws
As
Excel.Worksheet
Dim
lo
As
Excel.ListObject
Dim
qt
As
Excel.QueryTable
Dim
ErrorNum
As
Long
For
Each
ws
In
ThisWorkbook.Worksheets
For
Each
lo
In
ws.ListObjects
On
Error
Resume
Next
Set
qt = lo.QueryTable
ErrorNum = Err.Number
On
Error
GoTo
0
If
ErrorNum = 0
Then
qt.Refresh
End
If
Next
lo
Next
ws
End
Sub
This approach fixes the problem in the previous routine by immediately setting an ErrorNum variable to Err.Number’s value. This tightens up the On Error Resume Next scope so it’s only active during the test. Pretty good, and for tests that don’t involve objects I’d probably stop there.
#3 – You’ve got an object variable, just use that!
Sub
Three()
Dim
ws
As
Excel.Worksheet
Dim
lo
As
Excel.ListObject
Dim
qt
As
Excel.QueryTable
For
Each
ws
In
ThisWorkbook.Worksheets
For
Each
lo
In
ws.ListObjects
Set
qt =
Nothing
'Don't forget this!
On
Error
Resume
Next
Set
qt = lo.QueryTable
On
Error
GoTo
0
If
Not
qt
Is
Nothing
Then
qt.Refresh
End
If
Next
lo
Next
ws
End
Sub
Since we’re trying to set qt to something, let’s just test if it’s not nothing. This has the same advantage as the previous one: On Error statements bracket just the one line of your test, preventing stealth errors. The big gotcha is you’ve got to remember to set qt to Nothing before you try to set it to something. Otherwise, if the previous ListObject had a QueryTable, and this one doesn’t, the Resume Next will happily ignore the error and leave qt set to the previous one. That’s confusing, and potentially tragic.
#4 – The right way
Sub
Four()
Dim
ws
As
Excel.Worksheet
Dim
lo
As
Excel.ListObject
Dim
qt
As
Excel.QueryTable
For
Each
ws
In
ThisWorkbook.Worksheets
For
Each
lo
In
ws.ListObjects
Set
qt = GetListObjectQueryTable(lo)
If
Not
qt
Is
Nothing
Then
qt.Refresh
End
If
Next
lo
Next
ws
End
Sub
Function
GetListObjectQueryTable(lo
As
Excel.ListObject)
As
Excel.QueryTable
On
Error
Resume
Next
Set
GetListObjectQueryTable = lo.QueryTable
End
Function
Here I’ve moved the test into a function and put it in my utility module along with tests for workbook state, folder existence and other such mundanities. I know it works, I don’t have On Errors in the main module, and I only need Resume Next in the function, cause there’s not a heckuva lot of room for resuming.
Tangential miscellany
Here’s a
pithy Jeff Weir rant
on testing for ActiveCell.PivotTable versus ActiveCell.Listobject
This post deals with Excel-2007-and-on ListObject.QueryTables. In earlier versions QueryTables belonged to the worksheet they were on. In this
Stack Overflow answer
Dick (DDOE) Kusleika posts a function to find any QueryTable by name.
A thing I should know, but maybe you can tell me
What’s the difference between ListObject.Refresh and QueryTable.Refresh?
Apparently, ListObject.Refresh can only be used for a list that is linked to a SharePoint site.
http://msdn.microsoft.com/en-us/library/office/ff834313%28v=office.15%29.aspx
Great article by the way!