DaveyByrne
asked on
Excel Objects - from VB
Hi there,
I've been wrestling with Excel objects through VB for quite a while - and can't seem to resolve one thing.
When I create an excel object from VB, and then create a workbook within it, it works fine. But when the user closes the worksheet, the entire Excel Application disappears - and sits in the background, gobbling up resources. I have to go into Win NT Task Manager to kill it.
I've heard there is some kind of "flag" on either the Excel.Application object, or the Excel.Worksheet object that will stop this happening - but I can't find it.
This is a major problem, so I'd really appreciate any help,
thanks in advance,
Dave.
I've been wrestling with Excel objects through VB for quite a while - and can't seem to resolve one thing.
When I create an excel object from VB, and then create a workbook within it, it works fine. But when the user closes the worksheet, the entire Excel Application disappears - and sits in the background, gobbling up resources. I have to go into Win NT Task Manager to kill it.
I've heard there is some kind of "flag" on either the Excel.Application object, or the Excel.Worksheet object that will stop this happening - but I can't find it.
This is a major problem, so I'd really appreciate any help,
thanks in advance,
Dave.
ASKER
This isn't the problem - my Excel object has been set to visible... I can't close the workbook manually unless it is.
It's when I've got everything in front of me, and then I close the worksheet - the Excel Application becomes invisible.
I've tried trapping the Workbook Close by using Withevents in my class, and setting my Excel object to visible - but it doesn't make any difference.
Any more ideas?
I haven't got much hair left to pull out!
It's when I've got everything in front of me, and then I close the worksheet - the Excel Application becomes invisible.
I've tried trapping the Workbook Close by using Withevents in my class, and setting my Excel object to visible - but it doesn't make any difference.
Any more ideas?
I haven't got much hair left to pull out!
If would help to see the code your using.
ASKER
' Try the code yourselves... it won't work.
Dim oExcel as new Excel.Application
dim oWkb as Excel.Workbook
set oWkb = oExcel.Workbooks.Open("C:\
oExcel.visible = true
' Do some formatting in the workbook, worksheets...
' Now release the objects, but leave Excel running.
set oWkb = nothing
set oExcel = nothing
' Terminate the program.
' Now the user has still got Excel with the workbook open in front of them.
' But if the user closes the worksheet, the whole Excel object becomes invisible...
Dim oExcel as new Excel.Application
dim oWkb as Excel.Workbook
set oWkb = oExcel.Workbooks.Open("C:\
oExcel.visible = true
' Do some formatting in the workbook, worksheets...
' Now release the objects, but leave Excel running.
set oWkb = nothing
set oExcel = nothing
' Terminate the program.
' Now the user has still got Excel with the workbook open in front of them.
' But if the user closes the worksheet, the whole Excel object becomes invisible...
before you set the oWkb and oExcel to NOTHING, you MUST use exit the associated WorkBook, or Application :
oWkb.Close
set oWkb = Nothing
oExcel.Quit
set oExcel = Nothing
simply setting the objects to Nothing does not affecdt the running instance, in memory, as you have discovered (since these are ActiveX EXE's, they have a lifetime which is completely independent of the VB app which spawned them. And thus it is the reaponsibility of YOUR APP to terminate the executing EXE, BEFORE you get rid of the object reference)
Arthur Wood
oWkb.Close
set oWkb = Nothing
oExcel.Quit
set oExcel = Nothing
simply setting the objects to Nothing does not affecdt the running instance, in memory, as you have discovered (since these are ActiveX EXE's, they have a lifetime which is completely independent of the VB app which spawned them. And thus it is the reaponsibility of YOUR APP to terminate the executing EXE, BEFORE you get rid of the object reference)
Arthur Wood
ASKER
I don't want to quit the Excel Application - I want it to stick around. I just want the references from my VB project to be severed, so I set them to nothing. This lets my ActiveX Server stay alive.
I want this to happen, and it works. My application can terminate successfully, leaving Excel open for the user.
It's an Excel thing - a colleague told me there's a setting on either the Excel object which makes the Excel Object close along with its worksheet... but I can't find it.
I want this to happen, and it works. My application can terminate successfully, leaving Excel open for the user.
It's an Excel thing - a colleague told me there's a setting on either the Excel object which makes the Excel Object close along with its worksheet... but I can't find it.
Try this: I was able to close the worksheet without Excel closing:
Dim excel_app As Object
Set excel_app = CreateObject("Excel.Applic
excel_app.Visible = True
With excel_app
.Workbooks.Open "c:\dave.xls", UpdateLinks:=3, ReadOnly:=False
End With
RichW
Dim excel_app As Object
Set excel_app = CreateObject("Excel.Applic
excel_app.Visible = True
With excel_app
.Workbooks.Open "c:\dave.xls", UpdateLinks:=3, ReadOnly:=False
End With
RichW
There is a read-only property that can tell you if Excel was started by the user or by automation but this property won't block the user from killing it. This property Application.UserControl.
try this:
Dim oExcel as new Excel.Application
dim oWkb as Excel.Workbook
set oWkb = oExcel.Workbooks.Open("C:\
oExcel.visible = true
' Do some formatting in the workbook, worksheets...
' Now release the objects, but leave Excel running.
with owb
.saved=true
.close
end with
set oWkb = nothing
' Terminate the program.
' Now the user has still got Excel with the workbook open in front of them.
' But if the user closes the worksheet, the whole Excel object becomes invisible...
On terminate event of your class
if not (oExcel is nothing) then
set oExcel = nothing
end if
Dim oExcel as new Excel.Application
dim oWkb as Excel.Workbook
set oWkb = oExcel.Workbooks.Open("C:\
oExcel.visible = true
' Do some formatting in the workbook, worksheets...
' Now release the objects, but leave Excel running.
with owb
.saved=true
.close
end with
set oWkb = nothing
' Terminate the program.
' Now the user has still got Excel with the workbook open in front of them.
' But if the user closes the worksheet, the whole Excel object becomes invisible...
On terminate event of your class
if not (oExcel is nothing) then
set oExcel = nothing
end if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Painstaking!
guess you need this line in your code
oXl.visibble = true
but this is safer calling the tree under the object like
Dim oXL as object
set oXL = CreateObject("Excel.Applic
oXl.visibble = true
oXl.Workbooks.Open("mydoc.
when the user closes the workbook
it will still have the excel app visible till that one closes also
:O)Bruintje