How to change path to mailmerge excel data source?

[Reiner]

Hi all,

I have a word mailmerge document that is linked to an excel data
source. Everytime the excel data is updated, a VBA macro starts the
mailmerge process to create the output document. My problem now is
that different users have to use this from different locations so the
even though the relative path between the excel sheet and the word
document is the same, the absolute path differs because of different
localizations and drive names. I am now looking for a way how to make
this location-independent. I tried the OpenDataSource method from the
macro but then I get asked to select which sheet to use. Are there
other ways that work with DDE? I can not create ODBC datasources on
each system. It should work with at least Word XP / Word 2002 if
possible also Word 2000.

Best regards,
Reiner Buehl.

 

[Cindy M -WordMVP-]

Hi Reiner,

In order to get Word 2002 (and later) to use DDE you need to set the
SubType argument in the OpenDataSource method. For some inexplicable
reason, this argument is not documented in either the Word 2002 or Word
2003 help (!), but if you have Intellisense turned on you should see it
in the list of arguments. This should work:

SubType:=wdMergeSubTypeWord2000

(It looks like you're German-speaking, maybe even in Switzerland? In
that case, if you can get hold of a copy of "Microsoft Word. Das
Profibuch" from MS Press you'll find a full discussion and code samples
for automating mail merge.)

I have a word mailmerge document that is linked to an excel data
source. Everytime the excel data is updated, a VBA macro starts the
mailmerge process to create the output document. My problem now is
that different users have to use this from different locations so the
even though the relative path between the excel sheet and the word
document is the same, the absolute path differs because of different
localizations and drive names. I am now looking for a way how to make
this location-independent. I tried the OpenDataSource method from the
macro but then I get asked to select which sheet to use. Are there
other ways that work with DDE? I can not create ODBC datasources on
each system. It should work with at least Word XP / Word 2002 if
possible also Word 2000.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Reiner]

Hi Cindy,

thanks for your reply. I tried using the SubType argument but when I
try to open the data source with the following code:

ActiveDocument.MailMerge.OpenDataSource
Name:="C:\Temp\conditions.xls", _

SubType:=wdMergeSubTypeWord2000

I get the following error: 0x80020005: "Type mismatch" in
METHOD/PROPERTYGET "OpenDataSource" argument "SubType".
Do you have an idea what could have gone wrong?

Best regards,
Reiner Buehl.

P.S.: Thanks for the litrature recommendation. I am german speaking
and will try to get a copy of the book asap.

 

[Cindy M -WordMVP-]

Hi Reiner,

I just did a quick test and it works fine, here. Note,
however, that it may not be enough to have only the Name
and Subtype arguments. You should record a macro to see
what might be missing. Here's what I got (modified to use
the wdMergeSubTypeWord2000 enum), note the "Connection"
argument:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\xxx\My
Documents\SalesData.xls", _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire
Spreadsheet", SQLStatement _
:="", SQLStatement1:="",
SubType:=wdMergeSubTypeWord2000

Note that I am assuming you're doing this in Word and not
another programming environment. And if the latter, that
you do have a reference set to the Word object library. I
do find the error message "Type mismatch" rather odd...

tried using the SubType argument but when I
try to open the data source with the following code:

ActiveDocument.MailMerge.OpenDataSource
Name:="C:\Temp\conditions.xls", _

SubType:=wdMergeSubTypeWord2000

I get the following error: 0x80020005: "Type mismatch" in
METHOD/PROPERTYGET "OpenDataSource" argument "SubType".
Do you have an idea what could have gone wrong?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Reiner]

Hi Cindy,

last night I did a lot of testing and came up with a version that
works: In another article in this group somebody posted that in
addition to Name and Connection at least SQLStatement is needed. After
changing my code to:

ActiveDocument.MailMerge.OpenDataSource
Name:="C:\Temp\Conditions.xls", _
Connection:="Sheet1$", _
SQLStatement:="SELECT * FROM `Sheet1$`"

everything works! From your other posts I do think that I now use
another method to connect to Excel, not DDE anymore since I do not use
the SubType parameter. Is this correct and do you have an idea if this
could still work with Word 2000?

Thanks for your help and patience!

Best regards,
Reiner Buehl.

 

[Cindy M -WordMVP-]

Hi Reiner,

last night I did a lot of testing and came up with a version that
works: In another article in this group somebody posted that in
addition to Name and Connection at least SQLStatement is needed. After
changing my code to:

ActiveDocument.MailMerge.OpenDataSource
Name:="C:\Temp\Conditions.xls", _
Connection:="Sheet1$", _
SQLStatement:="SELECT * FROM `Sheet1$`"

everything works! From your other posts I do think that I now use
another method to connect to Excel, not DDE anymore since I do not use
the SubType parameter. Is this correct and do you have an idea if this
could still work with Word 2000?

ah, yes, in that case you've got an OLE DB connection. Looking at the
code you post, I'm *guessing* it could work in Office 2000 to generate a
DDE connection, but you'd need to test to be sure.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail