B
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
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