Refreshing Tables with VBA

Question

I have two tables, one with a list of cities( we'll call this City List), and another with data points that correspond with those cities ( Call this The Data Table). The Data Table, is connected to a Select query that I built in MS SQL Server. This Select query/ Data Table has a single Where clause in which I have substituted the SQL criteria and replaced a ? in order to make it a parameter when connected to Excel.

Now that I have that out of the way, I'll explain what I'm trying to accomplish. I want to loop through the City List and for each city in the list, update The Data Table to reflect the data points for the city. Ultimately, I would like to loop through and each time The Data Table is refreshed, it saves a copy of the workbook with that specific table.

I have posted my current code down below, but my issue is that the table never refreshes with the current data that corresponds with the city that is currently selected via the loop. With that being said, if I hit the escape key to break out of the VBA macro, the table will then refresh with whatever the last city was before I stopped the macro.

Code:

Sub Macro1()
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim CT As Variant
Dim MSG As String

Set WS = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
CT = Range("A1").CurrentRegion
For i = 2 To UBound(CT, 1)
    MSG = ""
    For J = 1 To UBound(CT, 2)
        WS.Range("$D$2").Value = CT(i, J) //Places the city into Cell $D$2 which is where The Data Table looks to for the parameter.

    Exit For


    Next J

        ActiveWorkbook.Connections("Query from Database").Refresh

          WS2.ListObjects(1).Refresh

Next i
End Sub

It's almost as though the macro is running too fast for the table to catch up and refresh. I've tried adding some wait times into the code, in hopes that it would give it enough time to allow the table to refresh, but that had no affect. I have also turned off Background Refresh, and that doesn't seem to do anything either. Right now it just loops through the city table, and with each city it shows that the query is refreshing, but after the query is finished refreshing, it goes onto the next city without ever updating The Data Table. HELP!

Answer 1

There are a couple of things I think you need to do -- maybe you've already done them.

1. When you set up your parameter/bind variable (which you have done), point it to a specific cell. Then, within your SQL Server query, make sure the parameter is bound to that range every time:

Forgive me if I'm overstating the obvious, but for those that don't know you get to this dialog by right-clicking the table and selecting Table->Parameters.

2. From there, as you iterate through your main table (the one with the cities in it), you can just take the value from each row in that table and update the cell with the binding parameter.

Something like this should work:

Sub RefreshAllCities()

  Dim ws1, ws2 As Worksheet
  Dim loCities, loDataTable As ListObject
  Dim lr As ListRow

  Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
  Set ws2 = ActiveWorkbook.Worksheets("Sheet2")

  Set loCities = ws1.ListObjects("CityList")
  Set loDataTable = ws2.ListObjects("DataTable")

  ' get rid of those pesky warnings to overwrite files
  Application.DisplayAlerts = False

  For Each lr In loCities.ListRows
    ws2.Cells(1, 2).Value = lr.Range(1, 1).Value
    loDataTable.QueryTable.Refresh BackgroundQuery:=False

    ActiveWorkbook.SaveAs Filename:="C:\temp\" & lr.Range(1, 1).Value & ".xlsx", _
        FileFormat:= xlOpenXMLWorkbook
  Next lr

  Application.DisplayAlerts = True
End Sub

I assume you wanted .xlsx files in this example. This will clobber any embedded VBA, which is actually a nice bonus, as the recipients of the filtered datasets won't have to be exposed to that. If you want xlsm or xlsb, that's easy enough to change.

Answer 2

By default Excel will "Enable Background Refresh" which will allow Excel to move on and continue execution before the query refresh is actually finished. Some people have been able to get it to work by calling .Refresh twice but it's pretty much random/arbitrary timing.

There should be an Excel option to uncheck in the Data Tables properties or you might be able to update the BackgroundQuery = False property from VBA through a reference to it

If you disable background refreshing then your code will sit and wait for the refresh to complete before moving on.