Re: Mail Merge relative reference to data source

You relaly have to do at least two thing:
a. make sure that the Mail Merge Main Document is never saved with any data source information - otherwise, when you re-open it, the first thing that Word does is to try to locate the existing data source. You can't write a macro that intercepts WOrd before this point and you can't really guarantee to avoid the dialog boxes that will appear if the data source is not found.
b. use an AutoOpen macro to connect to the new data source, as you mention.

To disconnect a datasource from its datasource before saving it, you can use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

You will lose the source name, connection infromation and any sort/filter selections, but not any of the fields in the document.

IN Word 2003 you can also do ActiveDocument.MailMerge.DataSource.Close, but I know little about the consequences of that approach.

Here's a sample AutoOpen for you:

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String


' this is a simple example for a data source which is a Word document
' set this to be the file name of your data source
strDataSource = "kt.doc"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' strQuery = ""

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
' for a Word document as data source, we just need the path name
With .MailMerge
.OpenDataSource _
Name:=strDataSource
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Peter Jamieson

"Chris Slowe" <20footduck@xxxxxxxxx> wrote in message news:1179079633.578633.13540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi

I've been trying to find out over the last few days how exactly create/
fake a relative reference (as opposed to an absolute eg C:\blah\blah
\blah) to an Excel spread*** I'm using as a data source. It sounds
possible from some things I've read online, and I think I understand
the theory but unfortunately I'm not proficient in VBA to do it, and
nobody has posted the code anywhere (and as this is the only vb I'll
be touching it's not worth learning the whole caboodle).

It sounds like you have to create an AutoOpen macro (that kicks in on
opening the doc) that changes the path of the MailMerge datasource to
a combination of the file path of the word document and the file name
chosen for the spread***. I cobbled together some code that looked
ok, and it seemed to work... but only once. on moving the duplicate
folder it reverted to referring to the original again.

I'm sure the code must be simple but I'm not having any luck

would appreciate some help. typically the project somewhat relies on
me finding a fix for this and will be seriously hampered without one!

thanks in advance

Chris


.