** RESOLVED ** Excel Object Question (Just to add to the pile)

OK .... I know there are LOADS of Excel based questions on these forums. I've even answered some of them, but this one is a bit different.

I've done enuff Excel based in VB now to know where to find errors that create EXCEL objects 'on the fly', and I know if you create an Excel object, you have to pretty much prefix EVERYTHING with it to stay out of trouble, but I've just stumbled across something else maybe someone can answer.

EVERY time I run a VB app that uses the Excel object, it leaves an EXCEL process when its finished.

Weirdly though, this only happens on Windows 2000 and Windows XP machines. Run it on 95 or 98 and you don't get a problem :confused:

Is there a new 'EXCEL9.OLB' to cover 2000/XP ?

If so I couldn't find one.

The following code is an example, and yes the file does exist :

VB Code:

Private Sub Build_Cus_Enq_1()
    Dim objExcel As Excel.Application
    
    Set objExcel = CreateObject("Excel.Application")
    With objExcel
        .Workbooks.Open "C:\Excel.csv"
        .Visible = True     
    End With
    
    Set objExcel = Nothing
    
End Sub

How do you terminate your instance of Excel?

I must admit, Im on NT, so this might not work for you, but I always open excel by adding the Excel 8.0 object library then:

Code:

---


Dim XLApp as Excel.application
Dim XLBook as Excel.Workbook
Dim XLsheet as Excel.Worksheet

      Set Xlapp = New Excel.application
      Set XLBook = XLApp.Workbooks.Open("C:\Excel.csv")

---

And end it with:

Code:

---


XlApp.Quit
Set XlApp = Nothing
Set XLBook = Nothing
Set XLSheet = Nothing

---

And I dont get any problems.....

.... but I'm not quitting Excel.

I am dumping info onto a spreadsheet for the user to work on.

Thats why I don't do a .QUIT like ni your example.

Setting the Excel object to 'Nothing' should just flush the process.

Like I said previously, this all works fine on Windows 95 & 98, just not on 2000 or XP, so it must be something unique to those Operating Systems .....

:confused:

.... but I'm not quitting Excel.

I am dumping info onto a spreadsheet for the user to work on.

Thats why I don't do a .QUIT like in your example.

Setting the Excel object to 'Nothing' should just flush the process.

Like I said previously, this all works fine on Windows 95 & 98, just not on 2000 or XP, so it must be something unique to those Operating Systems .....

:confused:

The Excel instance that you leave open is the one that is showing up as a process in task manager etc. Any visible instances of Excel will show up in the application list, and ALL instances (including the visible ones) show in the process list as "Excel.exe".

Well thats what I originally thought .... but its not !

When I run this code on Windows 95, Excel stays open, but when you do a CTRL/ALT/DELETE there is no "EXCEL" process there.

Also, a way to prove if an EXCEL process is left open is to export again whilst leaving the first one open.
If the relevant processes have been cleared, you will end up with 2 Excel sessions.
If not, then the second one will fall over whilst trying to process the Excel code in the VB app.

That doesn't necessarily apply to the code I provided, as I am opening a file called "Excel.CSV", which may cause a file lock when trying to re-open, but generally thats the first clue when we test.
The cuplrit is normally something like a RANGE object, which hasn't explicitly been assigned to the Excel object, e.g :

Range("A1").Select

rather than

objExcel.Range("A1").Select

What would be good would be if someone out there with Windows 2000 or XP could cut and paste that code I supplied into an app (and create a quick "Excel.CSV" file), and see if they have the same problem.

if they don't then I'm guessing our hardware team have been doing updates recently, because I never used to have this problem, and my PC hasn't changed for the last 6 months.

Because you don't quit excel, it is normal there is an excel process open after the code is finished.

However, if there still is an excel process open after the user closed excel, then I cannot reproduce your problem with the code you provided (excel 2000 on windows 2000).

I agree with Frans, and to test it you can do this:

* use the task list make sure no Excel processes are running

* open up excel manually

* look in the task list - in the App's list you will have "M$ Excel - <document name>", and in the processes list you will have "Excel.exe".

* Quit Excel, there should now be no entries for it in either apps or processes


This will always happen, it is just the way Windows NT (including 2k & XP) works.

Yes Frans C thats quite correct .... only the process thats left open is called

"Microsoft Excel - Excel.Csv"

and not

EXCEL

As you can see from the attached screenshot, Excel is open and there is no process called "EXCEL", but there IS one called "Microsoft Excel - Excel.Csv"

This was done on a Windows 95 machine.
If I run the same thing on a Windows 2000 or XP machine, I get an extra process called "EXCEL". THIS is the one that represents the Excel object from my bv app, and is not getting cleared properly.

Sorry bionic but i must say like the rest if you want to kill the started process you need to use quit.

The given screenshot doesn't prove anything cause you only show open tasks not processes.

So on a 2000/XP machine do you get something like the attached screenshots?

If so it's normal (see my last post)

Apologies guys :(

I've realised where I am going wrong.

I didn't post ALL my code because I thought the problem was somewhere other than where it actually was.

I just tried re-running the export after running it once (and therefore creating a second Excel instance) and it fell over half way thru.

It took me a minute to notice, but I had missed out a dot where there should have been one (exactly like I described in MY OWN post at the top of this thread :mad: ).

i.e

VB Code:

.Range(Selection, Selection.End(xlToRight)).Select

should have been

VB Code:

.Range(.Selection, .Selection.End(xlToRight)).Select

It was creating a "Selection" object "on the fly" :mad: :mad: :mad:

Apologies again for my stupidity :p .... and thanks for helping me realise it :p ;)