[Louise]

Hi

I have set up a mailmerge with the data in excel and the labels to merge
into set up in word. I have quite alot of formula in the excel data section
so i can not put the data into a word file.

The mailmerge needs to work on other computers at other sites - when i do
this all the links are lost - Is there a way round this?
--
Louise

[Peter Jamieson]

The simplest approach is probably to use Word VBA to set up the data source
in an AutoOpen macro.

To do this, you really have to do at least two things:
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 information 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


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 Sheet1 in an Excel
workbook
' set this to be the file name of your data source
strDataSource = "kt.xls"


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


' set this to be the query for your data source (I think this will do)
strQuery = "SELECT * FROM [Sheet1$]"


With ActiveDocument
strDataSource = .Path & "\" & strDataSource
' for an Excel document as data source, we need the path name and probably
the Query string
With .MailMerge
.OpenDataSource _
Name:=strDataSource, _
SQLStatement:=strQuery
' 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

If you have never worked with VBA, grham Mayor has a useful article at

http://www.gmayor.com/installing_macro.htm


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Louise" wrote in message
...
Hi

I have set up a mailmerge with the data in excel and the labels to merge
into set up in word. I have quite alot of formula in the excel data
section
so i can not put the data into a word file.

The mailmerge needs to work on other computers at other sites - when i do
this all the links are lost - Is there a way round this?
--
Louise