VB 5 & MS Excel 97 - Process not releasing

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!

 
LVL 3
Elmo_Head of InfrastructureAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RogueSolutionsCommented:

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
RogueSolutionsCommented:

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
mlmccCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Elmo_Head of InfrastructureAuthor Commented:
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
RogueSolutionsCommented:
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
Elmo_Head of InfrastructureAuthor Commented:
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
RogueSolutionsCommented:
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
Elmo_Head of InfrastructureAuthor Commented:
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
RogueSolutionsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
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
Elmo_Head of InfrastructureAuthor Commented:
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
Elmo_Head of InfrastructureAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.