Strange Excel Application Object Behavior

[Budget Programmer]

Hello,
I'm running a macro in Word, which opens two (template) workbooks, copies
various data from Word and places it into Excel cells, and saves them as
another file. If it is executed again, it overwrites the previously saved
files. The macro leaves the workbooks open for the user once it completes.
This behaves fine. But Excel doesn't behave well sometimes after this, and I
think it may have something to do with the way I'm handling the Application
or Workbook objects. Here's how the problem gets produced.
1 Code is run 1 to x many times
2 Two individual Excel icons appear on the taskbar They have the smaller
green "X" as an icon
3 User closes the two workbooks
4 An Excel application remains (no blank worksheet, just gray), with the
larger green "X" as an icon
5 The user runs the macro again
6 The two worksheets appear "Frozen". When I click on the workbook icons I
get nothing. The macro complets properly, but I have to force-close the
workbooks, the Excel application and close the Word document before Excel
will work properly again.

I've stripped out the processing part of the Macro, and run this
stripped-down macro to reproduce the problem.

If I copy this stripped-down code, delete the references to Word, and run it
from a separate Excel workbook, Excel does NOT freeze in the above scenario.

Any ideas why this macro running from Word would produce Excel objects that
"freeze"?

Sub CreateTestCasesAndTraceability()
' Macro1 Macro
' Macro recorded 7/21/2008 by PMandevi
' Declare Variables
Dim obXlTestAPP As Excel.Application
Dim obXlTestFile As Excel.Workbook
Dim obXlTraceFile As Excel.Workbook
Dim obWdApp As Word.Application
Dim obWdDoc As Word.Document
Dim blXlExcelWasNotRunning As Boolean
Dim vrXlTestTemplateFileName As Variant
Dim vrXlTestTemplateFullName As Variant
Dim vrXlTestFileName As Variant
Dim vrXlTestFullName As Variant
Dim vrXlTraceTemplateFileName As Variant
Dim vrXlTraceTemplateFullName As Variant
Dim vrXlTraceFileName As Variant
Dim vrXlTraceFullName As Variant

' Set Initial Values
Let vrXlTestTemplateFileName = "TestCaseTemplate.xls"
Let vrXlTestTemplateFullName = "H:\cim\ExtractsRUs\Management\DOCUMENT
TEMPLATES\TestCaseTemplate.xls"
Let vrXlTraceTemplateFileName = "TraceabilityMatrixTemplate.xls"
Let vrXlTraceTemplateFullName = "H:\cim\ExtractsRUs\Management\DOCUMENT
TEMPLATES\TraceabilityMatrixTemplate.xls"
Let vrXlTestFileName = "TestCasesProject3.xls"
Let vrXlTraceFileName = "TraceabilityMatrixProject3.xls"
Let stXlTestWorksheetName = "ProjectSpecificCases"
Let stXlTraceWorksheetName = "TraceabilityMatrix"
Let vrWdReqFullName = ActiveDocument.FullName
Let vrWdReqFileName = ActiveDocument.Name

'Start Excel
'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set obXlTestAPP = GetObject(, "Excel.Application")
If Err Then
blXlExcelWasNotRunning = True
Set obXlTestAPP = Excel.Application
End If

'If the Test file is open, from before, then save it and close it.
On Error Resume Next
Workbooks(vrXlTestFileName).Close SaveChanges:=True
On Error GoTo 0

'If the Trace file is open, from before, then save it and close it.
On Error Resume Next
Workbooks(vrXlTraceFileName).Close SaveChanges:=True
On Error GoTo 0

' Activate the current document
Set obWdDoc = ActiveDocument

'Let the Excel application be visible to the user
obXlTestAPP.Application.ShowWindowsInTaskbar = True
obXlTestAPP.Application.Visible = True

'Assign the variable vrXlTestFullName = The vrProjectDirectoryPart1,
vrWdReqSrNumber, vrXlTestFolder and vrXlTestFilename
Let vrXlTestFullName = "H:\cim\ExtractsRUs\Projects\00003\4
Test\TestCasesProject3.xls"
'Assign the variable stTraceFullName = The vrProjectDirectoryPart1,
vrWdReqSrNumber, vrXlTraceFolder and vrXlTraceFilename
Let vrXlTraceFullName = "H:\cim\ExtractsRUs\Projects\00003\5
General\TraceabilityMatrixProject3.xls"

'Open the workbook stored in "vrXlTestTemplateFullname" Note: Open uses the
Folder and Filename (FullName)
Set obXlTestFile = Workbooks.Open(FileName:=vrXlTestTemplateFullName)

'Save the template as a new file using "vrXlTestFullName" Note: Save and
SaveAs uses the Folder and Filename (FullName)
'By using SaveAs, the original template is automatically closed and does not
need to be closed "manually"

Excel.Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=vrXlTestFullName
Excel.Application.DisplayAlerts = True

'Open the workbook stored in "vrXlTraceTemplateFullname" Note: Open uses the
Folder and Filename (FullName)
Set obXlTraceFile = Workbooks.Open(FileName:=vrXlTraceTemplateFullName)

'Save the template as a new file using "vrXlTraceFullName" Note: Save and
SaveAs uses the Folder and Filename (FullName)
'By using SaveAs, the original template is automatically closed and does not
need to be closed "manually"
Excel.Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=vrXlTraceFullName
Excel.Application.DisplayAlerts = True

' NOTE: PROCESSING OCCURS HERE

' Activate the obXlTestFile workbook and save it
obXlTestFile.Activate
obXlTestFile.Save

' Activate the obXlTraceFile workbook and save it
obXlTraceFile.Activate
obXlTraceFile.Save

' Activate the Word document again
obWdDoc.Activate

Exunt:
Set obXlTestAPP = Nothing
Set obXlTestFile = Nothing
Set obXlTraceFile = Nothing
Set obWdApp = Nothing
Set obWdDoc = Nothing
MsgBox "The Macro completed successfully", vbOKOnly, "CONGRATULATIONS"
Exit Sub

Err_Handler:
Excel.Application.DisplayAlerts = False
'If the workbook obXlTestFile was open (if it was Not Nothing), then save it
and close it.
If Not obXlTestFile Is Nothing Then
obXlTestFile.Save
obXlTestFile.Close
End If

'If the workbook obXlTraceFile was open (if it was Not Nothing), then save
it and close it.
If Not obXlTraceFile Is Nothing Then
obXlTraceFile.Save
obXlTraceFile.Close
End If

'If Excel was runing when this macro started, leave it running. There are
probably other workbooks open.
'If Excel was Not running when this macro started (blXlExcelWasNotRunning =
False) then quit Excel
If blXlExcelWasNotRunning = True Then
obXlTestAPP.Quit
End If

'Set the Objects to "Nothing" to initialize the object
Set obXlTestAPP = Nothing
Set obXlTestFile = Nothing
Set obXlTraceFile = Nothing
Set obWdApp = Nothing
Set obWdDoc = Nothing
Excel.Application.DisplayAlerts = True
End Sub

Programmer on Budget

 

[Jim Thomlinson]

Hard to know since I can't actually run the code but one thing that I notice
is that you use the line(s)...

Excel.Application.DisplayAlerts = False
which may be causing some diffictulty since it does not have a direct link
to your object
obXlTestAPP

try changing that line to
obXlTestAPP.DisplayAlerts = False

just a shot in the dark...

 

[Budget Programmer]

Jim,
It worked like a champ. I'm guessing that since I used
"Excel.Application.xxxxx" that clearing the worksheet object at the end of
the macro had no effect on the "Excel.Application.xxxxx" handle. Your "just
a shot in the dark..." was spot-on. You have great insight!
Many Thanks!

 

[Budget Programmer]

Jim,
Update: Your advice definitely helped. When I follow steps 1 - 6, the
problem is now avoided. However, when I modifiy step 4 (The Excel
application remains with a blank worksheet) there's still a problem. If, in
step 4 I close that application through File / Close and then run the macro
from the Word document which has remained open, the same results appear. The
maco completes, but the worksheets are frozen. I tried inserting
"Excel.Application = Nothing" at the end of the Macro, but it generated an
error.
This was a big step forward though.
By the way, that code can be run if you just change the file names to be
something on your PC. The contents of the spreadsheets wouldn't matter, they
could be blank.
How would I get rid of the handle to that Excel application?
Thanks again for your help.