Solved

Excel Objects - from VB

Posted on 2002-05-31
11
178 Views
Last Modified: 2010-05-02
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.
0
Comment
Question by:DaveyByrne
11 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7047095
Hi DaveyByrne,

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.Application")
oXl.visibble = true
oXl.Workbooks.Open("mydoc.xls")

when the user closes the workbook

it will still have the excel app visible till that one closes also

:O)Bruintje
0
 
LVL 1

Author Comment

by:DaveyByrne
ID: 7047126
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!
0
 
LVL 1

Expert Comment

by:royster
ID: 7047167
If would help to see the code your using.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:DaveyByrne
ID: 7047197
' 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:\Dave.xls")

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...
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7047225
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
0
 
LVL 1

Author Comment

by:DaveyByrne
ID: 7047235
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.
0
 
LVL 4

Expert Comment

by:RichW
ID: 7047319
Try this:  I was able to close the worksheet without Excel closing:

Dim excel_app As Object
   
Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = True

With excel_app
    .Workbooks.Open "c:\dave.xls", UpdateLinks:=3, ReadOnly:=False
End With

RichW
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7047632
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7047816
try this:

Dim oExcel as new Excel.Application
dim oWkb as Excel.Workbook

set oWkb = oExcel.Workbooks.Open("C:\Dave.xls")

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
0
 
LVL 4

Accepted Solution

by:
jsweby earned 100 total points
ID: 7061493
DaveyByrne,

Make sure you've set all your objects to nothing, you've most likely got an object which is still holding on to the excel objects. This happened for me before and it took me ages to find out the result.

Jayo.
0
 
LVL 1

Author Comment

by:DaveyByrne
ID: 7062234
Painstaking!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to skip loop 6 57
message box in access 4 46
Hide vba in gp 7 95
How to measure sizes and angles in scanned images ? 3 63
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question