EXCEL Macro toolbars disappeared, personal doesn't open, etc.

[Rosa59]

I work on Windows Server 2003 standard edition. After over a year of work,
SUDDENLY my customized toolbars with all my EXCEL macros assigned
disappeared. I finally found the macro files that had also disappeared, but I
can't get it to open with the excel automatically (the path doesn't save in
the General tab of options ("at startup open all..."). I open it manually and
then can see my list of macros but I can't get the macro to save when I
assign it to a new button. Essentially, I have the macros but can't assign
them -BOTH IN THE CASE OF EXISTING MACROS AND WHEN I RECORD NEW ONES.
I have discovered that the well meaning staff at IT just don't know how to
help me.
Any suggestions as to how to assign or where to find my old toolbars?

 

[Dave Peterson]

If you customized the toolbars manually (through tools|customize), then this
toolbar will exist in a file with an extension of *.xlb. The version of windows
isn't important to the name of the file, but it may be for the location.

I use xl2003 and WinXP Home and mine is named: excel11.xlb
and is located here:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel

You can use windows start button|search to look for this file. But find them
all! They can be in hidden folders. And the last one that excel opens is the
one that excel uses. But excel will save back to to the "real" location.

I like to make a minor modification to my toolbar, close excel (to save the
*.xlb file), then search for all the *.xlb files.

The one that that was updated is in the "real" location. Then I'll find the
toolbar that I want (which may not be in that real location and put it there.

You may want to move all the *.xlb files to a safe out of the way location and
do some experimenting to see if you can save the toolbar that you have. Put one
in that "real" location and open excel. If it's what you want, delete the
others and save a copy for back up.

On the other hand, you may just want to delete all the *.xlb files and recreate
the toolbar from scratch. I'd still make a backup of that *.xlb file when
you're done. If you screw something up, just delete the messed up version and
copy the backup into that "real" location.


=====
As for the macros, if you're using a file called personal.xls (or personal.xla),
you should be able to use windows search to find it to:

Mine is under:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

This file shouldn't be in the folder in the "at startup, open...". In fact,
most people I know don't use this option at all.

If you want macros to always be available, I would either put them in the
XLStart folder or make them an add (*.xla) by file|saveas and choosing addin.

Then use Tools|addins to install the addin.

And this is just a guess...Some versions of excel (xl2002+, IIRC), have a way to
quarantine files that it thinks are corrupted. Next time you open excel and the
macros aren't there (and you can't save to them), try:

Help|about MS Excel|Disabled items

If you see your workbook's name there, re-enable it. But if that file is really
corrupted, you'll want to delete that version and copy your backup version to
your XLStart folder. (Saving backups is a good idea!)

====
ps.

I find it much easier to modify the toolbar to support the macros via code.
It'll work easier when/if I share the file -- or even just move the file.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

=============
pps.

One quick way to get to your "application data" folder:
Windows Start button|run
type:
%appdata%
and hit enter.

Then continue traversing down the folders.

 

[Rosa59]

THANK YOU SO MUCH.
I reviewed everything you just taught me and understand so much more now.
I tried everything you mentioned regarding the macros step by step and the
final real answer was in the disabled files. I found my original PERSONAL
macros workbook and another file excel started saving macros in called
acc78770 though it was not clear why. Do you have a suggestion for what that
reason might have been? How can I consolidate?
Some of the macros are saved in that new file. Now that I have enabled it, I
have to find it to put in excelstart, so that all of the myriad buttons which
have gratefully reappeared on my toolbar will work. You have been SO
EXTREMELY HELPFUL. I am not a programmer, just a sophisticated user so I
don't know how to write things myself. I will continue to record my
keystrokes and hope that things stay put.
THANK YOU

 

[Dave Peterson]

When xl2003 and older saves the file, it saves it as a temporary file with a
funny name (8 characters--no extension) in the same folder.

If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.

Common things that get blamed for interruptions to this process are antivirus
software poking its head in or network errors--either permissions or physical
problems.

==========

If you have macros that you want to keep that exist in separate workbooks,
you'll have to copy the code (or module) from one workbook to the other.

There's no mechanized routine (that I know of) that will inspect each workbook's
project and look for differences--and keep the one you want(!).

 

[Rosa59]

OK. That could definitely explain it. I will need to find the additional
funny file and keep it in the startup, as well I suppose, since some macros
are there. The worst thing that could happen would be to rerecord those
macros, but I am so far ahead of the game now, thanks to you, that it is
almost painless.