Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Using Events or Run Command to open & save excel files in a workflow, before outputting

Highlighted
7 - Meteor

In my workflow I need to open some excel files and save them before I can actually output data to them.

 

It looks like this can be done by creating an Event, or using the Run Command tool, but I'm not sure how.

 

Can someone please help me with this process?  Is this doable and what's the best approach?

 

Thank you,

Katy

Highlighted
Alteryx
Alteryx

Hi Katy,

 

Thanks for your question! To do this, you will need to use a VB script. There are many great resources online which you can find to learn the correct VB commands. 

 

It will look something like this if you want to rename the newly saved file

 

set objExcel=CreateObject("excel.application")
set objWorkbook =objExcel.workbooks.open("C:\Users\aglum\Desktop\Alteryx Resources\Customers\Sharp\Hector Use Case - VB Script\SRC_to_STG_STM_Template.xlsx")
objWorkbook.SaveAs("C:\Users\aglum\Desktop\Alteryx Resources\Customers\Sharp\Hector Use Case - VB Script\NewRenamedOutputFile.xlsx")
objWorkbook.Close
objExcel.Quit

 

or like this if you want to save as the same name:

 

set objExcel=CreateObject("excel.application")
set objWorkbook =objExcel.workbooks.open("C:\Users\aglum\Desktop\Alteryx Resources\Customers\Sharp\Hector Use Case - VB Script\SRC_to_STG_STM_Template.xlsx")
objWorkbook.Save
objWorkbook.Close
objExcel.Quit

 

I've attached an example showing how to create the script and run the script in a workflow. 

 

Hope this helps!

 

Amelia

 

Highlighted
8 - Asteroid

@AmeliaG 

Hey! This is helpful. However, I was just curious to know how is it working. I replaced the "your filepath" with the filepath on my machine and in the Run Command configuration, i updated to  "C:\Users\zkrn0lg\Desktop\vbscript.vbs" in both Write source and Run External Program.

But, it doesn't open the excel i have given the path to. Rather errors out saying "Failed to run the external program "C:\Users\zkrn0lg\Desktop\vbscript.vbs:: %1 is not a valid Win 32 application.

 

Could you please help me with this. 

 

Also, i tweaked the VB script a bit, since i want to delete data from a tab and then save and close it.

 

Thanks.

 

Labels