Excel Macro to Import Data Into Word Mail Merge Document

Hi,

I am new to programming and trying to come up with a script to complete a process at work. Basically this is a 2 day process. On the first day, we are importing a spreadsheet from our share point site and formatting the sheet to be emailed to an external service to have accounts activated. The workbook is then saved to a network location to be used for day 2. I have already created a code to complete these step:

Please Login or Register  to view this content.

On day 2, I need to come up with a script to pull the data from the excel document saved on day 1 into a word document that already has the merge fields. The easiest way I was thinking to do this, was to create a macro in Excel to call Word and enter the merge data.

I was also thinking of creating a script in Word to pull the prior days excel file from the network share. The problem with this is that we are not running the scripts on the weekends and holidays. So we would have to pull from the latest date in the folder. The file names in the folder are labeled as "HUP[MMDDYY]" where MMDDYY is the day the file was saved. If anyone has any insight on how to create this script, some help would be greatly appreciated!

So I have come up with this code to merge the data from my Excel source into my Word document that already has merge fields built-in.

Please Login or Register  to view this content.

The problem is, it works if I place the macro directly in the files macro database, but not when I put it in the Personal.XLSB file. When I put it in the personal.xlsb file, the word document launches and then wants me to select the table that the data is in, but I get no choices. It appears to be trying to pull data from the personal.xlsb file rather than the actual data spreadsheet.

How would I write that line if the file name changes every day?