Refresh All Queries in Workbook

Question

This works for .xls books, but can it be altered for .xlsx workbooks as well? Or is their syntax that will work for both?

Option Explicit
Public Sub RefreshQueries()
  Dim wks As Worksheet
  Dim qt As QueryTable
  For Each wks In Worksheets
    For Each qt In wks.QueryTables
        qt.Refresh BackgroundQuery:=False
    Next qt
  Next wks
  Set qt = Nothing
  Set wks = Nothing
End Sub

EDIT -- So it seems my syntax does refresh .xlsx workbooks, but not queries that are from sql server. How can those be refreshed via VBA.

Answer 1

First, no macro will work in a .xlsx workbook because .xlsx workbooks can't contain macros - you need to save as a macro-enabled workbook which has the extension .xlsm.

In Excel 2007 and later, user created external data connections to SQL Server data sources (amongst others) will result not in a QueryTables member, but in a ListObject which will possess a QueryTable object that can be accessed via the ListObject.QueryTable property - see Dick Kusleika's answer to this question. The following code should refresh both types of queries:

Option Explicit
Public Sub RefreshQueries()

  Dim wks As Worksheet
  Dim qt As QueryTable
  Dim lo As ListObject

  For Each wks In Worksheets
    For Each qt In wks.QueryTables
        qt.Refresh BackgroundQuery:=False
    Next qt

    For Each lo In wks.ListObjects
        lo.QueryTable.Refresh BackgroundQuery:=False
    Next lo

  Next wks

  Set qt = Nothing
  Set wks = Nothing
End Sub

I wasn't previously familiar with the ListObject type so I don't know if you can have a ListObject on a worksheet that doesn't have a QueryTable, which might cause an error in the above code - you might need to check for this.