Mail merge issue - ghost Excel process remains after closing application

[d_b]

Hi all,
I have seen similar problems in some old threads and other forums, but
no workable solution so far.

The problem I have is this:

I use Excel worksheet as a data source for Word mail merge, and I
wrote a sub in Excel VBA to open the word document and link it to it's
data source, which works fine. But when both Word and Excel are
closed, there is still an Excel process in the memory, it shows up on
Task Manager processes list. This ghost process causes opening failure
for all future instances of Excel, untill it is manually terminated or
PC is restarted.

The code in question is below.

Sub mergeAndViewLetters()

Application.ScreenUpdating = False
setMergeVariables 'sets all the global variables not dimensoned
here

Dim sDataFileName As String
Dim sSQL As String
Dim oWApp As New Word.Application
'early binding, requires MS Word Library
Dim docLetterFile As Word.Document

sDataFileName = ThisWorkbook.Name
sSQL = "SELECT * FROM `MergeData$`"

oWApp.Visible = True
Set docLetterFile = oWApp.Documents.Open(stProjectPath &
stLetterFileName)


docLetterFile.MailMerge.OpenDataSource _
Name:=stProjectPath & sDataFileName, _
LinkToSource:=True, _
Revert:=False, _
Connection:=stProjectPath & sDataFileName, _
SQLStatement:=sSQL


docLetterFile.Activate

'tidy up - release references
Set docLetterFile = Nothing
Set oWApp = Nothing

End Sub



I made an attempt at releasing all the references. I even tried
closing the data source or pointing it to a non-Excel dummy data
source on closing of the Word document (not in this version of the
code) - none of that worked.

There was a suggestions to use only fully qualified references to
Excel objects, I might try that, but it will not be easy and there is
no guarantee that I won't miss something.



Need urgent guru intervention...
Thank you

 

[nath]

This is how i have done it.

Sub Mail_Merge()

Dim wrd As Word.Application
Dim doc As Word.Document
Dim wrdSelection, wrdMailmerge, wrdMergefields As Object

Set wrd = CreateObject("word.application")
Set doc = wrd.Documents.Add
Set wrdMailmerge = doc.MailMerge
wrdMailmerge.MainDocumentType = wdMailingLabels
wrd.Application.MailingLabel.CreateNewDocument

Set wrdMergefields = wrdMailmerge.Fields
Set wrdSelection = wrd.Application.Selection
Set dSrcField = wrdMailmerge.DataSource.FieldNames

wrd.ActiveDocument.MailMerge.OpenDataSource
Name:="z:\data_for_merge.xls", _
linktosource:=True, _
sqlstatement:="SELECT
title,forename,surname,Add1,Add2,add3,add4,postcode FROM
`Sheet1$`"

For Each fld In dSrcField
If Not fld Is Nothing Then
Select Case LCase(Left(fld.Name, 3))
Case "add", "pos"
wrdSelection.TypeText vbCrLf
wrdMergefields.Add wrdSelection.Range, fld.Name
Case Else
wrdMergefields.Add wrdSelection.Range, fld.Name
wrdSelection.TypeText " "
End Select
End If
Next fld

wrd.Application.MailingLabel.DefaultLabelName = "L7160"
wrd.WordBasic.MailMergePropagateLabel
wrdMailmerge.Execute
wrd.Visible = True

doc.Close False
Set wrd = Nothing

End Sub