I'm working on a project for a client that initially loops through a folder where several Excel spreadsheets are found from day to day as newly exported from the client's clients.
The "For... Loop" container loops through all subfolders within a specific location, determines if files exist, and proceeds to move each file into a similar folder/subfolder structure in a different directory. No issues there... works fine.
The issue I'm trying figure out is how to take an Excel template file that will always reside in a specific location and always have the same name, and just after each client's Excel file is found and moved into the similar directory/subfolder structure as described in the previous paragraph, I also want to move this empty Excel template file (for example purposes let's just say the file is always located/named as follows: "c:\TemplateFile\ExcelTemplateToUse.xls") and name it similarly as the client's Excel file that was just moved, with the addition of the text "_Final" added to the end of the file. More details below...
So, taking one of potentially many client files in consideration, let's say the "For... Loop" process finds that a file is located here: "c:\ClientFiles\ABCCompany\ABCInfo.xls". There's code within the "For... Loop" process that will find this file and move it to "c:\ReadyForProcessing\ABCCompany\ABCInfo.xls". What I need to happen at this point once the "ABCInfo.xls" file has been moved is that the empty "c:\TemplateFile\ExcelTemplateToUse.xls" Excel template file (only has column headers in the file) also needs to be *copied* to the ""c:\ReadyForProcessing\ABCCompany\" location but it needs to be renamed to "ABCInfo_Final.xls" when it's copied instead of being left named as "ExcelTemplateToUse.xls".
Moving forward, let's say the continuing "For... Loop" process finds another file recently placed there from a client... "c:\ClientFiles\XYZIndustries\ZYZPayrollData.xls". This file will be moved to "c:\ReadyForProcessing\XYZIndustries\ZYZPayrollData.xls". What I need to happen again at this point once the "ZYZPayrollData.xls" file has been moved is that the empty "c:\TemplateFile\ExcelTemplateToUse.xls" Excel template file also needs to be *copied* to the ""c:\ReadyForProcessing\XYZIndustries\" location but it needs to be renamed to "ZYZPayrollData_Final.xls" when it's copied instead of being left named as "ExcelTemplateToUse.xls".
And so on for however many files the looping process finds...
I've read all sorts of posts on the pros/cons of using a File System Task vs a Script Task to accomplish when I'm trying to do with copying the "c:\TemplateFile\ExcelTemplateToUse.xls" over and over to new locations and renaming the copied "ExcelTemplateToUse.xls" file to the other Excel file's name that was just moved into the location with the desired naming convention as described above.
More and more of what I read seems to recommend using a Script Task to accomplish this instead of the File System Task.
Any recommendations or code snippets any of you that have done a similar sort of thing have to pass my way?
Many thanks in advance.