[Simon Cleal]

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!