How to hide the location of a data source

[dbrault57]

Hi all,
Since the Application.CommandBars Visible / Enabled seems to not work anymore, I was wondering if it was possible to hide or disable certain menu items from the ribbon?
In fact, I want to hide the location of a specific data file used as a target for Power Query and didn't find a way to do it, and cannot avoid any open-minded user to seek the location and modify this file manually.
Thanks in advance

 

[johnny51981]

What is done with the file once the linked data is refreshed? Like, do you distribute it as a report or is it a template that is used by others and they need to hold connection to the source data?

 

[dbrault57]

Hi Johnny,
It's like a user form to enter values that are posted to another "target file", but I don't want all users that access this form to see where the target file is located.
In the meantime, I found a solution:

• in the Sub Workbook_Open code
• I added a dynamic modification of the Excel.officeUI parameter file located in the C:\Users\xxx\AppData\Local\Microsoft\Office folder
• to include this tags

<mso:tabs>
<mso:tab idQ="mso:TabData">
<mso:group idQ="mso:GroupPowerQueryData" visible="false"/>
<mso:group idQ="mso:GroupConnections" visible="false"/>
</mso:tab>
</mso:tabs>

• In the Sub Workbook_BeforeClose code, I reset the previous values to True

And it works perfectly
Thanks