How can I revise Excel data source in merge?

[navysalad]

I have a small business where I use Excel 2007 to do number crunching for one individual at a time. I recently figured out how to use a Word 2007 merge document to take the results from my Excel calculations and plug the numbers into a pre-designed report.

What I'd like to be able to do is have a "master copy" of my Excel spreadsheet and word document that I copy to unique client-specific directories, then enter the client-specific information to produce the numeric results (in Excel) and the report (in Word).

The problem I have is that when I copy the Word and Excel files to a new directory, the Word document still wants to reference the "master" Excel file in the "master" directory and won't let me change it (or at least I haven't been able to do this yet). The only work-around I've been able to come up with is to change the name of the master file so that Word can't find it. When I do this, Word produces a error message about not being able to find the Excel file, and then displays a "Data link properties" box where I can enter the name of the local Excel file (rather than the "master" Excel file). (I've tried everything else I could think of and could find no other way to be able to enter the name of the Excel file). Ideally, instead of entering the full path name of the Excel file (e.g., D:\Bizname\clientname\project\Excelname.xls), I'd like to enter a name for just the local Excel file so that no matter what directory I copy the pair of Word and Excel files to, they can "find" each other.

Can anyone tell me how to accomplish this, or at least to directly edit the location of the Excel file without having to resort to the trickery described above? Any help greatly appreciated!!!