how to use a relative path to a data source for word mail merging (word 2007, word 2010)

suppose you set up mail merging in a word document and specify a data source that is in the same folder (e.g. a csv or an excel file), and then you move the folder.  next time you open the word document, word will look in the original location for the data source and give an error "file.docx is a mail merge document. Word cannot find its datasource."

here is a macro solution, but this requires disabling some security features so that it can run every time you open the document:

Sub Document_Open ()
Dim PATH, SOURCE As String
PATH = ThisDocument.Path 'determine current directory
SOURCE = PATH & "\Adresses.xls" ActiveDocument.MailMerge.OpenDataSource Name: = Source
End Sub

with way too much trouble you can make the source relative by editing the word document without using microsoft word.

0. back up the word file you are editing because you might destroy it in the following steps.

1. find an OpenXML editor that can edit zipped xml files.  for non-commercial use you can use this: http://free.editix.com/.  the following directions contain elements that are specific to using editix.

2. open editix.

3. click file-> open

4. under "files of type" choose "microsoft office documents"

5. open a word file with mail merging already set up with an absolute path to the datasource

warning: there may be two places where you have to change the path when using text files.

6. if you can find it, navigate to "file.docx" > "word" > "_rels" > "settings.xml.rels" (open this .xml.rels file).  if you can't find it then skip to step 9.

7. right click in white space in the panel on the right and click "pretty format" to make the xml easier to read

8. look for something like the below.

Target="file:///C:\users\you\documents\datasource.csv"

above is an absolute path.  change it to something like the below, which looks for a file called "datasource.csv" that must be in the same folder as the word document.

Target="datasource.csv"

warning: there may be other references to the data source in settings.xml.rels that need to be changed to relative paths.  thanks to mark for pointing this out.

if the data source is in a folder called "data", and the "data" folder is in the same place as the word document, type

Target="data/datasource.csv"

if the word document is in a folder called "word documents" and the "word documents" folder is in the same place as the data source, type

Target="../datasource.csv"

because ../ means go to the folder above.  "../../datasource.csv" means to look two folders up.  for more help google search "absolute and relative paths".

in this step only, if there are any spaces in the path to your file, replace the spaces with "%20" (not including the quotes)

9. navigate to "file.docx" > "word" > "settings.xml".  you should definitely be able to find this one no matter what your type of data source.

10. format again so you can read the xml easily

11. look for something like the below

 <w:query w:val="SELECT * FROM C:\users\you\documents\datasource.csv"/>

do not replace spaces with %20 here.

12. change "C:\users\you\my documents\datasource.csv" to a relative path.  it should be the same as the path you used in step 8 if you did step 8.  e.g.

 <w:query w:val="SELECT * FROM datasource.csv"/>

i would love a comment if this article helped you.