Excel External Data Connection Permission

Question

Hi Experts,

I am meeting an issue regarding Excel API connection as Data sources. I am successfully getting data from the web via OData. The permission method is basic authorization, which means a username and password are required.

However, when I shared this file with my non-IT coworkers, the excel required them to enter usernames and passwords. However, this username and password are confidential and cannot be shared.

After testing other APIs, I realized that excel will store the permissions locally. I had an idea that we can get these permission local files so that we can put the files on my coworkers' PC. If so, they would not have to enter usernames and passwords again and the info also won't be exposed.

Does anyone have an idea where the permission files are or a better solution for sharing API data without entering usernames and passwords? Appreciate you!

Answer 1

Hi,

Thanks for posting in our community.

Generally, the username and password will be asked when the workbook is opened on a new device as the file sharing won't transfer the login cache folder. And kindly check if the Privacy levels setting can help anything for your scenario.

For reference:

Manage data source settings and permissions (Power Query) (microsoft.com)

Set privacy levels (Power Query) (microsoft.com)

Authentication with a data source - Power Query | Microsoft Docs

If the privacy setting can't meet your scenario, we may be sorry to convey that the currently there has no other out-of-box way to eliminate the feature limitation for Excel Data Connection to store the credential when moving the workbooks. However, we'd like to suggest you give it another try in Microsoft Q&A Community where engineers there focus more on developed and customized feature design with Office apps. You may get some useful ideas there.

Meanwhile, we will keep this thread open and see if any Excel MVP or community member experienced at VBA can share any other possible workarounds for your scenario. Appreciate all the understanding and patience!!

Best Regards,

Mia

Answer 2

Hi Mia,

Thank you for replying and providing the information.

I have submitted the question on Microsoft Q&A Community.

Best,

Zhen