Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Excel Application Object Behavior
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Excel Application Object Behavior
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... -- HTH... Jim Thomlinson "Budget Programmer" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Excel Application Object Behavior
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! -- Programmer on Budget "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Budget Programmer" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Excel Application Object Behavior
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. -- Programmer on Budget "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Budget Programmer" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Excel behavior please Help | Excel Discussion (Misc queries) | |||
Excel Theme Colors Strange Behavior | Excel Discussion (Misc queries) | |||
Strange behavior in Excel 2003 | Excel Programming | |||
Excel Mac OS X - Strange Behavior | Excel Discussion (Misc queries) | |||
FormulaR1C1 gets strange application/object defined error | Excel Programming |