community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Input and Output password protected XLSX files in Alteryx

Alteryx Certified Partner
Alteryx Certified Partner

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.

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

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)

 

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

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

Ben
Atom

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?

Labels