OpenDataSource Was Working, Now Isn't

[BillR]

I have mailmerge files that are passed among various users,
where the document path may change. To ensure a proper link the the
merge
data file, I had code that saves the document path as a document
property.
When the file is opened, the current path is checked against that
property.
If they do not match (if the file is in a different directory), I
reestablish
the link to the datasource (an Excel file and a named worksheet in the
file,
in the same directory as that document.)

Until I changed computers a week ago, the following code worked to
reestablish
that connection:

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strDataSourceName, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="WORKSHEET Variables$", SQLStatement:="",
SQLStatement1:="", _
SubType:=wdMergeSubTypeWord2000
End With

I included the Word2000 subtype to avoid prompts to select the
datasource.

My old PC was on Windows 2000, and I was using Office XP. My new
computer uses Windows XP Pro, but we're still on Office XP.

The old code no longer works. In fact, now, even if I try to do a
basic mailmerge wizard connection to the Excel source, I am presented
with a bewildering array of options to choose from, offering ODBC,
OLE, DDE, etc.

I think my old computer had ODBC installed, but am not sure. I don't
want to have to rely on that installation.

Can anyone suggest why this code is no longer working? And what I can
do to fix it to link to the Excel file and the worksheet with my data?

Thanks in advance!

Bill

 

[Cindy M -WordMVP-]

Hi BillR,

We're missing a key piece of information: strDataSourceName

I have mailmerge files that are passed among various users,
where the document path may change. To ensure a proper link the the
merge
data file, I had code that saves the document path as a document
property.
When the file is opened, the current path is checked against that
property.
If they do not match (if the file is in a different directory), I
reestablish
the link to the datasource (an Excel file and a named worksheet in the
file,
in the same directory as that document.)

Until I changed computers a week ago, the following code worked to
reestablish
that connection:

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strDataSourceName, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="WORKSHEET Variables$", SQLStatement:="",
SQLStatement1:="", _
SubType:=wdMergeSubTypeWord2000
End With

I included the Word2000 subtype to avoid prompts to select the
datasource.

My old PC was on Windows 2000, and I was using Office XP. My new
computer uses Windows XP Pro, but we're still on Office XP.

The old code no longer works. In fact, now, even if I try to do a
basic mailmerge wizard connection to the Excel source, I am presented
with a bewildering array of options to choose from, offering ODBC,
OLE, DDE, etc.

I think my old computer had ODBC installed, but am not sure. I don't
want to have to rely on that installation.

Can anyone suggest why this code is no longer working? And what I can
do to fix it to link to the Excel file and the worksheet with my data?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[BillR]

Hi Cindy M:

strDataSourceName is a string variable that contains the path and
filename to the Excel file. I use the variable so I can set the
connection back to "where it belongs" according to my little system
for where the datasource should be. (Typically the same folder as the
merge file, or sometimes a subdirectory named DataSource.)

I had seen some posts where it was suggested to leave the Name
argument blank, but that didn't seem to apply to my case. Where Name
was left blank, it seemed that similar info had to be placed in the
Connection argument.

Thanks for your help (and for all your own postings at Swissonline.
Very useful.)

Bill

 

[Cindy M -WordMVP-]

Hi BillR,

strDataSourceName is a string variable that contains the path and
filename to the Excel file. I use the variable so I can set the
connection back to "where it belongs" according to my little system
for where the datasource should be. (Typically the same folder as the
merge file, or sometimes a subdirectory named DataSource.)

Yes, that's fine, and I understand that. But I still would like to see
a sample of how that's been put together, otherwise I won't have a
complete picture, and won't even know which kind of connection method
was being used. I'm guessing it was DDE, but...

One thing you can certainly try is connecting up to the data source and
recording a macro, then compare that with your code to see where they
may differ. You may even find out that there's something in the UI
keeping you from making this connection, which is why the macro can't
work. Try all those various options in that list you mention seeing,
all of a sudden.

I had seen some posts where it was suggested to leave the Name
argument blank, but that didn't seem to apply to my case. Where Name
was left blank, it seemed that similar info had to be placed in the
Connection argument.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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