I’ve started a new thread as I’ve moved a bit away from my original (PQ on Mac) topic, though that’s still relevant. Apologies for the post length – there’s quite a lot of background!
Issue: Using a parameter table with default privacy settings causes a firewall issue. Changing privacy settings is not an option.
I have a workbook that draws data from an .xlsx file on Dropbox, accessed as a local file (eg C:Users<name>Dropboxetc for PC users). Copies of the workbook are used by several Windows users and I want to make it usable by Mac (Excel 365) users as well. Individual users need to filter the main data tables to provide information relevant to themselves.
I have now discovered (see earlier post) that it’s not possible to pass a file path to PQ on Mac as Privacy Settings for all data sources on Mac are set to Private. I shall tell Mac users to use Data>Connections>Change File Path, which isn’t too bad. Because the workbook needs to work on both platforms, I reset the Windows privacy settings to default (“Combine data according to each file’s privacy level settings”) and looked for a solution that would work under these conditions.
I was able to use staging queries (Lesson 7.05) to perform query merges but still hit a firewall snag when trying to pass filter parameters. It thus seems that using a parameter table isn’t a viable option for Macs, so I needed an alternative approach.
My solution was to base a query on a worksheet table containing the filter value and use that as the “one” side of a merge with the main data query. The result was a table containing the filtered values, which is what I wanted.
This works fine on a PC but not quite so well on my tester’s Mac. After initially trying to refresh, hitting an error and changing the file path he can re-run the queries, which then largely work: if the main data query is loaded to a worksheet, the filtering query based on it works OK. However, if the data query is changed to connection-only the filter query fails. I can’t work out why and would appreciate any pointers.
My tester is not an Excel programmer and I’m having a diagnosis session with him tomorrow, so I’d like to know what I should be asking him to do.
Many thanks,
Pieter
Hi Pieter,
You didn't say what the error is, so it's difficult to say. However, presumably there aren't any queries that are directly referencing the query output table containing the filter parameter (as opposed to the query directly), which when set to connection only is no longer there to reference?
Mynda
Hi Mynda,
Apologies - I conflated two issues. The error message I referred to was when my Mac tester ran the queries "as-is" with my original path in the queries; the path was naturally wrong for his Mac so he used Data>Connections>Change File Path to set the correct path. After setting the correct path he was able to refresh the queries.
Here's a picture of the query relationship:
tblSelection should contain the filtered values from query tblSales, but when he refreshes the query it remains blank. However, if I send him a modified version, with tblSales loaded to the worksheet, the filtered values (tblSelection) are displayed. Since tblSelection is drawn from the query and not the loaded table, I'm a bit stuck. I'll have more details when I quizz my tester the afternoon (UK time). In the meantime I've attached my test workbook (yellow tab). The grey tab just contains a flat copy of the data source file, held on Dropbox.
Thanks,
Pieter
Hi Pieter,
Thanks for clarifying and sharing the file. I bet you're wanting to tell your tester to get a PC 🙂
I can't see any reason why loading tblSales to a table in the worksheet would make any difference to the query working/not working, but obviously there must be something going on in the background that is allowing the query to execute when it loads to the worksheet. Is it possible to just allow it to load for the sake of the Mac user?
Mynda
Hi Mynda,
Good morning - for you. early evening here.
I fully agree with your suggestion for the Mac tester; it was one of my politer thoughts, followed by breaking his fingers and worse!
In any event, I had my online session with him and was able to watch his steps on my PC. Funnily enough, when he did exactly what I told him to, it all worked just fine. So, in essence, I don't think this thread adds any value to the greater community and perhaps you'd be able to pull it?
The take-away, of which I'm quite proud, is that it seems that you can't use a parameter table with default privacy settings, which are automatically set to "Private" for the Mac at the moment. Therefore, if you want to pass a filter value to a query, my solution is to base a query on a worksheet table containing (just) the filter value and use that as the “one” side of a merge with the main data query. This gives the required filtering without the need for a parameter and function.
Hope this helps and thanks again.
Pieter
Phew, that's a relief!
