Four ListObject QueryTable Tests - Each Better Than the Last

I started with this devil-may-care bit of code:

Sub Zero()
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.

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?

7 thoughts on “Four ListObject QueryTable Tests – Each Better Than the Last”

ruve1k on May 15, 2014 at 11:43 am said:

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!

Reply ↓
- Doug Glancy on May 15, 2014 at 1:25 pm said:
  
  Thanks, and thanks!
  
  Reply ↓
Dick Kusleika on May 20, 2014 at 1:10 pm said:

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.

Reply ↓
- Doug Glancy on May 20, 2014 at 6:32 pm said:
  
  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.
  
  Reply ↓
Pingback: Daily Dose of Excel » Blog Archive » Learning from my Errors
MikeW on November 8, 2017 at 4:24 pm said:

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

Reply ↓
Don on February 21, 2018 at 10:05 am said:

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.

Reply ↓