... byt the way, I know that the Mac uses a different fille path structure and have catered for this in my workbook through VBA.  I've also read the Microsoft tech blog, which (02 Sep 2020) confirms that it should now support Excel tables, which is how my data file is set up.

Hope this helps.

Pieter

Hi Pieter,

Yes, in Excel 365 for Mac. Do they have 365? Also, Dropbox is a web query, not a local Excel file. This may be the difference.

Mynda

Hi Mynda, thanks for thst and, yes, they have 365 (fully updated).  I know that Dropbox is a web service, but I thought that with the desktop app files were syncronised with the local drive (eg C:users<name>Dropbox etc).  Wouldn't that make the files local? 

I've now made a bit of progress with him and he's reporting error messages "query xxxx references other queries [or data sources] that have privacy levels which cannot be used together.  Please rebuild this data combination." and similar.  On a Windows version I'd use Query Options/Current Workbook/Privacy and set to Ignore the privacy levels, but my friend says he can't see that option - I'll check.

Are we on the right track?

Pieter

Hi Pieter,

If you're referencing the Excel file using your hard drive path, then that should work fine.

Sounds like you're making progress. Please see lesson 7.05 that covers formula firewalls which should address the issue with referencing other queries.

Mynda

Hi Mynda,

I hadn't got that far in the lessons yet and that's really helpful, thanks.  I've sent a series of diagnostic tests to my Mac-using colleague for him to confirm exactly where the issue might lie and then I'll set about rebuilding the queries.  I can probably also achieve that by reverting the workbook privacy levels on my PC to Default and doing the same checks on my machine.  I'll report back.

Pieter

Hi Mynda,

Your hint was invaluable and got me a long way down the path.  I had a filter on the original data, driven by a selectable value in a parameter table.  I still got a firewall message if I applied the filter after the staging query, but including it in that query seems to have resolved things.  I wonder if this is because Excel is a bit funny when it comes to deciding what's internal and waht's external data.

Incidentally, I'm glad that I came up with the idea of resetting my privacy levels as that got me a long way towards identiying the issues.  Troubleshoot on someone's Mac by email and phone is a dead loss, largely because he wasn't able to step through the M-code to see where the problem was.  On reflection, I guess that's because Excel 365 for Mac still lacks query authoring(??) so that route's not open to him.  They still have a long way to go to achieve convergence between the platforms, but it's getting there.

Thanks again for your help.

Pieter

Great to hear, Pieter!

Yes, PQ can't always tell if an external reference will lead to a privacy breach, so sometimes it's overzealous as a precaution. Glad you found a solution though.

Yes, PQ for Mac doesn't have query authoring yet, but they're working on it.

Mynda

Hi Mynda,

Sorry to have to come back on this one.  My Mac-using colleague says he can't reference the .XLSX file.  I directed him to Data / Get Data / From File / From Workbook but he tells me that apart from From Text /CSV and From XML, other options are greyed out.  Am I advising him wrong or is his version of 365 not so up-to-date as he believes?  I'd be grateful for any advice!  Thanks.

Pieter

Hi Pieter,

As far as I'm aware, in Excel for Mac you cannot author queries, you can only refresh queries build in Excel for the PC, as explained here.

Mynda

Hi Mynda,

Thanks - I was about to go down a rabbit hole and you pulled me back from the brink.  I was using Get Data etc as a (misguided) diagnostic tool.  Here's the current position:

My workbook is designed for Windows and Mac users.  I use VBA to concatenate User profile from system attributes with the data path entered by the user to create a line in my parameter table; VBA also corrects path delimiter directions etc.   This is then used to run / refresh the table.

Thus when I run it the table contains, for example C:UsersPietDropboxTestdummy.xlsx etc and when my Mac user runs it, it contains Users/Fred/Dropbox/Test/dummy.xlsx etc.  He's confirmed that this is where it is on his Mac.

The first query populates a table with data from Dummy.xlsx and runs fine on Windows.  My Mac user gets an error "(DataFormat.Error) The supplied file path must be a valid absolute path".  I think this is telling me that the Query is firing but stalls because it doesn't recognize the path as being correct and I'm trying to work out why.

The Mac path looks fine on the screenshot he sent me, which is why I was trying to see if he could pull the data directly.

My plan now is to use a plain parameter table and a test table.  I've already established that the query can refresh from a table on the same worksheet, so I'll move it progressively further away (ie same book, different sheet, different workbook in same local folder etc)  Does that seem a reasonable approach?

Pieter

Hi Pieter,

I'd write the query using Get Data > From File which will hard key the file path. Then edit the query and put the Mac file path in place of your file path and see if they can refresh it on the Mac.

Otherwise, I don't know, sorry.

Mynda

Hi Mynda,

Thanks for the suggestion and I'll try hard coding the query as you suggest instead of using a parameter table, though it'll severely limit the ability to roll it out to multiple Mac users.  If the hard coding works I'll then investigate further to see how I can pass a parameter instead.  It's really frustrating that there's not more information available!

Thanks for your support.

Pieter

Hard coding it is just to narrow down where the issue lies and make sure it's not the file path itself.

Hi Mynda,

It's taken me a bit of time to link with my Mac-using tester but I finally have a clearer picture of what's happening.  I hestitated to post because it seems rather strange, but if I hard-code the path into a query it runs with an error on the Mac, which the user is able to overcome by going to data / Connections / Change path, after which the query runs - to my delight!

However, as soon as I introduce a parameter, it fails with a firewall error.  I think that this goes back to the issue you mentioned earlier, where a staging query is used to buffer the queries from external data sources.  The trouble is that it needs the parameter to identify the source in the first place.  Does this seem plausible?  It seems really strange!  I've attached a picture.

Can you suggest a way round this, or a different approach, or have I got the wrong end of the stick?

Pieter