Word 2007 Error opening document based on mailmerge template

L.s,

I have made a mailmerge template with a Excel datasource. When I open the
template no problem just the "select ...." messages which is normal.

When I make a new document based on the template it still works fine and the
datasouce is linked. When I save this document en reopen it the problems
start:
I get the "select...." messages 2 times, when I in both cases I choose
"yes" I get the "select datasource dialog window" and must select my
datasource again.

A tested some hours and found out:
- The fist "select.." means Word is connecting to the datasouce for the
template witch is attached to the document (the template I used to make the
document).
- The second "select ..." means Word is connecting tot the datasource for
the document I'm opening. (I find this out by changing the datasouce for the
document save and reopen it, the "select..." shows with datasource is
opening).

I found 2 work arounds:
- When I choose no the first time en yes the second it works fine, but I
don't like this solution because I have many users. It's not "clean good
automation".
- When I attached the datasource not the “standard way� (I think this uses
DDE), but choose ODBC, it works better. I still get 2 times "select..." but
when I choose yes 2 times it will work. Maybe I'm a perfectionist, but I
don't really like this solution either because the 2 times "select" and the
use of ODBC which is more complex for users when connecting to a different
datasource.

By the way I know I can get ride of the "select ...." messages, but because
we have I TS environment with 1 live server, I don't trust myself making
chances in the registry.

Does anyone know a nice and clean solution so Word only opens the datasouce
which is connected to the document and not the datacourse from the template
(without changing the template attached to the document).

Many thanks

Peter

One thing that you might consider is not having a data source attached to
the template and instead have an autonew macro in the template that attaches
the data source to the document that is being created from the template.

This example attaches the Excel worksheet named "Names.xls." The Connection
argument retrieves data from the range named "Sales."

ActiveDocument.OpenDataSource Name:="C:\Documents\Names.xls", _
ReadOnly:=True, _
Connection:="Sales"

In addition to that, if you create the following macros in the template, you
will also be able to avoid the SQL message when the mail merge main document
is subsequently opened.

Sub autoopen()
Dim mdt As String, datasource As String
With ActiveDocument
mdt = .Variables("mdt").Value
datasource = .Variables("datasource").Value
With .MailMerge
.MainDocumentType = mdt
.OpenDataSource "C:\Documents and Settings\Doug Robbins\My
Documents\My Data Sources\company.doc"
End With
End With
End Sub

Sub autoclose()
With ActiveDocument
.Variables("mdt").Value = .MailMerge.MainDocumentType
.Variables("datasource").Value = .MailMerge.datasource.Name
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PeterHS" <PeterHS DeleteThis @discussions.microsoft.com> wrote in message
news:49A53D70-ECAD-4B44-8B9F-241BCB4B2958@microsoft.com...
> L.s,
>
> I have made a mailmerge template with a Excel datasource. When I open the
> template no problem just the "select ...." messages which is normal.
>
> When I make a new document based on the template it still works fine and
> the
> datasouce is linked. When I save this document en reopen it the problems
> start:
> I get the "select...." messages 2 times, when I in both cases I choose
> "yes" I get the "select datasource dialog window" and must select my
> datasource again.
>
> A tested some hours and found out:
> - The fist "select.." means Word is connecting to the datasouce for the
> template witch is attached to the document (the template I used to make
> the
> document).
> - The second "select ..." means Word is connecting tot the datasource for
> the document I'm opening. (I find this out by changing the datasouce for
> the
> document save and reopen it, the "select..." shows with datasource is
> opening).
>
> I found 2 work arounds:
> - When I choose no the first time en yes the second it works fine, but I
> don't like this solution because I have many users. It's not "clean good
> automation".
> - When I attached the datasource not the "standard way" (I think this uses
> DDE), but choose ODBC, it works better. I still get 2 times "select..."
> but
> when I choose yes 2 times it will work. Maybe I'm a perfectionist, but I
> don't really like this solution either because the 2 times "select" and
> the
> use of ODBC which is more complex for users when connecting to a different
> datasource.
>
> By the way I know I can get ride of the "select ...." messages, but
> because
> we have I TS environment with 1 live server, I don't trust myself making
> chances in the registry.
>
> Does anyone know a nice and clean solution so Word only opens the
> datasouce
> which is connected to the document and not the datacourse from the
> template
> (without changing the template attached to the document).
>
> Many thanks
>
> Peter
>