Hi everyone,

I use Excel 2000 and have specified the "Alternate file startup location"
(Tools/Options - General tab) for some alternate working templates. Are they
all loaded in memory when Excel starts or are they not? To me this is a major
problem because there are many of them and I wonder if a part of memory is
used by them and therefore if I should try to make them smaller.

When I use Excel XP (2002) and I specify that folder (At startup, open all
files in:), I get a message for each of them asking if I should enable the
macros contained in the templates. This is annoying as I have to click about
45 times on the Enable macros button. This message, I think, should come only
when I try to create a new workbook base on a cpecified template. Is there
another way in Excel 2002 to allow the creation of a new workbook based on an
additional list of specified templates for a defined group of users on a
network.

Thanks to anyone who can help.
--
Jac Tremblay

I've never seen anyone really use that "at startup, open all files in"
effectively.

But you can specify a network template location in MSWord:

In MSWord:
Tools|Options|General Tab|Workgroup templates

Or if you've loaded the MSOffice shortcutbar, you can use that:
rightclick on an empty spot of that toolbar, choose Customize
It's on the Settings Tab under "workgroup templates location"

Jac Tremblay wrote:

Hi everyone,

I use Excel 2000 and have specified the "Alternate file startup location"
(Tools/Options - General tab) for some alternate working templates. Are they
all loaded in memory when Excel starts or are they not? To me this is a major
problem because there are many of them and I wonder if a part of memory is
used by them and therefore if I should try to make them smaller.

When I use Excel XP (2002) and I specify that folder (At startup, open all
files in:), I get a message for each of them asking if I should enable the
macros contained in the templates. This is annoying as I have to click about
45 times on the Enable macros button. This message, I think, should come only
when I try to create a new workbook base on a cpecified template. Is there
another way in Excel 2002 to allow the creation of a new workbook based on an
additional list of specified templates for a defined group of users on a
network.

Thanks to anyone who can help.
--
Jac Tremblay

--

Dave Peterson

Hi Dave,

I know that is a special problem. But in the organization where I work,
everyone is using that "Alternate file location" where over 50 templates and
XLAs are stored and must be used for their job. This location is on the
network and appears to be quite useful because everyone gets access to the
work templates from a central location. But at the same time, this causes a
particular problem. The users have been experiencing a performance problem
and I suspect that the memory used by this loading of all those templates may
be one of the causes.

I am looking for a solution to this problem. I will eventually choose the
best I find, but not necessarily the cheapest nor the easiest.

I figure that if I do not specify that alternate file location, I must
develop a UI that will present the templates to the user and permit the
creation of a new workbook. For the XLAs, I must find the places in the code
where they are used an adapt it to match the new location. If all those
templates are not loaded in memory, will that make the situation better?
Before I start developping anything, I would like to be sure that this is a
good solution.

I remember some time ago, when I had specified that Alternate file location
with Excel 95 (or was it 97?), all the recently used workbooks were opened in
addition to the ones in the specified directory. To fix the problem, I had to
blank it out. I never used that functionality again. But now, I am confronted
with it another time and I'm determined to find a smart solution.

I know that Word does not work the same way... But that's another topic...

What does Microsoft has to say about that Excel alternate file location? How
is it supposed to be used ou be user for? Where can I find information about
it?

To conclude, Dave, I will have to rate your comment as not useful for now
because I must get other opinions from the Excel community. But in fact, your
comment has been very helpful and I really appreciate it. Your answer means
that my problem is a real one and that it must be addressed seriously. I am
sure that everyone will profit from that questioning.

Thank you again, Dave.

"Dave Peterson" wrote:

I've never seen anyone really use that "at startup, open all files in"
effectively.

But you can specify a network template location in MSWord:

In MSWord:
Tools|Options|General Tab|Workgroup templates

Or if you've loaded the MSOffice shortcutbar, you can use that:
rightclick on an empty spot of that toolbar, choose Customize
It's on the Settings Tab under "workgroup templates location"

Jac Tremblay wrote:

Hi everyone,

I use Excel 2000 and have specified the "Alternate file startup location"
(Tools/Options - General tab) for some alternate working templates. Are they
all loaded in memory when Excel starts or are they not? To me this is a major
problem because there are many of them and I wonder if a part of memory is
used by them and therefore if I should try to make them smaller.

When I use Excel XP (2002) and I specify that folder (At startup, open all
files in:), I get a message for each of them asking if I should enable the
macros contained in the templates. This is annoying as I have to click about
45 times on the Enable macros button. This message, I think, should come only
when I try to create a new workbook base on a cpecified template. Is there
another way in Excel 2002 to allow the creation of a new workbook based on an
additional list of specified templates for a defined group of users on a
network.

Thanks to anyone who can help.
--
Jac Tremblay

--

Dave Peterson

Hi Jac,

What does Microsoft has to say about that Excel alternate file location? How
is it supposed to be used for? Where can I find information about it?

Many companies require Excel to start with a given set of files always loaded -
such as addins to provide formatting tools to help ensure a corporate standard,
ISO 9001 compliance, etc. The network admin can point that folder to a network
location and therefore have a single copy of the addin to maintain instead of
distributing it to all users.

I know that Word does not work the same way... But that's another topic...

I think you were too hasty to dismiss Dave's information. Although Excel does
not have a way to set the 'Workgroup Template' folder, it *does* respond to it
being set in Word! So if you go to Word and set the 'Workgroup Template' folder
to your network location, any Excel templates you put in that folder will show
up in Excel's File New list of templates.

FWIW, the path is actually stored in:

HKEY_CURRENT_USER\Software\Microsoft\Office\N.0\Co mmon\General\SharedTemplates

where N is the office version (Excel 2000 and above, Excel 2000=9.0, 2002=10.0,
2003=11.0)

For Excel 97, the folder is stored in the (Default) entry at:
HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Co mmon\FileNew\SharedTemplates

In each case, you can create the key/value if it doesn't exist.

Furthermore, any folders you create below that level will be displayed as tabs
at the top of the File New dialog, with the folder name used as the caption
for the tab and any xlt's in the folder displaying when that tab is clicked.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Hi Stephen,

The information you provide about the use of the "Alternate file location"
is quite informative. I wander why I could not find that information in Excel
Help.

What I understand from your comment is that it should be used to store
addins for common use. Addins are by nature only VBA code and therefore are
small in size. But as far as I know, this file location is not the place to
store templates, particularly if they are big (or huge) and numerous...

Templates should be stored in a network folder that can to be set from Word:
Tools|Options|File location Tab|Workgroup templates (not the General Tab).

When I first read Dave's comment on how to set a workgroup template in Word,
I did not understand that it would set Excel's templates folder. You opened
my eyes on that point. I'm glad this happened because it provided the
community with your very pertinent comment on the "Alternate file location"
and a very informative supplement on the workgroup template brought by Dave.

Thank you very much to both of you.

"Stephen Bullen" wrote:

Hi Jac,

What does Microsoft has to say about that Excel alternate file location? How
is it supposed to be used for? Where can I find information about it?

Many companies require Excel to start with a given set of files always loaded -
such as addins to provide formatting tools to help ensure a corporate standard,
ISO 9001 compliance, etc. The network admin can point that folder to a network
location and therefore have a single copy of the addin to maintain instead of
distributing it to all users.

I know that Word does not work the same way... But that's another topic...

I think you were too hasty to dismiss Dave's information. Although Excel does
not have a way to set the 'Workgroup Template' folder, it *does* respond to it
being set in Word! So if you go to Word and set the 'Workgroup Template' folder
to your network location, any Excel templates you put in that folder will show
up in Excel's File New list of templates.

FWIW, the path is actually stored in:

HKEY_CURRENT_USER\Software\Microsoft\Office\N.0\Co mmon\General\SharedTemplates

where N is the office version (Excel 2000 and above, Excel 2000=9.0, 2002=10.0,
2003=11.0)

For Excel 97, the folder is stored in the (Default) entry at:
HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Co mmon\FileNew\SharedTemplates

In each case, you can create the key/value if it doesn't exist.

Furthermore, any folders you create below that level will be displayed as tabs
at the top of the File New dialog, with the folder name used as the caption
for the tab and any xlt's in the folder displaying when that tab is clicked.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Hi Jac,

The information you provide about the use of the "Alternate file location"
is quite informative. I wander why I could not find that information in Excel
Help.

I couldn't possibly comment <g

What I understand from your comment is that it should be used to store
addins for common use. Addins are by nature only VBA code and therefore are
small in size. But as far as I know, this file location is not the place to
store templates, particularly if they are big (or huge) and numerous...

Correct

Templates should be stored in a network folder that can to be set from Word:
Tools|Options|File location Tab|Workgroup templates (not the General Tab).

Correct again.

When I first read Dave's comment on how to set a workgroup template in Word,
I did not understand that it would set Excel's templates folder.

I doesn't necessarily 'set' Excel's templates folder. It's more that there are a
number of folders that Excel looks in for templates and that is one of them

You opened
my eyes on that point. I'm glad this happened because it provided the
community with your very pertinent comment on the "Alternate file location"
and a very informative supplement on the workgroup template brought by Dave.

Thank you very much to both of you.

You're welcome; that's why we're here.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

This is very interesting and the behaviour in using the Word Template
location is different to using the XLSTART folder. The Word Template folder
displays the Excel extension and the XLSTART folder does not.

This may be the basis of my issue that the Templates in the XLSTART folder
will not open but do if they are in the Word Template folder. (per "Template
fails to Open" Thread)

"Stephen Bullen" wrote:

Hi Jac,

The information you provide about the use of the "Alternate file location"
is quite informative. I wander why I could not find that information in Excel
Help.

I couldn't possibly comment <g

What I understand from your comment is that it should be used to store
addins for common use. Addins are by nature only VBA code and therefore are
small in size. But as far as I know, this file location is not the place to
store templates, particularly if they are big (or huge) and numerous...

Correct

Templates should be stored in a network folder that can to be set from Word:
Tools|Options|File location Tab|Workgroup templates (not the General Tab).

Correct again.

When I first read Dave's comment on how to set a workgroup template in Word,
I did not understand that it would set Excel's templates folder.

I doesn't necessarily 'set' Excel's templates folder. It's more that there are a
number of folders that Excel looks in for templates and that is one of them

You opened
my eyes on that point. I'm glad this happened because it provided the
community with your very pertinent comment on the "Alternate file location"
and a very informative supplement on the workgroup template brought by Dave.

Thank you very much to both of you.

You're welcome; that's why we're here.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk