Run Command to delete data from excel sheet

[Bhavika]

Hi There,

I have been trying to follow posts in the community for the below scenario but, couldn't figure out anything.

I have an existing excel with 6 data tabs and 2 pivots tables. I have been successfully been able to develop a workflow to have the data tables replaced and the Excel refreshes pivots on opening. But, before i run the workflow i manually open the excel and delete historic data in the data tabs. The output tool configuration i used is "Append to existing" . This way, i get the data out of my workflow into the Data tabs(which has been cleared of data but has headers intact) in the excel format that the file originally has. 

Challenge: Is there a way to open the excel and delete the historic data, re-save and close it through Alteryx. So, that i can use this before i Input the data to my existing workflow and process the output. This'll save a lot of my time.I know this can be probably be done using Run Command. But, not sure how to do it.

Can someone help me how to achieve it? 

Thanks 

[GrahamL]

You can use PowerShell commands via the Run tool to manipulate Excel sheets. Example site is https://sqlnotesfromtheunderground.wordpress.com/2014/01/25/modifying-excel-documents-with-powershel...

However, I don't understand why you can't edit the Alteryx workflow to delete the records first and then repopulate with the new data. I've attached a rough example

Cheers

G

[Bhavika]

@GrahamL

The user wants it to work that way. The historic data should be removed by Alteryx. 
I did try my luck with Run Command. So, i created a VB script (saved as .vbs). The file runs fine and deletes the data on the Data tabs, saves and closes the excel. So, i know that the script has no issue when ran without Alteryx. However, when i run it through Alteryx Run Command tool i get an error which i have no clue about. Below is the screenshot of my workflow and the error.

Below is the configuration of the Write Source under the Run Command

I have been struggling to understand why is Alteryx errors out when the VBS file runs fine.

Please help if you can :(

Thanks

[GrahamL]

ok - if it needs to be vbs then take a look at this thread where @jdunkerley79 has solved the issue and has posted an example

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-VB-script/td-p/64153

Cheers

G

[kk98]

Hi @GrahamL 

I checked your overwrite solution and it did help me with what I needed to do so thank you so much! :)

This might be a very basic question but what does the 1=0 actually mean in the filtering tool?

I can see what it's doing but I don't understand why it works.

Thank you in advance :)

[Rao2821]

Hi GrahamL

I had the similar issue and this workflow solved the problem. Thank you for the flow.

But have a question what is 1=0 in filter?

[rachelgomez]

If you click a cell and then press DELETE or BACKSPACE, you clear the cell contents without removing any cell formats or cell comments. If you clear a cell by using Clear All or Clear Contents, the cell no longer contains a value, and a formula that refers to that cell receives a value of 0 (zero).

Regards,

Rachel Gomez