Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB 5 & MS Excel 97 - Process not releasing

Posted on 2004-11-22
12
Medium Priority
?
199 Views
Last Modified: 2010-05-02
Hi Folks,

Have a question for you about a problem I am currently having and can not seem to get around.

The basic functions of the VB app is to perform file handling.  There was a new section added to the application as a new file to be handled needed to be formatted and saves as an [Name].XLS file.  It was previously in [Name].CSV format.  There is no problem with the formatting code that I have written.  This section works fine.

The problem seems to be that when the application has successfully formatted and moved one file to its new location it gives an error when it tries to process another file of the same type.  (This is an automation error).  

I think that this problem is coming down to the excel process not being released after the application has been quit.  For some reason after the code has ran, if you go to the process list it will show an "excel.exe".

Does anyone have any ideas as to why this is not being released correctly?  (Note; it is being released when the VB application is terminated.)

The code I am using to close this is as follows:

    Set loSheet = Nothing
    loExcel_Book.Close
    Set loExcel_Book = Nothing
    loExcel_App.Application.Quit
    Set loExcel_App = Nothing
   
    DoEvents

Thanks in advance for any help.  It is very much appreciated!

 
0
Comment
Question by:Elmo_
  • 5
  • 5
  • 2
12 Comments
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12644321

I assume your Excel automation runs behind the scenes, i.e. the user doesn't see it?

It might be that Excel is asking a question regarding the close and that prevents the Quit from operating.

Try
   
    Set loSheet = Nothing
    loExcel_Book.Close SaveChanges:=False
    Set loExcel_Book = Nothing
    loExcel_App.Application.Quit
    Set loExcel_App = Nothing
   
    DoEvents

You might want SaveChanges:=True if you are trying to keep the Excel wokbook of course.

0
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12644379

Hmm, just tested my theory and I do actually get asked by Excel if I want to save changes even though Excel isn't visible.  It does get stuck if I say cancel though.

How are you creating you Excel object?  Early or Late Binding?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 12644545
This question ha sbeen asked.  
Here is one of the solutions
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20518857.html

If that doesn't work, search VB for CLOSE EXCEL

mlmcc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:Elmo_
ID: 12645059
Guys thanks for the prompt response!

RogueSolutions,

To answer your questions:

1. Yes, Excel Runs in the background - It actually runs on a server
2. I do not think that it asking a questions as I have set loExcel_App.DisplayAlerts = False
3. When I create the Excel object I use early binding.

The code suggestion you gave does not seem to make a difference.

mlmcc, Had a look at this and the answer does not seem to help me.  Also had a quick look in help and could not see anything there as to why the process would not release!

Any other Ideas guys!  I more than willing to pay more points for this one!  It is really buggin me now!

Cheers,

Ed.
0
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12645226
Ed.

My test code was using early binding as well so I guess that makes no difference.

Couple of further thoughts.

1) To quit the application you should only need loExcel_App.Quit since your application variable is loExcel_App I presume - the extra .Application isn't required.  That said I can't see why it would matter.

2) Is any chance that an (unexpected) event is occuring the triggers the creation of the Excel object.  In other words, the Quit code is fine but another object gets started and that's what you see in Task Manager.
0
 
LVL 3

Author Comment

by:Elmo_
ID: 12645500
RogueSolutions,

1) Removed the Extra "Application" - No effect as expected

2) I do not think that any enxtra is being triggered as I have stepped through the code (With excel Visible)and can not see anything extra happening.

I did try a few other things like, Using late Binding ans changing the order of the Close statements.  

Changing the order of the close statements did have a small effect but the process is still being kept in memory.  The effect was that when you set loSheet1 = Nothing it terminates Excel.  My code now looks like:

    loExcel_Book.Close
    loExcel_App.Quit
    Set loSheet = Nothing
    Set loExcel_Book = Nothing
    Set loExcel_App = Nothing

Any further ideas?
0
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12645641
Ed

Having stepped my version (with the same code order you have) the process is released when the loExcel_App is set to nothing.

The Application (if visible) disappears when the Quit is executed.

---

Maybe the fact it's running on a server has some additional effects?  Could you duplicate the code against a non-server PC and see if it responds the same?

Apart from that I am out of suggestions, sorry!

0
 
LVL 3

Author Comment

by:Elmo_
ID: 12645939
RogueSolutions,

Could you post the code that you have so I can try replicate on my machine.  It could possibly be something to do with the os as well (NT)  we are moving to XP in the next few months though.

Thanks again for your help and after you post the code I will give you the points (I want to accept that as the answer as it does work for you).

Thanks agains for all your help.

Cheers,

Ed.
0
 
LVL 5

Accepted Solution

by:
RogueSolutions earned 2000 total points
ID: 12646404
Ed

Here's my code.  I created a standard EXE project in VB.  Added the Reference to the Microsoft Excel 8.0 object library.  Put a button on the default form and added this code ...

Private Sub Command1_Click()

    Dim xlApp   As Excel.Application
    Dim xlWb    As Excel.Workbook
    Dim xlWs    As Excel.Worksheet
   
    Set xlApp = New Excel.Application
   
    Set xlWb = xlApp.Workbooks.Add
   
    Set xlWs = xlWb.Sheets(1)
   
    'xlWs.Cells(1, 1).Value = "Hi"
    xlApp.Visible = True
   
    xlWb.Close 'SaveChanges:=False
    xlApp.Quit
   
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
   
End Sub

You can see where I was playing with making a change (and testing the SaveChanges option on the Close) and I also now have the app visible so I can tell when it terminates, as opposed to when its process vanishes.

Points are very welcome but I don't feel I helped that much!

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 12650867
I have the same problem but don't have ready access to the source code anymore so I hadn't tried the suggested solution.

mlmcc
0
 
LVL 3

Author Comment

by:Elmo_
ID: 12652386
Hi guys,

I have run that code and it is fine - Technically I should not be getting this problem.  I am going to run a few more tests to see if i can identify why the error is occurring.  If I come accross anything I will post it here!

Thanks again.

Ed.
0
 
LVL 3

Author Comment

by:Elmo_
ID: 12673830
Just to note.

I finally got this working.

I farmed out the code to a new vb app which initially loads Sub Main.

The excel instance gets createded, generates the report and saves.  When sub main is finished it automatically terminates the app - There by also killing the associated Excel Process which previously would not close.

I then shell this from my main app and everything works fine!

Thanks for all the help!

Ed.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

580 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