Hi Pieter,

RefreshAll does not take parameters.
Instead of RefreshAll, you have to specify each pivot you want to refresh:

Sheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh

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 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

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

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

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

You should accomodate with object browser in vba, will save you lots of time.

In the image you can see all the properties for a connection object.

I guess you're chasing the .EnableRefresh property?

You looking for

ActiveWorkbook.Connections("Query - qryMGI_Table").Connections.RefreshWithRefreshAll = False

property

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!