Hi all,
Has anyone had any experience trying to import a password-protected xlsx file?
I’m currently faced with the error message: ‘File has wrong format. End of Central Directory record could not be found. Worksheet name cannot be changed.’
Has anyone any suggestions?
Apparently, EasyMorph can’t load password-protected Excel workbooks at this point.
I did a bit of research – it’s possible to remove/disable the password using a VBA macro. The macro can reside in another workbook, which gets executed from the command line [hint].
Alternatively, this can be done using a PowerShell script, something like that (not tested!):
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("C:\myfile.xls",0,$False,1,"password")
$workbook.Password = ""
$workbook.save()
$workbook.close()
$excel.quit()
In v3.7 there is the PowerShell transformation that can be used for running PowerShell commands. Or simply run a script from command line using “RunProgram” and PowerShell executable.
Once the password is removed the workbook can be loaded in EasyMorph in a regular way.
Actually, the script does work 
Here a working example (v3.7+ required).
remove workbook protection.morph (1.5 KB)
Cheers Dmitry!
Hey @dgudkov
Has there been any advancement on the best way to accomplish this or is the PowerShell script referenced above still the best way?
I have an additional wrinkle. Is there a way to insert the {File} and {Password} from columns available from the previous action? I am using “Get File” to generate a list of files in a certain location, then “Filter by Condition” to only have the one file I want to process through the Morph, and then “Calculate New Column(s)” to create the password (that changes monthly to reflect the date in the password). The PowerShell action would come after the “Calculate New Column(s)”.
Any help here would be very appreciated.
Thanks!
Hi Tanner,
The reply above is still there recommended way.
Move the "PowerShell" action into a new module and use the "Iterate" action to run that module once. Define a parameter (e.g. "Password") in the iterated module and assign it with a column value in the "Iterate" action. If you're new to iterations, check out this tutorial: EasyMorph | Loops and iterations
Worked perfectly iterating the script as its own module.
Thanks for your help Dmitry!
You’re welcome 