personal.xls "already open"

[Michael Fisher]

I've seen this topic before, and tried to implement the ideas people have
suggested. The situation is that I have Excel 2003 (XP) on Windows 2000.
I have a set of macros in personal.xls, most of which have been tied to
toolbar buttons. I just reworked two of them down into one new macro.
While writing the macros, I attached the "key" one to a button. It worked
just fine. When I finished the work, I deleted the new button and
reassigned an old button to the new macro. Suddenly I'm getting the
"already open" error. I have deleted ALL file and shortcut references to
personal.xls from my C: drive. I have several copies of old
personal.xls's saved under different names on a separate network drive.
What's weird is that the macro runs fine if I select it from the
Tools/Macro/Run menu or if I start it from the VBA editor. Initially,
other old custom buttons worked fine. I've tried changing the filename,
moving it, recopying it to each of the XLSTART directories (including
recording a short macro in it after "reactivating" it), hard and cold
reboots along the way, running excel /regserver -- in fact, pretty much
everything I could find anyone posting as a solution. NO HELP. I even
managed to kill the other custom buttons. I've even tried copying the
..xlb the same way.

So, does anyone out there have any further ideas?

 

[David McRitchie]

Hi Michael,
It means that you are opening Excel twice. Actual the
message I get is -- but I think it amounts to the same thing.

File in Use
PERSONAL.XLS is locked for editing
by 'username....'.
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.
[Read Only] [Notify] [Cancel] appear on right side

Your toolbars may be at risk, if you are not careful
and they are different on the two Excel windows.

Open additional workbooks from within Excel (file, open)

I haven't had a problem with toolbars in long time, but I
am careful to watch out for it.

If you see your toolbars on one of them but not on the other
close the Excel without your customized toolbars, then
close the one with your customized toolbars. If you
closed the wrong one, but did not close Excel yet you can
restore your toolbars by using a search on *.xlb and
opening that file with Excel open. If you closed out of
Excel it is too late because the toolbars file gets save
every time that Excel closes.

My options are
Tools, View (tab), (unchecked) Windows on Taskbar
-- option displays each open workbook on Windows Taskbar
Tools, General(tab), (unchecked) Ignore other applications
-- option prevents exchange of data applications that use DDE.

Backing up: http://www.mvps.org/dmcritchie/excel/backup.htm
Toolbars: http://www.mvps.org/dmcritchie/excel/toolbars.htm

 

[Michael Fisher]

Thanks for the response; I haven't had any toolbar problems in a while,
either, so this one really caught me off guard. I must not have explained
things well, because you missed the point. Still, I got some ideas from
your websites. First off, I KNOW I only have one instance of Excel
running, so pretty much all of your reply didn't help. Personal.xls was
NOT locked for editing -- I'd been editing it when the problem occurred.
The error seems to be one you cover on your site -- the appearance, upon
clicking on a custom button, of an error dialog informing me that a file
named personal.xls is already open and I can't have two files with the
same name open.

Thanks to your site and some other newsgroup postings, I found that the
custom toolbar button is pointing to a nonexistent file (named, of course,
personal.xls). It appears that when I assign a macro to the button, it is
being pointed to the default file directory. Changing the directory
didn't help -- it just changed the pointer. I tried your repair macros
with, well, mixed success. I managed to change the path, but it gets
confused between the the file that started with Excel and the SAME file
it's trying to re-open with the button -- that is,
C:\--\XLSTART\personal.xls loads at startup, but the button tries to open
it again. The key was that the macro worked fine from menu or from the
VBE. Putting personal.xls in the Documents & Setting subfolder didn't
help, either.

So now, the problem seems to be how to convince Excel XP/SP3 to NOT grab
the default file path as the path to the personal.xls. Alternatively, I
need to convince Excel to recognize personal.xls in either of the XLSTART
directories correctly. In the interim, I stuck personal.xls in the default
directory so at least the buttons work. I may have to assign a
certificate to it to kill the Disable/Enable dialog.

Any other ideas?

Hi Michael,
It means that you are opening Excel twice. Actual the
message I get is -- but I think it amounts to the same thing.

File in Use
PERSONAL.XLS is locked for editing
by 'username....'.
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.
[Read Only] [Notify] [Cancel] appear on right side

Your toolbars may be at risk, if you are not careful
and they are different on the two Excel windows.

Open additional workbooks from within Excel (file, open)

I haven't had a problem with toolbars in long time, but I
am careful to watch out for it.

If you see your toolbars on one of them but not on the other
close the Excel without your customized toolbars, then
close the one with your customized toolbars. If you
closed the wrong one, but did not close Excel yet you can
restore your toolbars by using a search on *.xlb and
opening that file with Excel open. If you closed out of
Excel it is too late because the toolbars file gets save
every time that Excel closes.

 

[David McRitchie]

Hi Michael,
Did you restore your toolbar files to a different location, that can
cause problems, non-existent location which you mentioned and
which I think is mentioned on my barhopper.htm page because
that is how I fixed them. individually I could reassign the button
to personal.xls again which overrides the other location, but
using a macro allowed me to change all of them.

Thanks for the response; I haven't had any toolbar problems in a while,
either, so this one really caught me off guard. I must not have explained
things well, because you missed the point. Still, I got some ideas from
your websites. First off, I KNOW I only have one instance of Excel
running, so pretty much all of your reply didn't help. Personal.xls was
NOT locked for editing -- I'd been editing it when the problem occurred.
The error seems to be one you cover on your site -- the appearance, upon
clicking on a custom button, of an error dialog informing me that a file
named personal.xls is already open and I can't have two files with the
same name open.

Thanks to your site and some other newsgroup postings, I found that the
custom toolbar button is pointing to a nonexistent file (named, of course,
personal.xls). It appears that when I assign a macro to the button, it is
being pointed to the default file directory. Changing the directory
didn't help -- it just changed the pointer. I tried your repair macros
with, well, mixed success. I managed to change the path, but it gets
confused between the the file that started with Excel and the SAME file
it's trying to re-open with the button -- that is,
C:\--\XLSTART\personal.xls loads at startup, but the button tries to open
it again. The key was that the macro worked fine from menu or from the
VBE. Putting personal.xls in the Documents & Setting subfolder didn't
help, either.

So now, the problem seems to be how to convince Excel XP/SP3 to NOT grab
the default file path as the path to the personal.xls. Alternatively, I
need to convince Excel to recognize personal.xls in either of the XLSTART
directories correctly. In the interim, I stuck personal.xls in the default
directory so at least the buttons work. I may have to assign a
certificate to it to kill the Disable/Enable dialog.

Any other ideas?

Hi Michael,
It means that you are opening Excel twice. Actual the
message I get is -- but I think it amounts to the same thing.

File in Use
PERSONAL.XLS is locked for editing
by 'username....'.
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.
[Read Only] [Notify] [Cancel] appear on right side

Your toolbars may be at risk, if you are not careful
and they are different on the two Excel windows.

Open additional workbooks from within Excel (file, open)

I haven't had a problem with toolbars in long time, but I
am careful to watch out for it.

If you see your toolbars on one of them but not on the other
close the Excel without your customized toolbars, then
close the one with your customized toolbars. If you
closed the wrong one, but did not close Excel yet you can
restore your toolbars by using a search on *.xlb and
opening that file with Excel open. If you closed out of
Excel it is too late because the toolbars file gets save
every time that Excel closes.

 

[Michael Fisher]

As before, thanks for the response. I restored the xlb to where I found
it, also the personal.xls (before I moved it to the location Excel keeps
pointing to). I even went so far as to completely remove all the xlb's
and start from scratch (to guarantee that the xlb winds up in the file
Excel wants it to be in). Since the whole problem stems from the way
Excel is assigning the personal.xls path, I haven't tried to either move
it back to XLSTART or reassign the buttons. Could there be a registry
error which is making Excel assign the default path
(Tools/Options/General/Default File Location) to every button assignment.
It's all really weird, since it worked perfectly for three years. The
problem only started when I rewrote an existing macro, deleted the button
I'd been using for testing and reassigned the original button.

Oh, the cit.ctu.edu.vn link on your website is dead.

 

[David McRitchie]

Hi Michael,
Look at the individual button to see if the link is actually good.
The purpose of the barhopper / barhopping macros is to
repair the buttons by removing the pathname and reentering.
So you would not see the actual pathname that the button
has on your listing.

I marked the link as "dead link", I doubt that it would make any
difference to using information. You will find the code for
both macros at.
http://www.mvps.org/dmcritchie/excel/code/barhopping.txt
You would find by looking for the word code
and most of my web pages with code (at least the newer ones)
have a corresponding .txt file in the code subdirectory
with the macro.

 

[Michael Fisher]

I tried it all. FINALLY, I got lucky. Unfortunately, not one of your
suggestions, though. Along the way, I created a new personal.xls in
the Win2000/Excel 2002 default folder and copied the original over it
(Documents & ...) -- which didn't help. After that, I deleted the
problem buttons (which were still problems -- pointing to the Excel
default file save directory. Then, in desperation, I unhid
personal.xls, and redid the buttons on the unhidden personal.xls.
They worked. I then saved the personal.xls to the Documents &
Settings XLSTART and closed Excel. I then restarted Excel and hid
personal.xls, exited Excel again (saving personal.xls on the way out)
and restarted a second time. The buttons still worked, and worked the
rest of the day.

Why would Excel force the default FILE SAVE path on toolbar button
macro assignments? Why should unhiding personal.xls make any
difference?

Hi Michael,
Look at the individual button to see if the link is actually good.
The purpose of the barhopper / barhopping macros is to
repair the buttons by removing the pathname and reentering.
So you would not see the actual pathname that the button
has on your listing.

I marked the link as "dead link", I doubt that it would make any
difference to using information. You will find the code for
both macros at.
http://www.mvps.org/dmcritchie/excel/code/barhopping.txt
You would find by looking for the word code
and most of my web pages with code (at least the newer ones)
have a corresponding .txt file in the code subdirectory
with the macro.

As before, thanks for the response. I restored the xlb to where I found
it, also the personal.xls (before I moved it to the location Excel keeps
pointing to). I even went so far as to completely remove all the xlb's [clipped]

 

[David McRitchie]

Hi Michael,
I think that suggestion is found between reading both toolbars.htm
and barhopper.htm but obviously not clear enough. The pathname
is part of the buttons (and menus) -- even though you can only have
one file open with the filename -- and that conflict is the basis for
the message file already open. My suggestion for using the barhopper macros
is to save time if you have a lot of menus and toolbar buttons, but
it is customized for my use, my filenames (which is not personal.xls),
my menu names (i.e. My Tools), and the toolbars my buttons are in.

Definitely stated in the toolbars.htm that if you are having trouble
with toolbar assignment to unhide and only have your personal.xls
file open -- assuming that it will be the first file opened. I don't know
why because the toolbars are in their own separate file.

I tried it all. FINALLY, I got lucky. Unfortunately, not one of your
suggestions, though. Along the way, I created a new personal.xls in
the Win2000/Excel 2002 default folder and copied the original over it
(Documents & ...) -- which didn't help. After that, I deleted the
problem buttons (which were still problems -- pointing to the Excel
default file save directory. Then, in desperation, I unhid
personal.xls, and redid the buttons on the unhidden personal.xls.
They worked. I then saved the personal.xls to the Documents &
Settings XLSTART and closed Excel. I then restarted Excel and hid
personal.xls, exited Excel again (saving personal.xls on the way out)
and restarted a second time. The buttons still worked, and worked the
rest of the day.

Why would Excel force the default FILE SAVE path on toolbar button
macro assignments? Why should unhiding personal.xls make any
difference?

Hi Michael,
Look at the individual button to see if the link is actually good.
The purpose of the barhopper / barhopping macros is to
repair the buttons by removing the pathname and reentering.
So you would not see the actual pathname that the button
has on your listing.

I marked the link as "dead link", I doubt that it would make any
difference to using information. You will find the code for
both macros at.
http://www.mvps.org/dmcritchie/excel/code/barhopping.txt
You would find by looking for the word code
and most of my web pages with code (at least the newer ones)
have a corresponding .txt file in the code subdirectory
with the macro.

As before, thanks for the response. I restored the xlb to where I found
it, also the personal.xls (before I moved it to the location Excel keeps
pointing to). I even went so far as to completely remove all the xlb's [clipped]