My colleague (Phil Lowe) and I have been working on developing two macro's for use by the community. These macro's are designed to allow users to input data from a password protected XLSX file and output data to a password protected XLSX file.
The input macro can be found on the gallery here: https://gallery.alteryx.com/#!app/Input-Password-Protected-XLSX/5a9d23a7826fd31b1c2826ea
The output macro can be found on the gallery here: https://gallery.alteryx.com/#!app/Output-Password-Protected-XLSX/5a9d074e0462d716682045a9
These macros are very much in beta and we are already aware of a couple of known issues but the idea of posting these here is to gather as much feedback as possible from the community so we can improve the macros and make them more reliable.
Current known issues
In order for the macro to run successfully the workflow with which it is within must be saved else the end user will get a write error.
The macro does not run on parallels.
A single error ' Designer x64 The Designer x64 reported: Error running Event #1: The external program ".\deleteOutput.bat" returned an error code: 1: The system could not find the environment option that was entered. ¶ (203)' will always appear. This does not appear to affect the functionality of the tool.
How the macros work
The input macro uses a visual basic script to temporarily generate an unprotected excel file. The macro then uses the dynamic input tool to read this file into alteryx before a clean up script is applied on completion of the workflow to delete this unprotected file and other generated scripts.
The output macro writes the data stream as an unprotected file before a visual basic script creates a copy of this file and protects it with the password specified by the user within the macro configuration. A clean up process then takes place to remove the unprotected copy of the file and all other scripts.
