VB 5 & MS Excel 97 - Process not releasing (Part 2)


For those not already familiar this relates to my previous questions yesterday "VB 5 & MS Excel 97 - Process not releasing".

I am just going to follow on from there.

I think that I have identified why the Excel Process is not releasing but as of yet can not find a solution.

The code works fine if you open an Excel File, Save it and Close.

The Problem is introduced when you perform some formatting and layout changes to the Excel File.

As I am converting a CSV file to XLS and performming formatting and layout changes I get the Message box on saving notifying that this CSV was saved in a previsous version of excel - Do you want to save in 97, so on.

The problem is that I have yet not seen a way to answer yes to this and this seems to be what keeps the Excel process open.

If you leave DisplayAlerts = True and try modify a file of this format you will get the message box I am talking about and the Excel process should not release after you have set xlApp = nothing.

Any ideas on this one guys?

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

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.

When you save, are you specifying the second (file format) parameter? I think that, if you do, then Excel will not have to ask you?

If you don't want to save, you can set the Workbook property .Saved to true.

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

Thanks for the prompt response:  The Save that I use is as follows:

    loExcel_Book.SaveAs psSave_To_Loc & lsSave_File_Name, xlExcel4Workbook

Excel does not ask for the new file format when this is specified.  If you perform no formatting or changes to the file this saves fine as [Filename].xls.  If you the open this XLS file make a formatting change and try to save you get:

[FileName] was created in a previous version of Microsft Excel.  Do you want to update it to the new format?

To update it click yes.
To Save it in the existing format, click no. Some types of changes may be lost.

Any ideas
Just to be sure that you've considered the question, do you really need to save is as Excel 4?
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:

Cheers for that - I am looking at it now and will get back to you.  I did not notice that the save is Excel4 instead of the normal!

Hopefully I will revert soon with good news.
Elmo_Head of InfrastructureAuthor Commented:

Sorry that I only gave you a B - But I could not give you full marks as it was not exactly the solution I was looking for but it did help.




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!

Great. Thanks for the update
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.