disable connection and query ribbon by vba

Question

I have a file that makes several queries to other excel files and to a SQL server using PowerQwery.

I want to prevent users from accessing or editing/deleting the query.

How can I prevent access or disable connection and query ribbon with VBA Code in an unprotected file

I know it is possible by protecting the file, but I would like to check how this can be done in an unprotected file

Thanks

Answer 1

To hide the "Queries and Connections" section from the ribbon, you could use a Custom UI XML file and add it to your Excel file (more details on how to do that in this article).

Before:

After:

I'm using Excel 365, so the following worked for me:

[FileName: customUI14.xml]

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>

            <!-- Set visible to false for the Queries and Connections on the Data tab-->
            <tab idMso="TabData">
                <group idMso="GroupDataQueriesAndConnectionsWorkbookLinks" visible="false"/>
            </tab>

            <tab idMso="TabData">

                <!-- Replace the group with an empty one -->
                <group id="DupGroupDataQueriesAndConnectionsWorkbookLinks" label="Queries &amp;&amp; Connections" insertBeforeMso="GroupDataQueriesAndConnectionsWorkbookLinks">

                </group>

            </tab>

        </tabs>
    </ribbon>
</customUI>

This code is inspired by this answer, but I've left the group empty since we don't want any buttons there.

If you are using a different version of Excel, you might need to replace GroupDataQueriesAndConnectionsWorkbookLinks with a different group name. Note that you can find a list of the official group names in this repository: https://github.com/OfficeDev/office-fluent-ui-command-identifiers (Select the folder corresponding to your version of Excel and download the file excelcontrols.xlsx).