Input and Output password protected XLSX files in Alteryx

[BenMoss]

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.

[patrick_digan]

@BenMoss This is intriguing. I'll give it a try!

[MaryCondo]

Hi @BenMoss,

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!

Kind regards,

Mary

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)

[MaryCondo]

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.

Cheers,

Mary

[BenMoss]

Thank you Mary, I will take a look at the changes you made to resolve the issue!

Ben

[LizWurt]

How do we gain access to the sample workflow and workflow instructions? I just get this google permission page and I've requested access. Is it possible to post them in this thread rather than google?

[roblen]

This is super useful, Ben!

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...

Best,

Rob

[liliampumpernickle]

This is great, going to be everyday use for me. Thank you.
I also have one question, maybe by any chance you have figured out how to make it work for different users straight from BOX?

[MaryCondo]

Alteryx has a really helpful functionality called Workflow Dependencies that allow you to set any location instances to relative.

Here is an Alteryx Community article that goes through the various features and how to use.

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.

Cheers,

Mary

[liliampumpernickle]

Hi MaryCondo,

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.