Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

AkisM
10 - Fireball

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?

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
AkisM
10 - Fireball

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
20 - Arcturus
20 - Arcturus

@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).

 

capture.png

 

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

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
AkisM
10 - Fireball

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
20 - Arcturus
20 - Arcturus

Nope. Just point it to your script. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels