This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I need to combine multiple workbooks with the same sheets (schema) into one output file (.xlsx). I then want to be able to add another workbook to the directory and run the workflow without overwriting any data which has been added to the previously output data.
- Sheet 1 and Sheet 2 are in a directory and need to be combined (2 sheets: Name & Cost)
- The Output should look like this
- The output may will to edited as shown below (Approved Field)
- Sheet 3 then becomes available
- I want to add Sheet 3 to the previously output data, without overwriting the edited information (Approved Field). Appended Data shown in red
Any help greatly appreciated.
I have been looking at the following articles, but haven't been able to create a workflow/macro which completes the process as expected:
not sure if I got what you want to achieve right, but have you tried to set up the Output Tool -> 3. Output Options to "Append To Existing Sheet"?
Attached a sample workflow. It will give you an error the second time you run it, as the first 2 Output tools are set to "Create New Sheet", but at least you won't lose the output you created the first time you run it.
For the output, load in the old file and just union the new stuff to it (and rewrite data with drop sheet). Or like the post above, append the data (this one makes me more (unjustifiably) uncomfortable because xlsx layouts can be weird but... it should work just as well.
Personally I would also have a batch file that moves all successfully processed xlsx files to a Processed folder at the end.
Thanks a lot for your response, I've been able to get the input side of things working. I'm now having a bit of trouble with the Output and was wondering if you could explain both options a bit more if possible please. My issue is that I want to append to my newly created sheet, but I'm getting duplicates whether or not the 'Approved' field has been edited.
For the output, load in the old file and just union the new stuff to it (and rewrite data with drop sheet)
At what point are we loading in the 'old file'? with a union will I not still have the issue of duplication i.e Approved empty and fill since last run.
Batch file that moves all successfully processed xlsx files to a Processed folder at the end
I like this idea but am not completely sure what you mean, is it a batch macro? Could you give me an idea of how it might be constructed.