I started with this devil-may-care bit of code:
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
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.
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
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!
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
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!
Thanks, and thanks!
Tushar once told me to move all On Error Resume Next blocks to their own procedure. Good advice. I should really follow it more often.
You could also use qt.Refresh rather than lo.QueryTable.Refresh, but for no benefit that I could imagine.
Thanks for pointing that out. I’m going to fix those.
It is interesting advice, and I think it’s probably the right thing to do. But in some cases it seems clearer to leave the test in the middle of the code, rather than squirreling it away in yet another tiny function… Or at least not worth the bother… or… damn I suppose he’s right.
Pingback: Daily Dose of Excel » Blog Archive » Learning from my Errors
This checks the type of the ListObject, then refreshes if it is of the querytable type.
This doesnt throw any errors in normal operation, though this only refreshes query tables.
Sub Five()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
For Each ws In ThisWorkbook.Worksheets
For Each lo In ws.ListObjects
if lo.SourceType = xlSrcQuery Then
lo.QueryTable.Refresh
End If
Next lo
Next ws
End Sub
Excel 2010, my worksheet has several querytables, but they are not recognized as listobjects, so although I can manually perform a data refresh, the above does nothing. I have yet to find a tutorial or website that fully explains querytables.