Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.programming,microsoft.public.office.developer.automation
|
|||
|
|||
Q317109
Several messages to microsoft newsgroups have been posted regarding excel's
persistence until a program is shut down, but there seems to be no answer. KB article Q317109 http://support.microsoft.com/default...b;en-us;317109 seems to not work? This question seems to be unanswered for VB net. I don't have vb net. Programming with Visual Basic 6, the same behaviour is easily reproduced - excel in the running processes list that won't go away. try this: '--------------------------------------- Dim oXLApp As Excel.Application Dim oXLWb As Excel.Workbook Dim oXLWs As Excel.Worksheet Set oXLApp = New Excel.Application oXLApp.DisplayAlerts = False Set oXLWb = oXLApp.Workbooks.Add oXLWb.Worksheets.Worksheets(1).Name = "my sheet" Set oXLWs = oXLWb.Worksheets("my sheet") '-- insert some data to excel oXLApp.Quit '// the above statement optional whether to close the instance of excel, leave it open, save it prior in VB, whatever. Set oXLWs = Nothing Set oXLWb = Nothing Set oXLApp = Nothing '------------------------------ The excel object will stay in the process list no matter what combination of the above is entered until the calling program is shut down. KB Q317109 says this is by design. ahhhhhhhhhhh. Help please. I want to get rid of excel without my users shutting down the program. |
#2
Posted to microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.programming,microsoft.public.office.developer.automation
|
|||
|
|||
Q317109
Yeager,
Following is my "canned" answer to the won't quit issue. In addition, the line of code... oXLWb.Worksheets.Worksheets(1).Name = "my sheet" should read... oXLWb.Worksheets(1).Name = "my sheet" '------------------------------------------------------------- Here are some general guidelines to use when automating Excel... 1. Set a reference to the primary Excel objects used in your program. Dim xlApp As Excel.Application Dim WB As Excel.Workbook Dim WS As Excel.Worksheet Set xlApp = New Excel.Application Set WB = xlApp.Workbooks.Add Set WS = WB.Sheets(1) Use the appropriate reference Every Time you make reference to a spreadsheet. Do not use Range(xx) - use WS.Range(xx) Cells should be WS.Cells(10, 20) or _ WS.Range(WS.Cells(10, 20, WS.Cells(20, 40)) 2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc. Use your object references. 3. Avoid the use of the "With" construct. 4. Set all objects to Nothing in the proper order - child then parent. Set WS = Nothing WB.Close SaveChanges:=True 'your choice Set WB = Nothing xlApp.Quit Set xlApp = Nothing Violating any of these guidelines can leave "orphans" that still refer to Excel and prevent the application from closing. '------------------------------------------------------------- Articles dealing with unqualified references and automation application not quitting: 1. 178510 - PRB: Excel Automation Fails Second Time Code Runs http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b178510 Summary: While running code that uses Automation to control Microsoft Excel, one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error: Run-time error '1004': Method '<name of method' of object '_Global' failed -or-... 2. 189618 - PRB: Automation Error Calling Unqualified Method or Property http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b189618 Summary: While running code that uses Automation to control Microsoft Word 97, Word 2000, or Word 2002, you may receive one of the following error messages: Run-time error '-2147023174' (800706ba) Automation error -or- Run-time error '462': The remote server... 3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory http://support.microsoft.com/default...;en-us;q199219 When you run a macro that uses automation to create a Microsoft Excel object (instance), the Excel object does not exit from memory when you specify. If you create another Excel object after quitting the first, a second instance is in memory. This problem occurs when your macro uses a "WITH" statement that refers to the automation object. 4. 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b319832 Summary: When you automate a Microsoft Office application, you may receive an error message or you may experience unexpected behavior, as follows. You may receive one of the following error messages: Error 91: Object variable or With block variable not set.... '------------------------------------------------------------- Regards, Jim Cone San Francisco, CA "Yeager Simpson" wrote in message ... Several messages to microsoft newsgroups have been posted regarding excel's persistence until a program is shut down, but there seems to be no answer. KB article Q317109 http://support.microsoft.com/default...b;en-us;317109 seems to not work? This question seems to be unanswered for VB net. I don't have vb net. Programming with Visual Basic 6, the same behaviour is easily reproduced - excel in the running processes list that won't go away. try this: '--------------------------------------- Dim oXLApp As Excel.Application Dim oXLWb As Excel.Workbook Dim oXLWs As Excel.Worksheet Set oXLApp = New Excel.Application oXLApp.DisplayAlerts = False Set oXLWb = oXLApp.Workbooks.Add oXLWb.Worksheets.Worksheets(1).Name = "my sheet" Set oXLWs = oXLWb.Worksheets("my sheet") '-- insert some data to excel oXLApp.Quit '// the above statement optional whether to close the instance of excel, leave it open, save it prior in VB, whatever. Set oXLWs = Nothing Set oXLWb = Nothing Set oXLApp = Nothing '------------------------------ The excel object will stay in the process list no matter what combination of the above is entered until the calling program is shut down. KB Q317109 says this is by design. ahhhhhhhhhhh. Help please. I want to get rid of excel without my users shutting down the program. |
#3
Posted to microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.programming,microsoft.public.office.developer.automation
|
|||
|
|||
Q317109
Jim
Your note has been extremely helpful. Before I read this I had been searching for a while to resolve the persistence of EXCEL.EXE process. Thanks very much. Naveen |
#4
Posted to microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.programming,microsoft.public.office.developer.automation
|
|||
|
|||
Q317109
I have been working on making excel go away, only in VC++, using the example
code I found in KB articles 186120, 186122, and 179706. So far I have narrowed it down to using the Open method of the Workbooks object. If I start Excel, add a workbook, put some information in it, save the workbook and call the Quit method on the application object, Excel disappears from the system's task list, just like its supposed to. If I start Excel, get a reference to the workbooks object, though GetWorkbooks, and open a workbook, (with no other action taking place) Excel disappears from view, but won't exit the systems task list. Next approach is grabbing a snapshot of the processes in the system, before and after Excel is starting, then killing off the new copy of Excel when I need Excel to exit. Not pretty, but have no other ideas at present... There is a lot of extra potential for my application if I can solve this, any help is appreciated. David "Naveen" wrote: Jim Your note has been extremely helpful. Before I read this I had been searching for a while to resolve the persistence of EXCEL.EXE process. Thanks very much. Naveen |
#5
Posted to microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.excel.programming,microsoft.public.office.developer.automation
|
|||
|
|||
Q317109
Hi,
This works for me. I hope it helps you. Private Function closeExcel() As Short Dim count As Short = 0 Dim excelInstance As System.Diagnostics.Process Dim excelInstances() As Process = System.Diagnostics.Process.GetProcessesByName("Exc el") For Each excelInstance In excelInstances Try excelInstance.Close() '<-- you can use close or kill .. up to you excelInstance.Kill() count += 1 Catch ex As Exception End Try Next Return count End Function http://www.kjmsolutions.com/datasetarray.htm "DGT3" wrote in message : I have been working on making excel go away, only in VC++, using the example code I found in KB articles 186120, 186122, and 179706. So far I have narrowed it down to using the Open method of the Workbooks object. If I start Excel, add a workbook, put some information in it, save the workbook and call the Quit method on the application object, Excel disappears from the system's task list, just like its supposed to. If I start Excel, get a reference to the workbooks object, though GetWorkbooks, and open a workbook, (with no other action taking place) Excel disappears from view, but won't exit the systems task list. Next approach is grabbing a snapshot of the processes in the system, before and after Excel is starting, then killing off the new copy of Excel when I need Excel to exit. Not pretty, but have no other ideas at present... There is a lot of extra potential for my application if I can solve this, any help is appreciated. David "Naveen" wrote: Jim Your note has been extremely helpful. Before I read this I had been searching for a while to resolve the persistence of EXCEL.EXE process. Thanks very much. Naveen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|