Open, Save, Close Excel File

[JoeChia]

I have a workflow that outputs data to an excel file, then inputs the data that was processed in the file.  I noticed that the input data does not reflect the changes that was output to it.

Is there a tool or script for the Run Command that I can use that will:

1.  Upon output, Open the Excel File.

2.  Save the Excel File

3.  Close the Excel File

4.  Continue running the workflow.

I found some material out here, but it wasnt really clear.

Any help you can provide would be most appreciated.

[Bennel_Wilson]

Hi @JoeChia ,

Would you mind posting your workflow here.

[smoskowitz]

Hi @JoeChia --

I think if you provide us the workflow with some sample data -- it would be most useful.

Thanks,

Seth

[JoeChia]

Thanks.  The data is sensitive, the workflow contains inputs from over 20 different sources, so sending the actual workflow with all the inputs would be a mess.  Will some screen shots work?  I have attempted to show the issue at a number of levels.

Most of the workflow.  I could not get it all in the shot.  The middle green tool container is the area I need assistance with.

This shows the green container with my output and then input by month.

A closer shot with only a few months.

A shot of the output properties

A shot of the input properties from the same file.

I snap shot of the Excel file and tabs.  Flex is the one that is output to.  Jan - Dec are input in the same workflow. I need a tool or macro or something that will open the Excel file, allow the data to flow through, save and close before going back to finishing running the workflow.

[taxguy33]

@JoeChia Did you ever find a solution to your issue? I'm encountering a very similar issue and can't seem to find a definite answer anywhere on the community.

[JoeChia]

Thanks.  I did not.  There is some Python code I can apply, but after writing it, it was the same amount of work of opening, and refreshing.  I deemed this as a no longer an issue.

[JarekSkudrzyk]

@JoeChia hi, I guess the file itself performs some operations and you need their results back in alteryx workflow.
Have you tried making those operations in alteryx and then writing output to the file?
Another possible solution I can think of is building a chained app in Alteryx - you can set up one app up to the point of writing the output to the file. Then on success of this first app you can schedule to run the second app and read the data processed in the file as input.

You can find lessons on building chained apps on Alteryx interactive lessons site:
https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%2...

[5abarish]

Hi @taxguy33 and @JoeChia,

You could use the Run command tool to execute a VB Script file to open, save and close the Excel file that has the changes and then use a Dynamic Input Tool to take the updated Excel file and continue with the rest of your workflow. The VB script that I had used for a similar requirement is as below:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("'+[Filepath]+'")
objExcel.Application.Visible = True
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
WScript.Echo "Finished."
WScript.Quit

Hope this helps.

Thanks,

Saba