Excel Obect in Visual Basic 6.0 - Safely cleanup object

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PS - 13 Apr 2008 06:40 GMT

Hi,

I have an application in which I am referencing Excel Object. Here is the
piece of code using which I am instantiating Excel Object,

Set oXL = CreateObject("Excel.Application")

I have used Form_QueryUnload event and used following code in it to clean up
the excel object and quit if from VB6

If IsObject(oXL) Then
oXL.Run ("DeleteMenuItems")
oXL.ActiveWorkbook.Close False
oXL.Quit
Set oXL = Nothing
oXL = Empty
End If

This code works pretty fine if the application ends properly by Unload
method or X button of the winform.

But if by any chance, I close the application using Task manager it is not
able to close the excel instance created from my App this cause an instance
of Excel running in the task manager.

Can you plesae tell me how can I safely close the excel object even if the
parent application (VB6) closes abruptly from task manager of due to power
failure?

Signature

Thanks,
PS

Reply to this Message

expvb - 13 Apr 2008 17:35 GMT

> If IsObject(oXL) Then

In VB6, IsObject() is not the correct function to use, it would return True
if the variable is set to Nothing, and this would create a run time error at
the next line(Object or with variable not set). Use the following instead:

If Not oXL Is Nothing Then

In the line above, "Is" is evaluated first, then "Not" next, so there is no
need to add parenthesis.

> Can you plesae tell me how can I safely close the excel object even if the
> parent application (VB6) closes abruptly from task manager of due to power
> failure?

If your application terminates, then it has no control. However, when your
application starts, you could use GetObject() to get a pointer to a
currently running copy of Excel. However, it's not possible to tell it which
copy to use, or cycle through all running copies of Excel. You could check
the contents of the Excel object to see if it's yours, perhaps you could put
the value "Not done" and "Created by ..." in some of the cells.

Reply to this Message

PS - 13 Apr 2008 18:08 GMT

Hi,

I have tried creating a dummy class and put the code in Class_Terminate
event too, considerting that Class_Terminate event will fire every time when
object goes out of scope, but that too didnt help much.

Please suggest me some other idea. Do we have anything in VB6 like that of
IDispose interface of .Net?

Signature

Thanks,
PS

> > If IsObject(oXL) Then
>
[quoted text clipped - 17 lines]
> the contents of the Excel object to see if it's yours, perhaps you could put
> the value "Not done" and "Created by ..." in some of the cells.

Reply to this Message

Ralph - 13 Apr 2008 18:56 GMT

> Hi,
>
[quoted text clipped - 4 lines]
> Please suggest me some other idea. Do we have anything in VB6 like that of
> IDispose interface of .Net?

You probably need to provide more information, or perhaps you didn't quite
understand what expvb was trying to tell you. Re-read his post.

Your question:
"Can you plesae tell me how can I safely close the excel object even if the
parent application (VB6) closes abruptly from task manager of due to power
failure?"
Didn't make much sense, since one would assume a power failure will kill VB
and Excel - so something else must be going on here.

Are you just having trouble shutting down Excel once you open it? Is the
User using Excel while you are automating it? ... ?

See if these help:
"How to obtain the window handle for an Office Automation server"
http://support.microsoft.com/kb/258511/

"Kill an App Gently"
http://vb.mvps.org/articles/dd200109.asp

-ralph

Reply to this Message

PS - 14 Apr 2008 06:04 GMT

Hi,

> Are you just having trouble shutting down Excel once you open it? Is the

Yes. My only problem is shutting down the Excel Only if user terminates
application from the Task manager (sorry I have put due to power failure on
my last post).

> User using Excel while you are automating it? ... ?

Yes. User is using the Excel while automating.

Signature

Thanks,
PS

> > Hi,
> >
[quoted text clipped - 27 lines]
>
> -ralph

Reply to this Message

Ralph - 14 Apr 2008 15:46 GMT

> Hi,
>
[quoted text clipped - 6 lines]
> > User using Excel while you are automating it? ... ?
> Yes. User is using the Excel while automating.

With an interactive User using Excel at the same time you are automating it,
you can run into different issues in closing Excel, depending on what you
and the user are doing. If chewing on the same worksheet you are quite
likely to be unsuccessful as the User "wins" most of the time. If dealing
with a 'hidden' worksheet, you are likely to be more successful. It depends
on the actual scenario.

However, before we can get into that we need more information and you need
to rework your code so that you can successfully open, do stuff, and close
Excel without problems, excluding the User. Once you have that down then you
will be better prepared to tackle the other.

Check-out this site.
http://www.vbforums.com/showthread.php?t=391665

It primarily uses Early-Binding. If you are using VB and know the specific
version of Excel you will always be dealing with it has some advantages. It
is also useful to use Early-Binding during development. You can always
switch back for Release. The general techniques are the same if using Early
or Late Binding.

As the others have noted do NOT use IsObject(). This only tells you the
*Data Type* of the variable. It tells you nothing about whether a reference
variable actually points to a valid object. This is also important for ALL
OBJECTS you create in the Excel Model. Note: the section on "Excel doesn't
close".

Once you can reliably open Excel, do stuff, and close it. Then we can
address the issue of an interactive User - but we need information about the
exact scenario in your problem domain.

hth
-ralph

Reply to this Message

expvb - 13 Apr 2008 23:12 GMT

> Hi,
>
> I have tried creating a dummy class and put the code in Class_Terminate
> event too, considerting that Class_Terminate event will fire every time
> when
> object goes out of scope, but that too didnt help much.

There is nothing your application can do if the user kills the process,
execution is halted immediately including any cleanup code, so tell the user
to live with consequences of killing the process, it's not your fault. There
is not much you can do about it. If your application crashes, that's another
story. You need to trap any errors and fix what's causing them.

Reply to this Message

PS - 14 Apr 2008 05:55 GMT

Hi,

I have checked IsObject because the oXL is late bound variable declared as

Dim oXL

Since, I dont know if Object is created or not I am checking it with IsObject.

> If your application terminates, then it has no control. However, when your
> application starts, you could use GetObject() to get a pointer to a
> currently running copy of Excel. However, it's not possible to tell it which
> copy to use, or cycle through all running copies of Excel. You could check
> the contents of the Excel object to see if it's yours, perhaps you could put
> the value "Not done" and "Created by ..." in some of the cells.

You are right, that we dont have a control if application terminates. I just
wanted to know if we have something like IDisposible interface of .Net.

Signature

Thanks,
PS

Reply to this Message

Dmitriy Antonov - 14 Apr 2008 07:40 GMT

1. It's better to declare it here as Object rather than Variant. Otherwise
you need something like this

if isobject(oXL) then
if not oXL is nothing then
...
end if
end if

2. There is no need to have anything like IDisposable interface in VB6 -
every object here is disposable and disposed immediately whenever it goes
out of scope.
When the program is terminated from the TM or in similar way then no code is
executed.
And I don't think IDisposable interface will help in .NET in such case -
most likely you will have the same problem there too. I doubt that garbage
collection is performed if .NET application is terminated from the Task
Manager.

Dmitriy.

> Hi,
>
[quoted text clipped - 19 lines]
> just
> wanted to know if we have something like IDisposible interface of .Net.

Reply to this Message

expvb - 14 Apr 2008 16:13 GMT

> Hi,
>
> I have checked IsObject because the oXL is late bound variable declared as
>
> Dim oXL

Change that to:

Dim oXL As Object

This is late binding too.

> If IsObject(oXL) Then
> oXL.Run ("DeleteMenuItems")
[quoted text clipped - 3 lines]
> oXL = Empty
> End If

Change that to

Dim oActiveWorkbook As Object

If Not oXL Is Nothing Then
oXL.Run ("DeleteMenuItems")
Set oActiveWorkbook = oXL.ActiveWorkbook
If Not oActiveWorkbook Is Nothing Then
oActiveWorkbook.Close False
End If
oXL.Quit
Set oXL = Nothing
End If

Also add error handling in Form_Unload and see if you get any error
messages. If your application crashes, state what you did to make it crash.
Note that you don't have to close the work book or call Quit method if you
want the user to keep working on it. The only thing you need to do in that
case is set oXL to Nothing.

Reply to this Message

expvb - 14 Apr 2008 00:44 GMT

> oXL.ActiveWorkbook.Close False

I don't like the above line because ActiveWorkbook is a property that
returns an object that could potentially be null, and the format above
doesn't check it. Here is what I would do:

Dim oBook As Object

Set oBook = oXL.ActiveWorkbook
If Not oBook Is Nothing Then
oBook.Close False
End If

Reply to this Message

VB Forum / COM / April 2008

Excel Obect in Visual Basic 6.0 - Safely cleanup object