Alteryx to read and output password protected Excel Files

[wwc008love]

Alteryx Community,

I am looking for some help to let Alteryx to read and output password protected Excel Files.  Besides using R or Python, look like there're old discussions on using a macro. However, look like that macro was taken off the shelf some time ago. 

Anyone can offer help if the macro is now resurrected?  Much appreciated. 

I am searching if we have a macro to 

1) Output to an Excel file with password protected

2) Be able to access an existing password protected file to then be able to append to it

Many thanks! 

[wwc008love]

Thanks. I am glad it was working.  We recently adopted this Python solution in our daily work. 

I have updated the workflow by adding this piece of code.  I am not sure what error was given by Python for real because you will need to comment the Try/Except/Else to see the real error. 

One error we had is because the codes generate a temp file in local drive and cause error. The temp file can be removed by using PowerShell command.  I developed this piece of codes to remove this temp file each time Python codes run. This might help you resolve the issue.  

I am not sure if you are using block-until done tool. If that's the case, in #1 you can decode password, #2 to read/work on the workflow, #3 to encode password.  However, you may still get an error saying the file is open.  There is one workaround we used is to use the "Wait a Second" tool.  Basically, this tool can help the workflow pause for a second and then proceed. 

Below is the link to CREW MACRO ("Wait a Second" tool is part of the bundle)

https://community.alteryx.com/t5/Dev-Space/Crew-Macros-Download-Link/td-p/1040387

Below is the piece of codes to remove temp file using PowerShell run in Python.

This is been added to the new workflow already

import subprocess

command = 'powershell.exe Remove-Item -path $env:LOCALAPPDATA\Temp\gen_py recurse'

def call_command(command):
output = subprocess.call(command)

if __name__ == "__main__":
callcommand(command)

[wwc008love]

Thanks. I am glad it was working.  We recently adopted this Python solution in our daily work. 

I have updated the workflow by adding this piece of code.  I am not sure what error was given by Python for real because you will need to comment the Try/Except/Else to see the real error. 

One error we had is because the codes generate a temp file in local drive and cause error. The temp file can be removed by using PowerShell command.  I developed this piece of codes to remove this temp file each time Python codes run. This might help you resolve the issue.  

I am not sure if you are using block-until done tool. If that's the case, in #1 you can decode password, #2 to read/work on the workflow, #3 to encode password.  However, you may still get an error saying the file is open.  There is one workaround we used is to use the "Wait a Second" tool.  Basically, this tool can help the workflow pause for a second and then proceed. 

Below is the link to CREW MACRO ("Wait a Second" tool is part of the bundle)

https://community.alteryx.com/t5/Dev-Space/Crew-Macros-Download-Link/td-p/1040387

Below is the piece of codes to remove temp file using PowerShell run in Python.

This is been added to the new workflow already

import subprocess

command = 'powershell.exe Remove-Item -path $env:LOCALAPPDATA\Temp\gen_py recurse'

def call_command(command):
output = subprocess.call(command)

if __name__ == "__main__":
callcommand(command)

[VOparaji]

Hi,

Please I need your help. I am very new to Alteryx and I am trying to password protect my output excel files as well. I used your Python script and and followed the steps but I get the error that the files were not passworded. I want to encrypt multiple files and I also entered all of them in the text input tool in different rows.

The IF is returned.

I copied your code verbatim so I am not sure what I am doing wrong. It doesn't encrypt the files but it reads them.

[VOparaji]

I found out it was an issue with files stored in OneDrive synced from SharePoint. It encrypts all other files in other locations.

[C074584]

Hi team Alteryx,

I am new to Alteryx and the community.  I have a unique situation where I have a bunch of excel files (all the same format but different weeks) that I need to union together into one file. They are all password protected with the same password.  Will something like this work for that?  Is it even possible to combine multiple password protected excel files even if the passwords are the same? 

[Mhmd]

Hi bmillrine,

Thank you for your effort. The workflow is working 100%, but I have a question that I'd like some clarification on.

Every time I run the workflow, two VBS files are generated: Protect.vbs and Password Protected.vbs. Could you please explain what Password Protected.vbs represents and where it is generated from?

Additionally, in the Running Command tool, when we set the write source to .\Password Protected.vbs, what does that represent?

Thanks, and I look forward to your response soon.

[AlexAung]

any sample workflow to use this macro?