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.
Application.ScreenUpdating = False
setMergeVariables 'sets all the global variables not dimensoned
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 &
Name:=stProjectPath & sDataFileName, _
Connection:=stProjectPath & sDataFileName, _
'tidy up - release references
Set docLetterFile = Nothing
Set oWApp = Nothing
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...