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.
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.
Thanks for sharing this macro. I tried running, but the process errors out because the workflow is unable to read the vNoPassword.xlsx file created.
The script does generate the document, but the dynamic input tool is unable to read as it is saying the file is being used by another process.
Any idea how to resolve this? Thanks for your help!
Error: Input Password Protected XLSX (1): Tool #30: Unable to open file for read: C:\Users\A86276\Desktop\Test Folder\Test File vNoPassword.xlsx Error Opening file: C:\Users\A86276\Desktop\Test Folder\Test File vNoPassword.xlsx : The process cannot access the file because it is being used by another process. (32)
Following up-- I tweaked the macro settings within the Text Input to reflect my folder and file settings instead of the macro preset and it resolved the errors that I was seeing.
I thought what the user inputs with regards to the folder and file details would have overridden the general settings within the macro, but glad it is working now. Hoping this helps anyone else experiencing this issue!
Thanks again for sharing this tool with the Alteryx community.
Note: If it's not working, please first try to disable (possibly) all Excel add-ins. I wasted several hours trying to figure out why the workflow is not working and found out it's because of a "Hyperion" add-in. I was getting VBS Code Error 800A0401 until I realized it had been that nasty add-in the entire time...
If you set a pathway to relative, example: C:\Users\YourUserID\Box Sync\Targeted Folder, then any user that has that folder synced to their desktop, regardless of their user ID, should be able to access the referenced instances. You just need to ensure that the general pathways are the same.
Thank you for quick answer, therefore I am familiar with Workflow Dependencies which I use in my workflow too. Difference that in this workflow we use Text Input tool and same approach does not work for me. Not sure if I do something wrong or it's not even possible to append that.