Alteryx to read and output password protected Excel Files

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!

Developer

Developer Tools

Custom Tools

Macros

Interface Tools

Accepted answers

bmillrine

Try this one make sure the filename has .xlsx at the end

Output Password Protected XLSX V_BM.yxmc

All comments

apathetichell

Password protected files are not supported by Alteryx out of the box. I use R. someone else probably uses Python. Maybe someone uses run command.

bmillrine

I had the same challenge finding it and eventually did. Working on fixing it at the moment then I will add fixed version.

wwc008love

Thank you so much !! This looks like it. However, when I try to test the macro to read an excel file, it gives me an error message.

Any idea how I can get the macro work. Much appreciated your help. Thanks !!

open pwd excel test.yxmd

bmillrine

Try this one make sure the filename has .xlsx at the end

Output Password Protected XLSX V_BM.yxmc

ricardo_takimoto

Hi, thanks for the response. I'm using the Python solution. It had been working perfeclty by removing the password, runing the rest of the workflow and then protecting the file again. But now, when I run the workflows I get the message that "Password Removal Failed" from the Python tool.

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)

Python Solution_v5.17.yxmd

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.

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)

Python Solution_v5.17.yxmd

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?

robertalteryx

Hi @wwc008love and all! Looks like the Python solution_vNew22.yxmd works only partially. Our workflow was able to remove the Password protection but after executing other steps to read and write to the unprotected file, the Excel file was left unprotected. In other words, the Protect Workbook with Password (Step 4) did not execute correctly. Is there maybe an update file you could provide?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703