I have pulled in several password protected workbooks into power query, if the workbooks are open it works fine. However when they are not I get "DataFormat.Error: External table is not in the expected format." I think this is due to the workbook being password protected. Is there anyway around this, perhaps coding the password within power query.
Hi Emma,
Yes, it's the password. The only way around it is to write some VBA that opens the files, enters the password and then refreshes your query.
Given that passwords aren't that secure in Excel anyway (plenty of tools online to crack Excel passwords), I'd be inclined just to remove them and instead, move the files to a secure folder that limited people have access to.
Mynda
Thank you, I unprotected the sheets and moved to a secure folder, works perfect.
