
Power Query

August 3, 2020

Can anyone help me in using VBA to selectively include / exclude queries from “Refresh All”?
Background: I use a parameter table to pass values to a number of queries and I normally want all queries to be refreshed. However, if a parameter is blank, I don’t want to refresh the related query and I’d like to avoid errors caused by a user hitting the “Refresh All” button. I’ve created a macro to refresh individual queries and I’m aware of the ability to include / exclude individual queries from “Refresh All” through the ribbon bar. Any help appreciated, thanks.

Power Query

August 3, 2020

Hi Catalin,
Sorry for the delay in coming back; thanks for taking the trouble to respond and confirming that RefreshAll doesn't take parameters, which makes sense, and your solution provides a round this, but I still wonder ...
I need to refresh queries rather than pivots, but the same applies. I have an Excel workbook that I'm distributing to several users, some of whom use Windows and some Macs. Because Macs don't always act the same as windows(!) I've created a Set-up table which includes an OS parameter to determine whether I need to use Windows or Mac VBA varients. Once the set-up is complete I'd like to be able to use RefreshAll by macro or ribbon button.
So what I was trying to achieve was the ability to include or exclude certain queries from the RefreshAll command once the OS had been selected.
In other words: If Windows, then RefreshAll updates A, B and C; if Mac, RefreshAll updates A,B and D.
Does this make sense and is it possible? I'd rather not dumb down my Windows VBA just because Mac can't cope!
Thanks again,
Pieter


November 8, 2013

Hi Pieter,
Best is to start the macro recorder, to get the piece of code you need.
That's what I did, here is the code generated to update a specific query:
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
To update it to your tables:
ThisWorkbook.Worksheets("Sheet1").ListObjects("tblData").QueryTable.Refresh BackgroundQuery:=False

Power Query

August 3, 2020

Hi Catalin,
Thanks again. Unfortunately this whole thing started with it looking as though Excel for Mac VBA doesn't support statements like
ThisWorkbook.Worksheets("Sheet1").ListObjects("tblData").QueryTable.Refresh BackgroundQuery:=False
I'd love to be proved wrong, but in the meantime I need to include a query from RefreshAll if it's running on a Windows machine and exclude it if using a Mac.
I've now got a workaround, but it's clumsy so I'm still looking for a way to achieve the aim.
As ever,
Pieter


November 8, 2013

I'm confused.
"Can anyone help me in using VBA to selectively include / exclude queries from “Refresh All”?"
Is it the code for identifying if it's a Mac you need?
#If Mac then
#Else 'it's windows
ThisWorkbook.Worksheets("Sheet1").ListObjects("tblData").QueryTable.Refresh BackgroundQuery:=False
#End If

Power Query

August 3, 2020

OK - after a bit of digging around, it looks as though this can't be done. Just to recap, I want to use VBA programmatically to set whether or not a particular query is included in "Refresh All" (see attached pic) dependent on a particular condition.
On recording macros for setting and unsetting this parameter the code is identical for both, ie:
Sub tempdisableincludeinrefreshall()
With ActiveWorkbook.Connections("Query - qryMGI_Table").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("SELECT * FROM [qryMGI_Table]")
.CommandType = xlCmdSql
.Connection = _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qryMGI_Table;Extended Properties="""""
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query - qryMGI_Table")
.Name = "Query - qryMGI_Table"
.Description = "Connection to the 'qryMGI_Table' query in the workbook."
End With
End Sub
You'll see that the "Refresh this connection on Refresh All" parameter comes after "Refresh data when opening the file", which does feature, but this and several other parameters aren't.
From this I conclude that this parameter can't be programmed, though I'd love to be proved wrong!
Thanks, Catalin, for your suggestions.

Power Query

August 3, 2020

Hi Greg,
Thanks for mentioning this new property, which I hadn't spotted: it's really useful. For completeness, it doesn't require the second "Connections", so it is:
ActiveWorkbook.Connections("Query - qryMGI_Table").RefreshWithRefreshAll = False
Sorry it's taken me a while to reply!
1 Guest(s)
