Hi
I'm trying to design something to allow people to run a series of small workflows which will each generate single values, each of which will then get added to particular cells in a template excel file.
At the moment I have my data-generating workflow but I'm struggling on controlling the output tool. I want the user to be able to select the location of this month's template excel file but I don't want them to have to select the tab in that template or the cells that this particular workflow is going to overwrite: ideally I'd like those latter two things (which will be the same every time) to be held within the workflow. This is because I don't trust them to select the right cells every time and the template excel sheet will fall over if they don't.
What I've tried so far:
- File browse into an input tool which imports the template,
- Input tool is configured to add the filepath as a new column
- Used the Left command in the formula tool to strip out everything in the filepath after the file extension (to get rid of the |||Sheetname$Cell:Cell) that the user has inputted.
- Used a Text input tool to add in the correct sheet name and cell reference.
- Used another formula tool to concetenate the filepath and the static sheet name and cell reference - this produces a cell with the 'corrected filepath and sheet name/cell reference'.
- Keep that as a column into the output tool, which is configured to take the file/table name from a field - this is pointed at the filepath column.
This is where is falls over: instead of taking the filepath column and using that to find the output location, it's dumping the data in a new tab with the filepath as the tab name.
Is there a better way of doing this?
