Is it possible to remove formulas from an existing excel and keep only their values?

[AkisM]

I am making a workflow (to be run from gallery by users), that writes data to an existing excel template with lots of formulas, macros etc. That template is packaged as an asset into the workflow so the user can download the finished excel.

Is there a way however to keep only values from the finished product? I don't want the user to be able to look at the formulas or have a way to reverse engineer the spreadsheet. Maybe after the files are written to the template, a second workflow is triggered and this workflow takes the previous file with the updated records and re-writes the data as plain values instead of formulas?

Any ideas on how to achieve that?

[MarqueeCrew]

@AkisM ,

How are your VBA skills?  You can use alteryx to help you to execute a script to copy each page and paste values.  Alteryx itself doesn't perform this programming task.

Cheers,

Mark

[AkisM]

Hi @MarqueeCrew  , my VBA skills are non existent, but I can figure it out, and the vid you linked should be very helpful, thanks. One question though, how exactly do I tell my alteryx workflow to execute a script? And in which part of the workflow would that be? I'm guessing I would use a block until done tool, which would first write the stuff in the sheet, then it would run the script that replaces all formula values in the sheet with plain values. But I've never ran a script in Alteryx before. Also, would this still run as a gallery app, as long as the script is packaged as an asset in the workflow? Because ultimately that's the end goal.

[MarqueeCrew]

@AkisM ,

Inside of the configuration panel there is a cleverly hidden "EVENTS" tab.  You can configure it to run your script after the workflow runs (with or without errors).

I hope that this helps.  It's a great learning exercise.

Cheers,

Mark

[AkisM]

Does that mean I have to use the Alteryx command line? I remember reading that that's doable only on the server machine, correct me if I'm wrong, and in my organization the computer I build workflows from isn't the server machine.

[MarqueeCrew · Accepted solution]

Nope. Just point it to your script. 

cheers,

 mark