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!!!

Hi navysalad,

You can also kill off the existing merge by answering 'No' to the "Opening this document will run the following SQL command:" prompt. You can then select a new set of recipients and, hence, data source. If you open your 'master' mailmerge main document and choose 'No', then save, you'll only need to do the second part for the copies.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for the fast reply macropod! I'll give that a try. Odd that there doesn't seem to be a way to just directly edit the name of the data source file to use.

Hi navysalad,

It could be done with a macro, but then you'd get a macro warning every time you open the mailmerge main document.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod (or anyone else)

Your suggestion allowed me to revise the data source (thank you very much!), but even though I plugged in a relative name (ie just the file name, no path information), Word still inserts the full path + file name, which means that every time I copy the master Word and Excel files to a new directory, when I start a merge, Word wants to pull the data from the Master excel file, rather than the Excel file of the same name in the local directory. So the only way I've found to make this work is to change the name of the master Excel file so that Word can't find it, then when I get the "file not found" error message, it lets me enter the name of the local Excel file.

Is there any way to just code the local file name (with no path) into the merge process?

Hi navysalad,
As previously indicated, if you open your 'master' mailmerge main document and answer 'No' to the "Opening this document will run the following SQL command:" prompt, then save, you'll no longer have the mailmerge trying to attach itself to the wrong (or any) data source. From then on, you'll only need to attach a data source for the copies.
No, that's not possible.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks again macropod (especially for the really quick answer)!