[RESOLVED] Excel: Can a file be "in use" without being "Open"

[maurice_whittaker]

I am getting Error 75 Path/File access error, (file in use already I think), when I try to rename it (with coding). I am fairly sure that the file (Workbook) is closed. I read that the error would be number 55 if the file were open. The only person using the computer is me through the VBA program that has caused me so much trouble to date. When my program closes the file it disappears from the Status line as one would expect. I was under the impression that if a W/Book was being used by the master W/Book that it would appear as a "Grouped" file, but it does not appear on the Status line. Do I need to de-activate the file as well as close it? This is somewhat intermittent as the rename is sometimes successful but I haven't quite sorted out when and why. Any suggestions would be gratefully received.

[Spoo]

MW

I see that you closed the prior thread and that
this one seems to be what you referenced therein.

I'm not sure I know enough about this "new" issue
to be of much help, but perhaps you could start
by posting a small code snippet that includes the
line that produces the error.

Spoo

[westconn1]

Do I need to de-activate the file as well as close it?

it may be that you still have some reference to an object of the workbook which is holding it as open

as your code is not posted here it is hard to make suggestions

[maurice_whittaker]

Sorry, I will type that all again - this system keeps kicking me off but not until I submit my reply. Thanks Pete and Spoo. It will be short this time to avoid being ejected. I will try Pete's suggestion and if I remember correctly, all I have to do is to set the object to "nothing". Can either of you suggest a way to show a form which self-destructs after a short time? I beleive the form has to have a timer on it but my toolbox does not show one. Sorry about the rush. Thanks

[westconn1]

no timer in vba, try application.ontme

[maurice_whittaker]

Thanks Pete, I think I have news! I searched through for any objects not reset and the only one I found was in the sub for getting the "Extent" of a sheet which you were involved in refining if I remember correctly. I reset that to nothing but it was totally unrelated to my problem I think. One thing I did find was that when I had copied sheets from the W/Book causing the problem, I closed it without saving it because (in my innocence) I thought that copying from a book did not change it therefore it did not need it. Since I put a save in it seems to have solved the problem BUT I need to be convinced that this was the problem. I truly hope so! Whilst going through this, I removed "End" from my program as I understand it is not a good thing to have BUT I got terrible problems with "Run Over" into other subs and I had to re-instate it. If you will bear with me for a while I will leave this unmarked as resolved until I am sure. Thank you, once again, for your help and I will try "ontme" Tomorrow.

[maurice_whittaker]

Hi Folks, I have now re-run the prog. with the "Save" removed and I have succeeded in reproducing the problem! Needless to say, I put it back in very smartly! I will now mark the thread resolved and thanks again. My version is "Microsoft Exceel Version 12.0 running on Windows (32 bit) NT 5.01". I thought I had XP! This version does not have "ontme". Have you any tips on how to do away with "End". To be honest it does not cause any problems that I can see but it is apparently bad practice.
Maurice

[westconn1]

Have you any tips on how to do away with "End"

depends where you are using it
exit sub if you are running a single procedure, or the code will finish at the end of that procedure
unload me if you are working with a user form

This version does not have "ontme

i have never looked a this version, but i find that a bit surprising, you may have to build your own timer, but it may appear to hang excel while it is working

I have now re-run the prog. with the "Save" removed and I have succeeded in reproducing the problem! Needless to say, I put it back in very smartly!

you can probably close without saving, providing you specifically tell it to do so

[maurice_whittaker]

Thanks Pete, I am feeling much more relaxed now and I have made quite good progress since the awful period I've just been through!
TTFN, Maurice.

[nO_OnE]

Just some extra info, but in windows 7, if you have the "Preview Pane" option turned on, and try to open a file, such as a spreadsheet, it opens the file to provide a preview. I have found this to be quite problematic, for some files it shows that the files are open and gives the users errors and it opens the file as read only. It doesn't seem to do it all the time, but it is a royal pain...

I have just advised everyone to leave it off.