Starting Mailmerge from Excel - slow

[Dave Baxandall]

I need to reverse the process of mail merge. I've created
a mail merge template, removed the datasource and created
a macro that is expecting the datasource path in it.

In word I can have the excel datasource open and run the
macro from inside word everything works fine.
but
In Excel I:
1. Create a word.application and call the mail merge
template
2. The spreadsheet is re-opened and I need to click read-
only.
3. But this takes up to 60 seconds to run.

Is there a way to improve the performance?

Thank you
Dave Baxandall

 

[Cindy M -WordMVP-]

Hi Dave,

Since you don't mention which version of Word this is, we
can only guess. From the sound of it, the merge document was
set up in Word 2000 or earlier, and uses DDE to connect to
the data source. And this is what's causing the delay.

I'd try setting up the link to the data source using ODBC.
And to tell you how to do that, we DO need to know which
version of Word you have at hand.

I need to reverse the process of mail merge. I've created
a mail merge template, removed the datasource and created
a macro that is expecting the datasource path in it.

In word I can have the excel datasource open and run the
macro from inside word everything works fine.
but
In Excel I:
1. Create a word.application and call the mail merge
template
2. The spreadsheet is re-opened and I need to click read-
only.
3. But this takes up to 60 seconds to run.

Is there a way to improve the performance?

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

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

 

[Guest]

Sorry about that Cindy. I'm using word 2000. I don't know
what else you need. I think that Word needs to check if
the excel document is open and link to it, not open
another copy of Excel.

You helped me last month also and I appreciate it.
Dave Baxandall
(e-mail address removed)

Thanks

 

[Guest]

Cindy since we are 1/2 a world away I'm going to include
my code

In word: I just change SUB start_it to run in word or excel

Global SaveDocName As Variant
Global FileNamE As String
'Sub start_it()
Sub start_it(Source As String)
FileNamE = Source
'FileNamE = "C:\My Documents\HR Compensation
DisplayOnly 2004 ver1.xls"

Do_The_Merge
End Sub

Sub Do_The_Merge()

Dim SQLstring As String
SQLstring = "SELECT * FROM " & FileNamE & " WHERE ((print
= 'X'))" & ""
Debug.Print SQLstring

ActiveDocument.MailMerge.MainDocumentType =
wdFormLetters
SendKeys "{enter}", True
ActiveDocument.MailMerge.OpenDataSource
Name:=FileNamE, _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="print_it",
SQLStatement:="", _
SQLStatement1:=""
ActiveDocument.MailMerge.DataSource.QueryString =
SQLstring

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute
'Pause:=True
End With

End Sub

The code above works when I run it in word

*******************
My Excel code is:
Sub start_word1()
Dim mWordApp As Word.Application

Const docName As String = "C:\My Documents\HRCOMP1.doc"
Dim Merge_XLs As Variant

On Error GoTo EndIt

Merge_XLs = ActiveWorkbook.FullName

Set mWordApp = New Word.Application

' open the word document
With mWordApp
.Documents.Open FileName:=docName, ReadOnly:=True
.Visible = True
.Run macroname:="start_it", varg1:=Merge_XLs
End With

EndIt:
Debug.Print Err.Number; Err.Description
Set mWordApp = Nothing

End Sub

Thanks again.

 

[Cindy M -WordMVP-]

I'm using word 2000. I think that Word needs to check if

the excel document is open and link to it, not open
another copy of Excel.

Well, Word simply doesn't do this. It's a problem with how
the (VERY OLD) DDE technology functions.

What you should try is activating the "Select method"
checkbox in the "Open Data Source" dialog box. Once you've
chosen the Excel workbook, you should get a list with
(probably) 3 entries. Choose ODBC.

If you don't see the data range you want listed, click
"Options" and activate all the checkboxes you'll get. Now
you should be able to choose the data.

Record this in a macro in order to get the syntax you need
for your code.

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

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

 

[Cindy M -WordMVP-]

Hi DMSPaul,

Can you use ODBC with Word 97?
If so, how?

Sure (Assuming, of course, that the ODBC driver has
been installed) Activate the "Select method" button in the
Open Data Source dialog box. Once you've seleted the data
source file, you should get a list with at least three
entries, including ODBC.

~~Message posted directly in the microsoft news NNTP group

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

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