Overwriting an "Updated By" signature for Excel Documents

[schuprov]

Hi All,

I currently have a workflow that utilizes grabbing an Excel file from a set directory that has multiple tabs, so I am joining that with an Excel input of the sheets from the file that I am looking for. After all of that is merged and I have an Excel path isolated I have it connected to a dynamic input tool. Here is where my issue lies. On a daily basis, we have an RPA bot that saves these Excel files from vendor portals. When it is saved the Updated By signature in properties of the Excel file is by the Bot which is not labeled as an internal employee of my company so then my dynamic input does not read in as it gets blocked by the Microsoft Security Center. To get around it, I have to manually open each file and click "CTRL+S" and the file updates the signature to my name and allows Alteryx to read it in. 
Since I would like to schedule these workflows without having to manually intervene I was wondering if there is a solution that I can implement to have Alteryx update the file by simply saving it before it goes into the dynamic input. Wondering if one of the Interface tools can help here or the Python tool.

Thank you,

[Yoshiro_Fujimori]

Hi @schuprov ,

I understand Alteryx can add any data transformation to a file from outside only after it is input to Alteryx (by definition).

So if Dynamic Input is rejected, I cannot think of any workaround on Alteryx.

You may want to see the feasibility to

(1) change the "Updated By" name when RPA bot saves the file or

(2) let the Microsoft Security Cener to whitelist the user name of the RPA bot.

As it is related to the security policy, either way can be sensitive, but I am afraid this is all I can comment.

Good luck.

[ArnaldoSandoval]

Hi @schuprov 

Based on your comment above

To get around it, I have to manually open each file and click "CTRL+S" 
and the file updates the signature to my name and allows Alteryx to 
read it in. 

I prepared the attached Alteryx workflow, this is what it does:

• List all the XLSX files on the target folder defined by the Directory tool.
• It write several command lines to a batch file.

cscript //nologo VBA_LoadSave.vbs "Excel file name"

The "Excel file name" is the name of each Excel file found at the Target Directory defined by the Directory tool.

The VBA_LoadSace.vbs is a VB script that load the Excel file and immediately save it.

• The workflow writes to the file: Copy_Batch.bat all the loadsave lines.
• Once the Copy_Batch file is written, it executes it with the Run Command tool.

I am attaching the workflow and the support file: VBA_LoadSave.vbs

Notes:

• The batch file load and save each Excel file, so this process could take its time depending on the size of your files.
• You should test this workflow on your environment, let us know if it does the job.
• The attached package contains all what you need.

Hope this helps,

Arnaldo

[schuprov]

Hi @ArnaldoSandoval  this seems to be working for one of my directories, but I am getting errors when I try to run it for others.

For each environment, I have multiple directories in which files are saved. I have added an additional sample and sort tool to only take the most recently updated file and it worked on the first environment I ran it on. Then I created a copy of the workflow and updated the directory in which I wanted it to go and that gave me an error.

The run command tool is giving an error "Returned an error code:1". Do you know what could be the solution for this?

Edit: It looks like when I nest each separate directory update into its own folder I no longer have any issues with running this.  I am going to schedule this on the server to make sure it works on a daily basis and let you know how it goes. Thank you so much for your help!

Thank you,

Semeon

[ArnaldoSandoval]

Hi @schuprov 

Interesting, please keep us all posted with the outcome of your test, and flag my suggestion as a solution if it resolved your issue,

Arnaldo

[schuprov]

@ArnaldoSandoval so it appears that my run today failed since Alteryx is unable to over-write the batch files, when i ran the process manually it gives me an error that the file is already in use by another process, if I add a 1 to the batch file name and change the run command tool to run that file it works though. I wonder if you have any suggestions on how to get around that issue.

Thank you,

[ArnaldoSandoval]

Hi @schuprov 

I will take a look at it first, I will get back to you.

Arnaldo

[ArnaldoSandoval]

Hi @schuprov 

I slightly modified the VBA_LoadSave.vbs script (new version attached)

These are the changes to the VBA_LoadSave script:

hth

Arnaldo