Building an Input, to upload dynamic excel sheets, and creating an output for the files

Question

Hello,

Reposting a previous question I had, this time I have included my workflows and the analytical app preview.

I have 11 workflows created, and I am trying to setup an Input to allow me to add different excel files without having to "setup a connection" to the input tool every time I run one of the selected workflows. Just some information on the workflows, they are setup to update the excel file I choose depending on the workflow and that excel will then have the updates added so I can import the new excel file into a different program.

Also, I want to do something similar to the output, I was thinking if there was a way to just create a new sheet within the input and then I could replace the original sheet with the updates.

Basically, the end goal is to be able to export the excel file from the program we use, save the spreadsheet to either a folder or desktop, open the analytical app select the file and then run the workflow of choice. This is something my team uses on a daily basis.

TPD Macro 2.yxmd

TPD Macro 2.yxwz

shancmiralles · Accepted Answer

you have great diagram of how the WFs work.. have you tried the directory tool? I have a similar WF ( i did not use an analytic APP - but maybe this might help abit regarding pull multiple files and sheets) if you can have all excel files in one folder then you may use a directory tool to pull the excel files to your WF . you may connect a batch macro to copy how an input tool works ( ) so you can extract all the sheets (1st macro) .. result should be 2 fields ( Sheet Names and FileName).... [you may wanna use a filter tool if incase you're not using all of the sheets in all your files ] now to pull the data from each sheet, have a formula tool ready: 1st formula ( output column : FileName) Replace([FileName], "", "") 2nd formula ( output column : FileName) [FileName]+"'"+[Sheet Names]+"$'" after the formulas above, you have to create a second macro , also a batch macro for input tool (query is not list this time but to single sheet and choose yes under option 5. output file name as a field - this will help you differentiate and filter data according to filename/sheet name ) ..