Excel templates (XL 2000)

Topic

New Reply

Greetings,
Does Excel have a kind of start-up template like normal.dot that Word has? I’d like to change some of the workbook defaults dealing mostly with page setup (landscape, different margins, etc.).

I figured one could set up a dummy sheet with the desired settings, save it, and open it first when going into Excel. I suppose you could even put that dummy file as a parameter in the shortcut when Excel opens, but then you run the risk of saving your new workbook over the top of the dummy starter workbook.

I’m looking for a cleaner solution than that – any ideas?

Reply | Quote

Replies

Save your dummy sheet with all the settings you like as a template named Book.xlt.

Quit Excel. Locate your Templates folder and your XLStart folder (location depends on the OS you use). Move Book.xlt from the Templates folder to the XLStart folder, and make a copy there named Sheet.xlt.

If you now start Excel, or click the New button, Book.xlt will be used. If you select Insert | Worksheet, Sheet.xlt will be used.

Reply | Quote

Windows XP Home & MS Office 2000 (9.0.2720)

Hans,

I have been looking for an answer to how to change the default template in Excel for several days now & none of the methods on the Excel sites work for me. They are all similar to yours. I have tried the method you set out & that doesn’t either. Entering the path to my template file in Tools | Options | General | Alternate Startup File location: hasn’t worked.

To keep the exercise simple my Book.xlt has columns that are 20 wide rather than the default 8.43. Following your instructions I saved my Book.xlt to the folder:

C:Documents & SettingsMalcolmApplication DataMicrosoftTemplates

I then copied Book.xlt to the folder:

D:Microsoft OfficeOfficeXLStart

and made a copy in that folder named Sheet.xlt.

When I open Excel an 8.43 wide column worksheet is displayed. When I click Insert | Worksheet Sheet 2 is also in 8.43 columns. When I click File | New I have a choice of Workbook with 8.43 columns or Book.xlt that has columns 20 wide.

I want the sheet that has 20 wide columns to be the default. But all my efforts to do so have come to nothing!

Incidentally despite a Search of all drives & a manual inspection I have failed completely to find Excel’s own default Worksheet file. Can you tell me where I am going wrong please?

Reply | Quote

The defaults are not stored in a “file” they are part of the excel.exe program.

Just to make sure: you have installed not in the default location on the C Drive under program files but have it on the D Drive? or is this your alternate startup location?

“Typically” the XLStart excel looks in is:
C:Program FilesMicrosoft OfficeOfficeXLSTART

Though I realize you might have changed the installation folder or added it as an alternative startup folder.

You do not need to add it to your templates, it just must be available in one of the default startup folders (XL’s XLStart or your defined “alt startup directory”. It works as expected in XL97 in Win XP: adding a book.xlt and sheet.xlt in XLStart has the default workbook created at startup with wider columns

The “default file location” or the “templates” folders do not matter. The other 2 folders open all the files in them at XL start, these 2 only add them as requested.

Steve

Reply | Quote

MalcolmWalker indicates that he is using Windows XP. The XLSTART folder is user-dependent under Windows 2000 and Windows XP:

C:Documents and SettingsApplication DataMicrosoftExcelXLSTART

Personal.xls, Book.xlt and Sheet.xlt should go into this folder.

Reply | Quote

It must be a combo of XL2000 (and later) and XP (or Win 2000), not just using XP (or WIn2K) alone.

WIth XL97 and Win XP puts it in as I describe under program files (at least it did on my computer). I also “think” (I can’t confirm until I get to work) that XL97 and Win2K also uses an XLStart under program files.

I also realize that the installation folder can be “customized” by the user at install.

I only raise the point to be sure that he is putting the Book.xlt and Sheet.xlt in the correct folder: it must be in the same folder that contains “personal.xls” or the defined “alt startup directory” to be availble and work as desired.

Steve

Reply | Quote

You’re correct. Excel 97 doesn’t “know” about the concept of the Documents and Settings folder, so it uses the XLSTART under C:Program Files…

Excel 2000 and 2002 still have an XLSTART folder under C:Program Files…, but its use should be avoided if you want to be able to give each user his/her own copy of Personal.xls etc. And on a network with roaming profiles, the version under C:Documents and Settings is the one that will automatically be copied when you log in on another PC, because it is part of your profile.

Reply | Quote

You’re correct. Excel 97 doesn’t “know” about the concept of the Documents and Settings folder, so it uses the XLSTART under C:Program Files…

Excel 2000 and 2002 still have an XLSTART folder under C:Program Files…, but its use should be avoided if you want to be able to give each user his/her own copy of Personal.xls etc. And on a network with roaming profiles, the version under C:Documents and Settings is the one that will automatically be copied when you log in on another PC, because it is part of your profile.

Reply | Quote

It must be a combo of XL2000 (and later) and XP (or Win 2000), not just using XP (or WIn2K) alone.

WIth XL97 and Win XP puts it in as I describe under program files (at least it did on my computer). I also “think” (I can’t confirm until I get to work) that XL97 and Win2K also uses an XLStart under program files.

I also realize that the installation folder can be “customized” by the user at install.

I only raise the point to be sure that he is putting the Book.xlt and Sheet.xlt in the correct folder: it must be in the same folder that contains “personal.xls” or the defined “alt startup directory” to be availble and work as desired.

Steve

Reply | Quote

Hello Hans,

You are quite right about my system & the os & applications I am using. I have confirmed the details in my message to Steve sent a few moments ago.

However doing what you say in your message to Steve has produced a VB error on my computer when opening & closing Excel. The details are again in my message to Steve if you would be kind enough to have a look at it.

Reply | Quote

Hello Hans,

You are quite right about my system & the os & applications I am using. I have confirmed the details in my message to Steve sent a few moments ago.

However doing what you say in your message to Steve has produced a VB error on my computer when opening & closing Excel. The details are again in my message to Steve if you would be kind enough to have a look at it.

Reply | Quote

MalcolmWalker indicates that he is using Windows XP. The XLSTART folder is user-dependent under Windows 2000 and Windows XP:

C:Documents and SettingsApplication DataMicrosoftExcelXLSTART

Personal.xls, Book.xlt and Sheet.xlt should go into this folder.

Reply | Quote

Thank you for your reply Steve & for clarifying why my search for a default Excel template was fruitless! And I can confirm that my operating system (Windows XP Home) is on the C: drive, that my applications (including MS Office 2000) are are on the D: drive & my data is on the E: drive. I do not have a folder C:Program FilesMicrosoft OfficeOfficeXLStart. I do have a folder D:Microsoft OfficeOfficeXLStart.

I put two copies of the worksheet I want as Excel’s default template into that XLStart folder; one named Book.xlt & the other Sheet.xlt. When I opened Excel a dialogue was displayed:

Microsoft Visual Basic
Compile Error in hidden module: AutoExecNew
OK Help

By clicking OK Excel displayed my new default worksheet. When closing Excel another dialogue was displayed:

Microsoft Visual Basic
Compile Error in hidden module: DistMon
OK Help

By clicking OK Excel closes. I have deleted Book.xlt & Sheet.xlt from the XLStart folder & Excel continues to display the same dialogues when it is opened & closed.

I seem to have damaged Excel by putting the my templates into the XLStart folder.

Reply | Quote

See MSKB article OFF: “Compile Error in Hidden Module” Error Message When You Start Word or Excel and Jan Karel Pieterse’s Systematic Approach to Behavioral Problems in XL.

Reply | Quote

Thank you Hans. All is now well & my default template is displayed with no error messages!

Reply | Quote

Thank you Hans. All is now well & my default template is displayed with no error messages!

Reply | Quote

See MSKB article OFF: “Compile Error in Hidden Module” Error Message When You Start Word or Excel and Jan Karel Pieterse’s Systematic Approach to Behavioral Problems in XL.

Reply | Quote

Thank you for your reply Steve & for clarifying why my search for a default Excel template was fruitless! And I can confirm that my operating system (Windows XP Home) is on the C: drive, that my applications (including MS Office 2000) are are on the D: drive & my data is on the E: drive. I do not have a folder C:Program FilesMicrosoft OfficeOfficeXLStart. I do have a folder D:Microsoft OfficeOfficeXLStart.

I put two copies of the worksheet I want as Excel’s default template into that XLStart folder; one named Book.xlt & the other Sheet.xlt. When I opened Excel a dialogue was displayed:

Microsoft Visual Basic
Compile Error in hidden module: AutoExecNew
OK Help

By clicking OK Excel displayed my new default worksheet. When closing Excel another dialogue was displayed:

Microsoft Visual Basic
Compile Error in hidden module: DistMon
OK Help

By clicking OK Excel closes. I have deleted Book.xlt & Sheet.xlt from the XLStart folder & Excel continues to display the same dialogues when it is opened & closed.

I seem to have damaged Excel by putting the my templates into the XLStart folder.

Reply | Quote

Steve,

Not to take any more of your time but with an exchange of messages with Hans all is now well & I can display my default worksheet when opening Excel.

I am very grateful for your interest in my enquiry. Thank you.

Reply | Quote

Steve,

Not to take any more of your time but with an exchange of messages with Hans all is now well & I can display my default worksheet when opening Excel.

I am very grateful for your interest in my enquiry. Thank you.

Reply | Quote

The defaults are not stored in a “file” they are part of the excel.exe program.

Just to make sure: you have installed not in the default location on the C Drive under program files but have it on the D Drive? or is this your alternate startup location?

“Typically” the XLStart excel looks in is:
C:Program FilesMicrosoft OfficeOfficeXLSTART

Though I realize you might have changed the installation folder or added it as an alternative startup folder.

You do not need to add it to your templates, it just must be available in one of the default startup folders (XL’s XLStart or your defined “alt startup directory”. It works as expected in XL97 in Win XP: adding a book.xlt and sheet.xlt in XLStart has the default workbook created at startup with wider columns

The “default file location” or the “templates” folders do not matter. The other 2 folders open all the files in them at XL start, these 2 only add them as requested.

Steve

Reply | Quote

Windows XP Home & MS Office 2000 (9.0.2720)

Hans,

I have been looking for an answer to how to change the default template in Excel for several days now & none of the methods on the Excel sites work for me. They are all similar to yours. I have tried the method you set out & that doesn’t either. Entering the path to my template file in Tools | Options | General | Alternate Startup File location: hasn’t worked.

To keep the exercise simple my Book.xlt has columns that are 20 wide rather than the default 8.43. Following your instructions I saved my Book.xlt to the folder:

C:Documents & SettingsMalcolmApplication DataMicrosoftTemplates

I then copied Book.xlt to the folder:

D:Microsoft OfficeOfficeXLStart

and made a copy in that folder named Sheet.xlt.

When I open Excel an 8.43 wide column worksheet is displayed. When I click Insert | Worksheet Sheet 2 is also in 8.43 columns. When I click File | New I have a choice of Workbook with 8.43 columns or Book.xlt that has columns 20 wide.

I want the sheet that has 20 wide columns to be the default. But all my efforts to do so have come to nothing!

Incidentally despite a Search of all drives & a manual inspection I have failed completely to find Excel’s own default Worksheet file. Can you tell me where I am going wrong please?

Reply | Quote

pschommer

To answer your question, yes Excel does have a template system that you can create and then have new workbooks be based on. Check how to make one in the OLH, “Customize the defaults for a workbook or worksheet by using a template”. Also check where to place this template so that you can get access to it each and every time you start Excel.

It does not function as the Normal.DOT template in Word, but has very similar functions to that concept.

Hope this helps.

Wassim

Reply | Quote