Posted to microsoft.public.word.mailmerge.fields
|
|
MailMerge opens a 2nd Excel App
Thanks Peter, option 2 did the trick
"Peter Jamieson" wrote:
1. Could anything be blocking the opening of a new DDE connection to Excel?
Not sure what it might be, but for example an open Excel dialog box, that
kind of thing?
2. Is it an option for you to connect to the data source programmatically
(i.e. save the document without the data source and use OpenDataSource to
open it)? I haven't tested that, but wonder whether Word might open it in
the same instance as your existing Excel document in that case. Doubt it,
but perhaps worth a try.
3. If you do not absolutely need the cosy "get the data more or less as
displayed in Excel" behaviour of a DDE connection and you do not need to
have your data source open in Excel at the same time as you connect to it
from Word, you could consider
a. using an ODBC connection instead
b. applying any necessary formatting switches in Word
Peter Jamieson
"Simon Cleal" wrote in message
...
Word/Excel 2000
Hi,
I'm using the following code to open a Word mailmerge doc (with an Excel
data source) from Excel:
Public Sub Merge()
Dim objWord As Word.Application
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If
On Error GoTo 0
objWord.Application.Visible = True
objWord.Documents.Open "T:\APAS\Letters\NR.doc"
Set objWord = Nothing
End Sub
In NR.doc I have this code to merge to a new doc and save the resulting
doc:
Private Sub Document_Open()
Application.ScreenUpdating = False
Application.OnTime When:=Now + TimeValue("00:00:00"), Name:="MergeDoc"
End Sub
Public Sub MergeDoc()
Application.ScreenUpdating = False
Dim strOriginalDoc As String
Dim strDocSuffix As String * 1
Dim strOutputDoc As String
Dim strDocName As String
Dim objField As MailMergeDataField
strOriginalDoc = ActiveDocument.Name
'pick up the file name from the member no
For Each objField In ActiveDocument.MailMerge.DataSource.DataFields
Select Case objField.Name
Case Is = "MemberNo"
strDocName = objField.Value & " NR "
End Select
Next objField
strDocSuffix = " "
strOutputDoc = Trim(strDocName & strDocSuffix)
'files sytem object
Dim objFileSystem As Object
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
'test for file existence
While objFileSystem.fileexists("T:\APAS\FinsihedLetters\ " &
strOutputDoc
& ".doc") = True
Dim intDocSuffix As Integer
intDocSuffix = Asc(strDocSuffix)
If intDocSuffix = 32 Then
intDocSuffix = 65
Else
intDocSuffix = intDocSuffix + 1
End If
strDocSuffix = Chr(intDocSuffix)
strOutputDoc = Trim(strDocName & strDocSuffix)
Wend
strDocRef = Trim(strDocRef & " " & strDocSuffix)
'merge & Save the Doc
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
ActiveDocument.SaveAs FileName:="T:\APAS\FinishedLetters\" &
strOutputDoc
Documents(strOriginalDoc).Close False
End Sub
The code mostly works but...
...when word opens the mail merge doc it also opens a 2nd Excel
application,
this 2nd Excel automatically tries to open my personal macro workbook
(PERSONAL.xls) which is in use by the original Excel application resulting
in
a Open Read Only, Retry, Cancel message box which has to be answered
before
the merge doc will open. It also opens a new Book1.xls which owing to my
default Excel workbook has changed and so Excel wants to save changes
before
it will close down the mailmerge data source and the 2nd Excel app. This
severly slows down the whole process and rather defeats the object of
using
VBA.
When the document is opened 'by hand' everything runs smoothly
Is there a way to 'force' Word to open the Excel data source in the
existing
Excel app rather than start a new one?
Thanks in advance for any help
Simon
PS sorry if this post is long winded but I figure better too much info
than
too little!
|