Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-02-2012, 03:40 PM
navysalad navysalad is offline Windows Vista Office 2007
Novice
 
Join Date: Jan 2012
Posts: 4
navysalad is on a distinguished road
Default How can I revise Excel data source in merge?

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!!!
Reply With Quote
  #2  
Old 01-02-2012, 04:48 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,309
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-02-2012, 06:11 PM
navysalad navysalad is offline Windows Vista Office 2007
Novice
 
Join Date: Jan 2012
Posts: 4
navysalad is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 01-02-2012, 06:55 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,309
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 01-07-2012, 06:29 PM
navysalad navysalad is offline Windows Vista Office 2007
Novice
 
Join Date: Jan 2012
Posts: 4
navysalad is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 01-07-2012, 06:39 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,309
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi navysalad,
Quote:
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.
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.
Quote:
Is there any way to just code the local file name (with no path) into the merge process?
No, that's not possible.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 01-07-2012, 06:50 PM
navysalad navysalad is offline Windows Vista Office 2007
Novice
 
Join Date: Jan 2012
Posts: 4
navysalad is on a distinguished road
Default

Thanks again macropod (especially for the really quick answer)!
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically Selecting a Data Source Nathan_Newlin Mail Merge 1 12-06-2011 03:42 PM
Edit Data Source- Linking template charts to new data lbf PowerPoint 0 10-28-2011 12:19 PM
Wine List, Data Merge - Excel to Publisher - Or other suggestions? daym Publisher 2 05-01-2011 03:45 AM
pivot table source data hannu Excel 0 07-03-2010 04:54 AM
Mail Merge data source on network drive Brandi Mail Merge 0 06-13-2010 11:26 AM


All times are GMT -7. The time now is 06:52 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft