Mailmerge from Word 2003 using 2000 word template

[Andrew@AAP]

Hi,

Ive been searching for answers for this for the last 2 days, however have
not found an answer, only reasons why the problem occurs.

We use Word Automation in one of our VB6 programs (which runs as an NT
service - completely unattended) Recently we upgraded the version of word
being referenced from 9.0 (word 2000) to word 11.0 (word 2003).

Since then i have noticed our service hangs, whenever a merge is performed
on a mailmerge document built under word 2000.

The reason appears to be due to the transition of how word links its data
source. I am aware of the transition of 2000's DDE to the JET used in 2003.

I have applied the registry update for SQLSecurityCheck. It does not work
for this problem.

The hanging is due to the program prompting for the datasource upon Opening
the word document itself (the WordApp.Documents.Open call), NOT calling the
OpenDataSource method (WordDoc.MailMerge.OpenDataSource).

WordApp.DisplayAlerts = wdAlertsNone
Set WordDoc = WordApp.Documents.Open(filename:=DocFile,
ConfirmConversions:=False, ReadOnly:=True) ', Format:=wdOpenFormatAllWord,
NoEncodingDialog:=True, Visible:=False)

' Make sure its a mailmerge document
If WordDoc.MailMerge.MainDocumentType = wdNotAMergeDocument Then
Err.Raise -1, , "This is not a valid mailmerge document."
End If

' Open the data source
If UCase(Mid(DataFile, Len(DataFile) - 2, 3)) = "XLS" Then
WordDoc.MailMerge.OpenDataSource Name:=DataFile,
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=False,
Connection:="Entire Spreadsheet", SubType:=wdMergeSubTypeWord2000
Else
WordDoc.MailMerge.OpenDataSource Name:=DataFile,
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=False,
SubType:=wdMergeSubTypeWord2000
End If

Any help appreciated. This is a big problem for us at present.
(I am also aware of Microsoft's statements warning against the use of office
automation in unattended applications)

Many Thanks,
Andrew

 

[Peter Jamieson]

Can we take it that you are still seeing the /SQL/ message and not some
other message?

Have you applied the SQLSecurityCheck registry change to the correct user
(i.e. the user account that the service is running under) ?

Peter Jamieson

 

[Andrew@AAP]

Forgot to add; The wordapp.documents.open method is actually throwing up the
"Select Data Source" window.

I cant find a workaround for this, particularly since this is an unattended
application its beginning to look like we need to define what versions of
word we are prepared to support.

 

[Peter Jamieson]

Do you get this with all types of data source or a selection, e.g. the ones
that would have used DDE in Word 2000 (probably Access or Excel)?

If you open one of the "problem" documents manually, do you see the same
dialog box?

ISTR having to open mail merge main documents in Word 2002, updating all the
fields in the document, reconnecting to the data source(s) and re-saving as
Word 2002 documents, and not just because of the changes in the connection
methods. Probably the same for Word 2000-2003.

Peter Jamieson

 

[Andrew@AAP]

Hi Peter,

Thanks for the replies.

Yes, the prompt for selecting the data source occurs for all data source
documents (excel or CSV)

I have applied the SQLSecurityCheck Registry "fix".

Quite frustrating that i now read that Office2007 has changes data
connectivity again.

Thanks,
Andrew

 

[Andrew@AAP]

Hi Peter,

Do you get this with all types of data source or a selection, e.g. the ones
that would have used DDE in Word 2000 (probably Access or Excel)?

Yes, all types, we allow our customers to submit CSV or Excel datasources.

If you open one of the "problem" documents manually, do you see the same
dialog box?

Yes.
The problem is trying to program around this.
In Word 2000 you would still get this box, but not until you call
OpenDataSource.
Now it appears to occur on the Open Word document itself.
Different stage of execution that i dont see an immeadiate way of coding
around.

 

[Peter Jamieson]

As far as I know, the situation is that a document that is already connected
to a data source and cannot find that data source when it opens will always
prompt for the data source (even in Word 2000 AFAICR), but Word 2002/2003 is
more particular about where that data source is actually located - if you
are relying on Word 2000 finding the data source in the same folder as the
mail merge main document, I suspect that will not work in Word 2002/2003.
Even if the user manages to get Word to open some types of data source the
first time around, in some cases (e.g. where the data source has a long
pathname), when you close/re-open the mail merge main document, Word my
"lose" the data source and throw up prompts. The "can't find the data
source" problem has AFAIK always been one of the problems with automating
merges.

Unfortuately I really don't think there is any way of reliably programming
around this unless you know what the data source is supposed to be
independently of the document. i.e., you could try to use Sendkeys to get
through the Select Data Source, but then you lose all information in the
merge main document about the name of the data source, so you can't
reconnect to it. It's difficult even to suggest what you might do in future
without knowing, for example, how much your customers are willing to do to
ensure that things work, how you ensure that the data sources used are
findable on the server, and so on.

FWIW I don't think Word 2007 is that much different from Word 2003 in this
area, but you would have to have the ACE driver/provider on your server as
well as the Jet one that it "replaces", and if you have to cope with DDE
connections you would need Excel 2007 as well.

I don''t think I can get you any further on this one, sorry.

Peter Jamieson