Combine File Into Workflow Based on Sheet Name

[macst245]

Hello, I have a macro that works on a single file at a time, then goes through the rest of the input files and the result is one ouput file with multiple sheet names which are renamed after the input files.  I will be running that macro the first month, but in the second I will need information from that first month's output file and I want to put that information back into the macro to use some calculations in the macro on the first month output file, while combining it with the second month's input file.  

There will be around 20 original input files, but I have provided a sample workflow where there are only two input files, with the macro these are combined into one excel file with 2 new sheets with each sheet named after the original input file.  What I am looking for is an input tool I can include in the macro which will find the sheet name from the output file and correctly join that with its corresponding input file so I can perform calculations on that new data.  

For this sample workflow, the output of the macro is one excel worksheet with sheets titled Test A and Test B; I would then want some helping with putting in a tool that can grab the Test A Sheet from the output file, combine that with the Test A data from the input file.

Hope this explanation wasn't too confusing, but if it is please let me know and I can try to clarify.  

[mceleavey · Accepted solution]

Hi @macst245 ,

You didn't include your macro in the output so I built it for you:

I start with a Directory tool which reads in the list of the csv files in a given location, specifically the file names which will be used to create the tabs in an Excel output. I then use a tool I built to read in multiple files at once, and this brings all the data in while retaining the column headers. I then create an output path, which denotes the filename as the tab name in the destination file (removing the file extension).

I then use a simple batch macro to loop through each one to create the new tab:

I hope this helps,

M.

[macst245]

Thank you very much!

[mceleavey]