how do I make changes to the default Excel template?

[Polly]

I don't want to create a new template - just make minor adjustments to the
default template, eg landscape rather than portrait, printing with gridlines,
etc.

 

[Sheeloo]

Update Book.xlt or Sheet.xlt (available in XLStart folder)

The XLStart folder is usually located at:

C:\Program Files\Microsoft Office\Office11\XLStart

For more details visit
http://office.microsoft.com/en-us/excel/HP052292861033.aspx

 

[Gord Dibben]

Open a new workbook. Customize as you wish.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name
your workbook "BOOK"(no quotes). Excel will add the .XLT to save as
BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button
File>New or CTRL + n

WARNING................Do not use File>New...Blank Workbook or you will get
the Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben MS Excel MVP

 

[Gord Dibben]

I doubt very much if Polly has these in her XLSTART folder.

They have to be created first.

See my reply to Polly.


Gord

 

[Sheeloo]

You are right Gord...

I did not think of that...

 

[GregNga]

If you create macros that you want to make available to all documents, do you
create a default template as described in this thread and store them athere
or is there another way

 

[Bob Phillips]

No, either store them in Personal.xls, or create an addin.

 

[GregNga]

Where should Personal.xls be stored?

So, are you saying that default attributes such as landscape rather than
portrait, printing with gridlines, etc as indicated in the original question
in this thread by Polly should be stored in BOOK.xlt or SHEET.xlt (according
to reply by Gord Dibben) while to make macros available to all documents,
they should be stored in PERSONAL.xls

 

[Gord Dibben]

To create a Personal.xls file go to Tools>Macros>Record New Macro and "Store
Macro In". Select Personal Macro Workbook.

Record some simple steps like copy/paste some data.

Your Personal.xls will be created and when saved will be stored in the
default Office location.

Good idea to Save Personal.xls at this point. You will find it under
Window>Unhide

Save it and Excel will point it to the proper location.

Generally this is the location.

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

Now, if you have existing macros in other workbooks, you can copy them into
a module or modules of Personal.xls by hitting Alt + F11 to go to Visual
Basic Editor, CTRL + r to open Project Explorer.

Select Personal.xls and you can open the module1 containing your recently
recorded macro.

Add macros by copying them from other books(assuming you have some)

Go back to Excel Window by Alt + q

Window>Hide your Personal.xls again then close Excel.

You will be given a message asking to save changes to Personal.xls

Click yes so it opens hidden when next you start Excel

Your macros will be available to all open workbooks from now on.

It will be up to you to devise a method of running them from a button,
shortcut key or by just running from Tools>Macro>Macros list.

Note: recorded macros must be edited to run on ActiveWorkbook and
ActiveSheet rather than hard-coded names



Gord

 

[Bob Phillips]

I was answering your question as where to store macros. Template format
changes should be stored in a template, whether that be a custom template,
or the default workbook, Book.xlt.

 

[GregNga]

Thanks

Does this apply both to Excel 2000 as well as Wxcel 2003. I have the former
at work and the lattter at home.

 

[Gord Dibben]

Applies to all versions of Excel................I don't run 2007 so can't
say for sure about that.


Gord

 

[GregNga]

Gord

I noticed that when I create a PERSONAL.XLS file as you described, macros
are not always available to other documents. If PERSONAL is open, then they
are or if I open a document by double clicking on it in Windows Explorer.
However, if I start Excel (which now opens PERSONAL because it's in XLSTART)
and then close PERSONAL and then go to the FILE menu and open a document, the
macros in PERSONAL are not available to the document I just opened. Also is
it possible to have macros available to all documents without having the new
document created be named PERSONAL (previously, it was BOOK1 for a new
document). It looks like essentially what happens is that instead of creating
a new document when Excel starts, opens PERSONAL and you have to save it
under a another name if you want a new document.

This is in Excel 2000, I don't know about 2003


This may not be much of an issue

 

[Gord Dibben]

Why would you close Personal.xls after Excel starts up?

Because you can see it and don't want to?

Just hide it under Window>Hide as I explained fully in the post to which you
are replying. Re-read that part please.

If you want a new workbook hit File>New File or New File Icon or CTRL + n

Leave Personal.xls where it is in XLSTART but keep it hidden as above.


Gord