Hi Pieter,

Have you tried the technique described in session 7.05 Formula Firewall Errors?

Mynda

Hi Mynda,

Welll, I thought I'd done exactly that!  The lesson describes what can happen when, say, merging a table with an external source, but I don't get that far.  I first set up my staging query with Source = Excel.Workbook(File.Contents("C:Users
nnnnnDropboxxxxxxxyyyyy.xlsx"), null, true), which works on my PC and, when my Mac user tries it, he's able to use Data ConnectionsChange path to convert it to Mac format, ie Users/zzzz/etc

However, I'm trying to use the parameter to let him enter his own path details in the table and for that to be passed to the source line, ie

= Excel.Workbook(File.Contents(fnGetParameter("tblSetup",1)), null, true)

and this is where it fails.  It seems as though the simple act of passing the source address via a parameter is causing the issue.

I'll go back and watch Session 7.05 yet again, but I thought I was following its guidance.

Thanks again.

Pieter

Hi Pieter,

Maybe you can test it on another PC to see if it's a Mac issue of something wrong with the code. I'd say after all this testing that it's looking like a Mac limitation.

Mynda

Hi Mynda,

Yes, I've tried it with several other PC users and it works fine with them - it's just Mac users with Excel /Office 365, so it looks like a Mac issue.  Would you be able to suggest how I can reach out to a Microsoft / Mac expert to get further guidance?

Thanks.

Pieter

Hi Pieter,

I've emailed my MVP contacts to ask if anyone has managed to get this to work. I'll let you know.

Is the problem still a formula firewall issue?

Mynda

Hi Pieter,

One idea that has come back is to check if you can turn off the security settings in the query. You might need to do that in the Mac separately. On the PC it's query options > Current Workbook > Privacy > Always ignore Privacy Level settings.

Mynda

Hi Mynda,

Thanks - I appreciate that.  I've also posted on Technet (Guy Hunkin) to see if there's an answer there.

The Mac message doesn't say Firewall but the text is the same (attached).

The privacy settings were one of my first thoughts, but I recall reading somewhere that this is part of the query metadata, which isn't available on the Mac as that platform doesn't yet have query authoring.  I think that's also why the error message lacks the firewall error description.

Fingers crossed that someone responds with a positive answer.

Pieter 

Another thought: perhaps I could use VBA to modify the query source directly, instead of passing it as a parameter?

Pieter

I don't think you can use VBA to edit queries, but you could try. TBH I've never used VBA to edit queries.

BTW, you should let us know when you cross post questions on other forums so we don't get surprises when we ask for help on your behalf:

Get & Transform (Power Query) in Excel for Mac - Phase 1 - Page 2 - Microsoft Tech Community

The above link was shared when I asked my MVP contacts for help on your behalf. 

Mynda

Thanks Mynda,

Sorry - I didn't mean to embarrass you by cross-posting; TBH, it only occurred to me late in the day and I told you at our next exchange.  Anyway, I appreciate your efforts and very much hope something comes of them!

Pieter

No worries. I didn't see your comment about posting on TechNet because you followed up with another post and only read the last one thinking I had read the previous posts, so my bad. Next time please share the link to where you've cross posted.

Thanks - I've sent the folder to Guy Hunkin but haven't heard back yet.  I'll let you know any outcome.

Pieter

Hi Mynda,

I've heard back from the Microsoft Excel team [same link] and it seems that "...the Privacy Settings for all data sources on Mac are set to Private, meaning you can't share the data between the different sources. Setting the Privacy Settings to Private is intentional until we give you a UI to specify the data source settings in Excel for Mac."  Let's hope they come up with something soon; at least we weren't missing something obvious.

Pieter