I would be more willing to correct it. Unfortunately, I'm dealing with a rough quarter-end. Long story short, if you get the Mail Merge part set up, the PAD part is easy. In your Excel file, make sure you have a column for the filepath\filename to the Word Merge document required. If all your Word Merge documents are in the same folder, then it can just be the filename.
Get the Word Merge working manually. If you can do that, then all PAD has to do is open the Excel, read the right row, get the filepath\filename from the right column, open that file, and use the UI elements to merge the Excel with that template. None of that part is inherently difficult.
However, there is another option that I just thought of. This option would be slow, but I thought of it, so I wanted to give it to you. The result would be the same, but you may be more comfortable with this. Instead of creating merge templates the conventional way, you populate the merge fields with codes that would only exist in one place, such as "FTE_RATE001". In Excel, you make the titles the same thing (or put the code in at row2 if you want to leave the title row for whoever is filling it in (I will call this the %CodeRow%, and for the below example I will assume it is in row 2). Then, if you were to have all of these in Excel, referencing the right template, the PAD would be as follows:
- Get First available Row
- Set %CodeRow% to 2
- Set %TemplateFilePathColumn% to Y (or wherever your Word Template file path is defined)
- Set %TemplateFileNameColumn% to Z (or wherever your Word Template file name is defined)
- Loop from %CodeRow + 1% to %FirstAvailableRow - 1% in Excel increment by 1
- Read Excel (at %TemplateFilePathColumn% & %LoopIndex%) to %TemplateFilePath%
- Read Excel (at %TemplateFileNameColumn & %LoopIndex%) to %TemplateFileName%
- Open CMD to %TemplateFilePath%
- Write to CMD %TemplateFileName% (Enter button checked)
- For each column in row (%LoopIndex%) (Note: set variable to %CurrentCol%)
- Read Excel (at %CurrentCol% & %CodeRow%) to %Code%
- Read Excel (at %CurrentCol% & %LoopIndex%) to %Result%
- Find & Replace (you could use UI or hotkeys to make this work)
- Replace %Code% with %Result% - Replace All
- End #For Each
- Use hotkeys to save file as (whatever you want) and close the word doc
- Close CMD
- End #Loop
The point is, it would run through each row, and replace all on the data inside of each word template. This would take much longer than the other way, but it is an option. Between the two, I would take the time to program the first option as it would be much faster.
Best of luck!
