"File In Use" problem

[johnske]

Ok, this is getting beyond a joke...

(Office 2000) When I get an "Illegal...etc" message the workbook closes and when I reopen it I get this "File In Use" (and is locked for editing by...MYSELF!!!) message that only allows me to use as Read-Only. (AAGGH!! :motz2: )

I then have to reboot to get rid of this message. How do I either disable this particular "Feature" or else change it from Read-Only (this also often comes up with PERSONAL.XLS, so everything I then open has this message) :dunno


TIA
John

[Jacob Hilderbrand]

Is your file crashing at some point? Open the Task Manager and see if there is a process for Excel still running. If so, End Task on it and see if the problem is resolved.

Of course the crashing and illegal opperation is another issue all together.

[johnske]

Thanx Jake,

That got rid of it, but I was (preferably) hoping to disable it to stop it from happening again (it didn't lock it up like this when I used '97, it would just Quit)

Regards,
John :hi:

[Jacob Hilderbrand]

Well you need to take a look at why Excel is crashing and take care of that problem.

Can you post some more of what you are doing when it crashes? Does it crash randomly or when you try to do something specific?

[Cyberdude]

johnske, I've been annoyed by this EXACT problem during by Excel 2000 days, and it's been even worse in Excel 2003. I don't quite know what to look for in Task Mgr. Someone once told me to look to see if there was another copy of Excel running, but I didn't think that could happen. There have been times when I think maybe I quit Excel to quickly...as if there were some shut down things it has to do, and they aren't completed (if that makes sense). I did find that reducing the number of links I use (and I use a LOT of 'em) seems to have helped. Are links processed in any way during shutdown?

[johnske]

johnske, I've been annoyed by this EXACT problem during by Excel 2000 days, and it's been even worse in Excel 2003. I don't quite know what to look for in Task Mgr. Someone once told me to look to see if there was another copy of Excel running, but I didn't think that could happen. There have been times when I think maybe I quit Excel to quickly...as if there were some shut down things it has to do, and they aren't completed (if that makes sense). I did find that reducing the number of links I use (and I use a LOT of 'em) seems to have helped. Are links processed in any way during shutdown?

Hi Cyber,

Have a look here (http://codestuff.mirrorz.com/) and dowload "CodeStuff Starter" (free). It's a more in-depth 'Task manager' that you can keep on your desktop or somewhere else handy.

When Jake suggested I look at the Task Manager I went in there, looked at Processes, saw that EXCEL.EXE was running (even tho I had nothing open), so I clicked 'Terminate' and the problem was solved.


Jake,

It seems to be random, not too frequent, but it always occurs when I'm either trying someone else's code (which I have to do) or sometimes trying out something 'radical' myself that I guess/should know could be problematic (which is my own fault). Apart from that...:dunno

Regards,
John

[Jacob Hilderbrand]

Well at least you know how to fix it now. If it seems to happen a lot you may want to re-install Office.

[johnske]

Well at least you know how to fix it now. If it seems to happen a lot you may want to re-install Office.

Well I had no choice today... Office decided it'd finally had enough of me :motz2: and crashed completely (is Office Union or Management?) so I had to uninstall/reinstall it anyway :rotlaugh: :rofl: (musta heard us talking about it)...but yeh, know what to do if it happens in future :whip :whip :thumb

[Ivan F Moala]

If you have a number of crashes, then as Well as getting rid of ghost instances of Excel via the Task manager, it would also pay to delete Tmp files from your Tmp Dir. Tmp files can also be the cause of further problems.

[johnske]

Thanx Ivan :thumb

[Dave]

Not sure if this helps but I found it an interesting read. Dave
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/index.htm

[Cyberdude]

I do too. Regretably I'm not skilled enough to fully understand it. But I have a feeling he has discovered the cause of our problem. I gotta print out that reference and cogitate over it.

[johnske]

Hi Dave,

Yes, I think that gets to the root of the problem alright, thanx for that, but you then have to consider whether this solution is more of a problem than the problem itself.

For instance, this particular problem only seems to creep in when there are installation/corruption problems with Office and even then it doesn't happen all the time. When it does happen, I found you only have to quit EXCEL.EXE to fix it. Most of the time this is not even an issue.

However prefixing everything with xlWB or xlApp is a constant issue that would arise EVERY time you wrote a piece of code, and, if you didn't have the required reference set - it would then be the source of another potential problem.

Of course this may be the way to go if you're mass-marketing something that you're selling for a zillion dollars, but for everyday use... :dunno

Just the same, it's interesting reading and something to bear in mind if you do intend to mass-market something for lotsa dosh.

Many thanx,
John :)

[Cyberdude]

For instance, this particular problem only seems to creep in when there are installation/corruption problems with Office and even then it doesn't happen all the time. When it does happen, I found you only have to quit EXCEL.EXE to fix it. Most of the time this is not even an issue.
A final comment: For several months this "error" was occurring in my system 2 or 3 times a day. I've talked to others who also have had it occur quite frequently. When you're busy, it gets old real fast to have to stop what you're doing and do a reboot to clear it. It can be more than an infrequent annoyance. I have spent a lot of time reducing the copious links that I use, and it really did seem to result in a significant reduction of "read-only" conditions. Don't know why for sure.

[Ivan F Moala]

Hi Dave,

Yes, I think that gets to the root of the problem alright, thanx for that, but you then have to consider whether this solution is more of a problem than the problem itself.

For instance, this particular problem only seems to creep in when there are installation/corruption problems with Office and even then it doesn't happen all the time. When it does happen, I found you only have to quit EXCEL.EXE to fix it. Most of the time this is not even an issue.

However prefixing everything with xlWB or xlApp is a constant issue that would arise EVERY time you wrote a piece of code, and, if you didn't have the required reference set - it would then be the source of another potential problem.

Of course this may be the way to go if you're mass-marketing something that you're selling for a zillion dollars, but for everyday use... :dunno

Just the same, it's interesting reading and something to bear in mind if you do intend to mass-market something for lotsa dosh.

Many thanx,
John :)


No, I think you have mis-understood what Tushar is on about.

What he is refering to is Automation via COM, automation and the now old ref to OLE are usually referred to
as COM (Component Object Model) and now DCOM - Distributed COM, or in its earlier days as ActiveX.
Simply put COM is the Model that allows us to interface between applications using different programming
languages. At its root level COM is a binary standard, lets not forget that at the root of any computer is its
processor and the registers, accumulators and the setting on/off of bits. It is an interface represented as
a pointer to an interface node. C programers know this as pointers to VTables.
That's a broad view..... what Tushar is refering to is Automating between Applications such as
Xl to XL, VB - XL, XL - Word, Word - Xl, Xl - Powerpt or any other server > Application, as long as it
supports COM.

To be COM complient it needs these basics (procedures @ Interface level)

1) A "user" (client) needs to be able to query the object to find out what it can support ? (QueryInterface)
Returns a pointer to a specified interface on an object to which a client currently holds an interface
pointer. This function must call IUnknown::AddRef on the pointer it returns.
2) Perform a task or group of tasks. "Add job" (AddRef )
3) Finish and clean up (Release) Decrements the reference count for the calling interface on an object.
If the reference count on the object falls to 0, the object is freed from memory. You see this in the task manager

COM defines first three functions of the IUnknown object as:
QueryInterface, AddRef and Release.
These are the 3 methods of the IUnknown interface. Very important for COM.
If you want to check this out then using the TypeLibInformation object (TLI), which is implemented in the
Tlbinf32.dll file shipped in the Visual Studio 6.0 and Visual Basic 6.0 CDs. The TLI object allows you
to programmatically extract information from type libraries. The Tlbinf32.dll file and the HTML Help file
are provided for your reference only, and they are not supported by Microsoft.

Download the dll and help file here;

http://download.microsoft.com/download/vstudio60pro/doc/1/WIN98/EN-US/Tlbinf32.exe

I have a working file some where ?? & Chip Pearson has done some great work on this
which is really where I started from.

So what does this really mean, look @ typical automation code
1 - Set reference to Object, usually via it's ProgID
2 - Do some data manipulation
3 - Set ref to Nothing
4 - Quit App

So when using COM it is important to FULLY qualify your references any unqualified statements
will create ghost references to the server and never be taken off the ROT (running Object Table) as
the ghost reference will increment the table reference and the ROT count for that Object will never
reach zero = taken off the Task manager.
If you look @ Tushars code the errant code has an unqualified reference .... this is the root cause.

Also have a look @ Mike_R's excellent tutorial - Automating Excel from VB 6.0 .. same principals apply
because as I mentioned earlier COM automation is not just for Xl.

http://www.xtremevbtalk.com/showthread.php?t=135815

So in your case John, it appears you are NOT automating between different instances of Excel
you don't need to go the Automation route.

[johnske]

Hi Ivan,

Yes, I most certainly DID misunderstand it,OOPS!I just went back to first that link and read it right through this time (the first time I only browsed quickly thru the code) and I see now there are references to automation in the text. I was much too hasty with my reply...Thanx for pointing that out.

Regards,
John