How do we automate reporting using Excel Files (password Protected)

[Anonymous]

We receive excel file as an attachment each week (password encrypted) and as per the company policy they cannot remove password from the Files before sending via email.

we then pick this file and follow below (MANUAL STEPS)

1 download the attchment

2 Remove passowrd from excel files

3 save it in a location (password removed)

4 We then use excel connector and point PBI dataflow to this location 

5 the dataflow then processess business logic (in power query) and reports connected to this dataflow get refreshed

since the file is password protected power bi dataflow fails to refresh with password encrypted files, Is there any other way through which I can pick this file from outlook (inbox) and refresh the data without doing above manual steps, i want FULLY AUTOMATE the process, do we have any other connector (in Power query) that works with password protected files and yet refreshes the data 

Below is the error that gets thrown using password protected file 

[v-sgandrathi]

Hi @Anonymous,

Thank you for being a part of the Microsoft Fabric Community.

As @p45cal  mentioned that will address your issue.

we haven't heard back from you regarding this and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out to us!
 

Regards,
Sahasra.

[v-sgandrathi]

Hi @Anonymous,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thanks for using Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi @Anonymous,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can also vote on that. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated. Thanks for using Microsoft Fabric Community Forum.

[p45cal · Accepted solution]

It would be 'nice to have' the functionality within Power BI/Power Query to supply a password to  grab data from from Excel files but I don't think it's happened (yet?).

I'm pretty certain, vba (or perhaps an Office Script) can do most of the steps, and perhaps even refresh the query(s). It would involve some coding to grab the attachments (from Outlook?), save the workbooks, unprotected, in a given (secure?) location and let Power BI do its stuff. I don't think there'd be a need to point the PBI dataflow to this location as it would remain the same. The vba code could place/remove just what files you want to remain there.

This thread may be useful: https://answers.microsoft.com/en-us/outlook_com/forum/all/automatically-saving-of-outlooks-attachmen...

So rather than being fully automated, it may require a 2 step process (2 clicks?), one to grab attachments, unprotect the workbooks and save them somewhere, and one to update Power BI.