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.

[lssilva]

Thanks DevZig! this was super helpful. Do you think you could share your macro?

[suyunn]

Thank you @DevZig1103! Would you be able to share your macro?

[DigitalPlumberDave]

Thank you @BenMoss this is great.

How would I deal with a file name that changes based on today's date?

I output an xlsx file with today's date in the name. 

How can I feed that dynamically changing name into the macro?

See example workflow.

Thank you in advance.

[jromano]

Hi @DigitalPlumberDave and @BenMoss 

Did you find a solution for your workflow?

I need to output the file name and path used in the questions box. Please, could you help me?

Thank you

[BenMoss]

Hey!

You would need to place the output pp xlsx macro into a batch macro, and used the control parameter to overwrite the value in the filename text box, something like in this image...

Ben

[jromano]

Awesome! Thank you a lot

[jromano]

Hey! @BenMoss 

I'm sorry but could not set up the "output pp macro" to receive the input macro. Can you share your workflow?

Thank you! 

[KarolinaRoza]

Hello,

I am trying to use the macro but I am receiving errors and can not handle this.

When I run the macro I see that the protected file is being open, new file is being saved ("file vNoPassword.xlsx"), and then macro stops working. There is an error that: "Error Opening file: C:\Users\...\file vNoPassword.xls: The process cannot access the file became it is being used by another process."

I have no idea why this error appears and I can not progress with the macro.

Could anyone help me?

Thanks a lot,

Karolina

[jromano]

Hi @KarolinaRoza 

Steps that work for me with a similar issue:

Be sure that you do not have the file already open in excel.

Save the workflow and import the package.

Use the macro output to input your parameters and run the linked workflow =  

[KarolinaRoza]

Thank you for your tip.

I noticed that the issue is most probably due to files saved on Box. Even in the macro there is tool : Block Until Done, it fails on second step. Finally I applied different solution: I protected the cells in the file instead of whole file. It is fine for my case.

Thanks.

Karolina