Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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...
Need Help
Show and Tell
  • p45cal's avatar
    p45cal
    1 year ago

    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-attachments-to/aeb121c8-e481-4f15-8b8e-39c5dc76e8fb

    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.

p45cal's avatar
p45cal
Solution Supplier
1 year ago

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-attachments-to/aeb121c8-e481-4f15-8b8e-39c5dc76e8fb

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.