VBA to refresh Power Query on proteched workbook

Question

I have two files:

• A protected data source (File A)
• A report (File B)

File A is connected to File B via Power Query (data source | Workbook | .xlsx). When I want to refresh File B (Data | Refresh All) I get an error message "[DataFormat.Error] File contains corrupted data"

Found out this is related to the protection of File A. So when I open File A after providing the password the data refreshes.

Here's what I'd like to do:

• Get some VBA code to open File A (with the password VBA)
• Run the query to refresh the data - how would this query look like in VBA?
• Close File A and ensure it remains password protected

Related to this is it possible to open File A in the background without the user seeeing it?

Answer 1

Hi @Thomas Meier ,

Thank you for your feedback.
We are aware of this issue, but we do not have a time estimate on when it will be fixed.
In the meantime, you can vote for this idea on User Voice.

Thank you,
Inbar, Excel Team