Another Excel11.xlb Question

[Jack Gillis]

I have run across another problem customizing the main Menu but found a
way to solve it. My questions are about understanding what happened.

Before I tried to customize the menu, there was no Excel11.xlb file in
my user/Application Data folder. (I am 99% sure of that.) It was in
XLSTART I think.

I made a change and exited Excel. Now there was new xlb file in my
user/Application Data folder. When I opened Excel again, the menu
reflected the change briefly and then reverted to the menu defined in
the XLSTART Excel11.xlb. My changed menu didn't 'stick.'

I removed the XLSTART/Excel11.xlb and restarted Excel. My customized
menu, the one in my user/Application Data, 'stuck' this time.

As I witness this, whenever I customize a menu the changes are made to
my user/Application xlb file if it exist or one is created if there
wasn't one in my user Application Data folder to begin with. Is this
correct?

And, if there is an xlb file in both my user/Application Data and
XLSTART which takes preference? It appears the XLSTART one does and
replaces the menu contained in my user/Application Data after displaying
it briefly. Is that correct?

Finally, I moved the user/Application Data xlb file to XLSTART thus
resulting in only one xlb file and that in XLSTART. The customized menu
appears just fine. Is that the way it should work?

Thank you for any help on this.

 

[Dave Peterson]

You described what happened to me when I had a *.xlb in my XLStart folder
(didn't clean up stuff after doing some experiments).

But I think I'd only have the *.xlb file in the:
"C:\Documents and Settings\username\Application Data\Microsoft\Excel"
folder.

If you make a change to your toolbars, I think you'll find that excel creates
one where it thinks it belongs. Then you'll end up with two versions again and
have to go through the same stuff to sort it out.

By the way, I keep a back up of that *.xlb in a nice safe spot. Then I can
experiment as much as I want and just put a fresh copy of the saved *.xlb file
right over the "experimented" version.

 

[Jack Gillis]

You described what happened to me when I had a *.xlb in my XLStart
folder
(didn't clean up stuff after doing some experiments).

But I think I'd only have the *.xlb file in the:
"C:\Documents and Settings\username\Application Data\Microsoft\Excel"
folder.

If you make a change to your toolbars, I think you'll find that excel
creates
one where it thinks it belongs. Then you'll end up with two versions
again and
have to go through the same stuff to sort it out.

By the way, I keep a back up of that *.xlb in a nice safe spot. Then
I can
experiment as much as I want and just put a fresh copy of the saved
*.xlb file
right over the "experimented" version.

Yes, yes and yes to all of the above. That is sort of the way I thought
things happened.

I have a small batch file that copies most ot the important, volatile
files on my system drive as well as My Documents (it contains all my
data) to a separate hard disk. I run it periodicallly and will add a
line to include username/Application Data/Microsoft/Excel.

Thank you very much.

 

[Dave Peterson]

You may want to compare your backup routine with David McRitchie's:
http://www.mvps.org/dmcritchie/excel/backup.htm

I have a small batch file that copies most ot the important, volatile
files on my system drive as well as My Documents (it contains all my
data) to a separate hard disk. I run it periodicallly and will add a
line to include username/Application Data/Microsoft/Excel.

<<snipped>>

 

[Gord Dibben]

Jack

To add to Dave's suggestions.

Keep the *.xlb out of the XLSTART folder and let Excel store it where it
wants.

The only files you should have in the XLSTART folder is your Personal.xls(or
similar, like an *.xla with your global macros) and a BOOK.XLT and SHEET.XLT
for default book and sheet.


Gord Dibben Excel MVP

You described what happened to me when I had a *.xlb in my XLStart
folder
(didn't clean up stuff after doing some experiments).

But I think I'd only have the *.xlb file in the:
"C:\Documents and Settings\username\Application Data\Microsoft\Excel"
folder.

If you make a change to your toolbars, I think you'll find that excel
creates
one where it thinks it belongs. Then you'll end up with two versions
again and
have to go through the same stuff to sort it out.

By the way, I keep a back up of that *.xlb in a nice safe spot. Then
I can
experiment as much as I want and just put a fresh copy of the saved
*.xlb file
right over the "experimented" version.

Yes, yes and yes to all of the above. That is sort of the way I thought
things happened.

I have a small batch file that copies most ot the important, volatile
files on my system drive as well as My Documents (it contains all my
data) to a separate hard disk. I run it periodicallly and will add a
line to include username/Application Data/Microsoft/Excel.

Thank you very much.

 

[Jack Gillis]

Than you Gord. I realize that now.

However, you have exposed another hole in my Excel knowledge and
experience. How do the global macros included in the xla file you
mentioned differ from those macros in the Personal.xls file? Maybe I
don't understand what a global macro is?

Perhaps this should be the beginning of a new thread. If so, I will
start one upon your reply.

Thanks again.

Jack

To add to Dave's suggestions.

Keep the *.xlb out of the XLSTART folder and let Excel store it where
it
wants.

The only files you should have in the XLSTART folder is your
Personal.xls(or
similar, like an *.xla with your global macros) and a BOOK.XLT and
SHEET.XLT
for default book and sheet.


Gord Dibben Excel MVP

 

[Dave Peterson]

They don't differ by much. Many people have their own personal macros in
personal.xl* (.xls or .xla).

But if there are routines that need to be shared with others, those routines
could be separated into different files and those files could be shared.

And I've seen others suggest that they put all their UserDefinedFunctions into a
different workbook (*.xla) and keep the sub's in personal.xls.

Than you Gord. I realize that now.

However, you have exposed another hole in my Excel knowledge and
experience. How do the global macros included in the xla file you
mentioned differ from those macros in the Personal.xls file? Maybe I
don't understand what a global macro is?

Perhaps this should be the beginning of a new thread. If so, I will
start one upon your reply.

Thanks again.

 

[Jack Gillis]

They don't differ by much. Many people have their own personal macros
in
personal.xl* (.xls or .xla).

But if there are routines that need to be shared with others, those
routines
could be separated into different files and those files could be
shared.

And I've seen others suggest that they put all their
UserDefinedFunctions into a
different workbook (*.xla) and keep the sub's in personal.xls.

Thank you.

 

[Gord Dibben]

Jack

Another point about using the add-in.

You don't have to call the macros by using the workbook name.

i.e. To assign a macro from Personal.xls to a button you must use the path

Personal.xls!macroname.

If macro is in an add-in macroname is sufficient.

Downside to an add-in is...the macros don't show up in Tools>Macro>Macros so

you have to type the name in.


Gord

They don't differ by much. Many people have their own personal macros
in
personal.xl* (.xls or .xla).

But if there are routines that need to be shared with others, those
routines
could be separated into different files and those files could be
shared.

And I've seen others suggest that they put all their
UserDefinedFunctions into a
different workbook (*.xla) and keep the sub's in personal.xls.

Thank you.

 

[Jack Gillis]

Good points. Thank you

Jack

Another point about using the add-in.

You don't have to call the macros by using the workbook name.

i.e. To assign a macro from Personal.xls to a button you must use the
path

Personal.xls!macroname.

If macro is in an add-in macroname is sufficient.

Downside to an add-in is...the macros don't show up in
Tools>Macro>Macros so

you have to type the name in.


Gord