Include / exclude queries from “Refresh All”|Power Query|Excel Forum|My Online Training Hub

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

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

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.

Pieter