2011-08-07

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.

21 comments:

  1. Just tried the above, and it worked first time - excellent! Users may find, as I did, that they have more than one entry to edit under the "file.docx" > "word" > "_rels" > "settings.xml.rels" portion (your steps 6-8 above), or that the edit in step 11 regarding the 'SELECT * FROM ...' may not be in the 'SELECT' clause but may be nearby, possibly in more than one location, but it's fairly obvious to just spot the absolute file path/name and edit it as described.
    One last point, I followed your instructions then moved the Word document and all worked fine. However, if I then re-open the Word document at its new location using editix, I see the relative paths have become new absolute paths...?! I haven't had time to look into the impact of this for portability etc., but maybe others will comment.
    Great job, thanks. Mark

    ReplyDelete
  2. thanks mark. i put a note in step 8 for this.

    i hadn't tested it but i was afraid word would make the paths absolute again. this method works well for me when i let users download a zip file from my website with a csv and a mail merge document and it is unknown what the path will be when the user extracts the zip file.

    ReplyDelete
  3. Thank you man!
    It worked like a charm :)
    Regards from Barcelona

    ReplyDelete
  4. Same hier, worked like a charm...
    Thanks,

    ReplyDelete
  5. I love you, saved me!!

    ReplyDelete
  6. Grate thanks,
    I am getting next level.

    ReplyDelete
  7. usefull, thank you so much .
    Regards from Viet Nam

    ReplyDelete
  8. So basically ms is shit and all this work is useless, specially if what the first comment said is true.

    ReplyDelete
  9. Thanks for the tips Alexander! You can also open the file with WinRar if you happen to have it installed.

    ReplyDelete
  10. The macro works fine. However, you have to either save the file BEFORE you set up the merge data file OR use the Mail Merge Reset command (you have to add it to the ribbon) to clear out the non-relative addressing. Tested on Word 2010 and 2013.

    ReplyDelete
  11. Opening the file and editing the XML files worked great. I just changed the file extension to .zip and opened the file that way--edit them with any XML editor (or even notepad if you're _really_ desperate) and re-add them, change the filename back to .docx, good to go!

    Thanks for the tips!

    ReplyDelete
  12. I know it's been up there a while but this is fantastic and so simple! What is even better is my path I am using is a url. I just including the full https: path to my datasource (a database in a SharePoint document library) and it works!

    ReplyDelete
  13. Thanks a lot, worked great for me. Just changed 3 times in settings.xml.rels:

    .....Target="file:///\C:.....\....\card.xlsx"....
    to
    .....Target="card.xlsx"....

    The file card.xlsx is in the same folder. No problems any more by using the files on Mac, Windows, and any places in the file system... Works fine anywhere, as long as the files are in the same folder!

    ReplyDelete
  14. Great post, thanks for including the non-VBA version, which doesn't work for documents stored in desktop-synced SharePoint folders (in that case, ThisDocument.Path points to the SharePoint document URL, and, after much searching, there doesn't seem to be a way to get the local path using VBA).

    I used 7zip to open the archive directly, which will prompt you to update the archive after you edit a file within.

    ReplyDelete
  15. I found an easier way! Hope this helps others.
    https://youtu.be/4mnvGoUz-Z0

    ReplyDelete
    Replies
    1. Hi!
      I used both this page and jodi solutions and both worked great.
      However, I must thank Jodi because her link is a time-saver!
      And it works on network folders too.

      Delete
  16. I had to use "ActiveDocument" instead of "ThisDocument", to get the correct path. But them it worked perfectly!
    ThisDocument pointed to the path of NORMAL.DOT in my environment.

    ReplyDelete
  17. nice share
    thanks for information

    ReplyDelete
  18. Many thanks from Italy! It worked perfectly!

    ReplyDelete
  19. hi,we provide online training & video tutorial for soapui
    for free videos refer
    http://soapui-tutorial.com/soapui-tutorial/introduction-to-webservices/

    ReplyDelete
  20. Awesome - Still helping people after nearly 5 years! Good job, and thank you!

    ReplyDelete