Excel Sheet and (Book) Templates In Controlled Environments

Topic

New Reply

In a controlled/secure Windows environment, once can still use book.xltx in Excel by double-clicking.

(By controlled/secure I mean that you don’t have authority to put that template in the XLStart folder).

But can sheet.xltx be used to insert new worksheets into workbooks if it can’t be loaded into the XLStart folder?

Reply | Quote

Replies

If the user has access to the Templates folder they can put it there and it will then appear in the list of sheets when you right-click a tab and choose Insert…

Otherwise you could manually copy the sheet from the template to any workbook, or use code to insert it.

Reply | Quote

I believe I have tried that. What is the correct path to that folder?

To be clear, this is the folder that shows in a dialog when one right clicks a sheet’s tab in Excel, then chooses Insert off the context menu that opens.

Reply | Quote

WSboobounder wrote:

What is the correct path to that folder? To be clear, this is the folder that shows in a dialog when one right clicks a sheet’s tab in Excel, then chooses Insert off the context menu that opens.

The default Path for Excel templates (Book & Sheet) is at:

%AppData%\Microsoft\Excel\XLSTART

The workbook template is Book.xltx and the sheet template is Sheet.xltx.

If controlled environment you mean that you don’t have Admin rights such as a company PC, you may still access the templates. The folder is hidden and your PC may be set to not show hidden files, but directly entering the path address may allow access to the templates.

The attached pdf explains how to modify the templates.

If you can’t access the temp[lates to modify them you can create your own custom templates and have excel open new workbooks based upon that template.

Modify-the-Default-Templates-for-Excel

HTH, Dana:))

Reply | Quote

This path helped me clarify the problem.

But the initial question still stands.

You can always run book.xltx even if it’s in the wrong folder.

But how do you run sheet.xltx if you can’t put it in that XLSTart folder?

Without that, only the initial sheet in book.xltx open in Excel, but each added worksheet reverts to the systems default worksheet default template.

Reply | Quote

Sorry for the delay – this forum is hard to navigate relative to any other I use (even the MS one!)

That’s not the templates folder, that’s the XLSTART folder. The templates folder is usually something like:

%appdata%\Microsoft\Templates

If you can access that, then you can put the sheet.xltx in there.

Failing that, you can create a simple macro to add a new sheet based on a template workbook. It’s really just one main line of code:

activeworkbook.worksheets.add type:=”path to your template here”

so you could put it in a macro in your personal macro workbook and assign it a shortcut key.

 

Reply | Quote

WSboobounder wrote:

But how do you run sheet.xltx if you can’t put it in that XLSTart folder?

Just a slight hassle if you can’t access the XLSTART folder.
Open a new workbook
Delete sheets until only one sheet remains.
Adjust the formatting, etc. that you want your added sheet to be.
Save this as SHEET.xlsx (you don’t have to save it as a template .xltx) and save in a location for easy access.
To Use the SHEET.
When you need to add a sheet to your open Excel document:
Open the SHEET.xlsx workbook of only one sheet.
Right click the sheet name tab at the bottom of the sheet and select Move or Copy…
This will open the Move or Copy box
Click the dropdown arrow of the box under To book: and select the workbook you want to add the sheet to (that workbook has to be open to be on this list).
After you select the workbook, the sheets in that workbook will display for you to select where to place the sheet you want to insert.
Be sure to check the Create a copy box so you don’t Move and lose your formatted sheet.

Ask if you have any problems.

HTH, Dana:))

Reply | Quote

debaser wrote:

That’s not the templates folder, that’s the XLSTART folder. The templates folder is usually something like: %appdata%\Microsoft\Templates

Other Excel templates are kept in the %appdata%\Microsoft\Templates folder With Word’s templates, BUT the Book.xltx and the Sheet.xltx templates are in the %appdata%\Microsoft\Excel\XLSTART folder as shown in my screenshot in my instructions and the screenshot below.

I suggest you go to that folder on any of your Windows systems and you will find those templates in the folder I indicated.

 

HTH, Dana:))

Reply | Quote

You will only find those templates if you have created them. They do not exist by default. But that’s not my point (I am well aware of how they work).

If you don’t have access to the XLSTART folder but do have access to the actual Templates folder, you can put the sheet.xltx template in there and it will be listed when you right-click a worksheet tab and choose Insert…

Reply | Quote

debaser wrote:

You will only find those templates if you have created them. They do not exist by default.

Correct, but that is the only place Excel keeps the default template that Excel opens when it opens a new blank workbook.  There is no default Excel templates kept in the Microsoft\Templates folder.  The templates kept there are all custom templates even if you put sheet.xltx and book.xltx as the name of some of the custom templates.  Only Book.xltx and Sheet.xltx templates placed in the XLSTART folder will become the new default templates.   Until you create your own Book.xltx and Sheet.xltx templates, the default template is in the coding of the Excel software.  By default I mean selecting New Blank Workbook or clicking the + icon at the bottom to insert a new sheet.

When you open a new blank workbook in Excel it looks in the XLSTART folder for Book.xltx and if not there uses the default formatting set in its programing.  Book.xltx and Sheet.xltx can be used as a custom template in the Templates folder, but will have to be selected like other custom templates and are not the defaults.    That being said, I think custom template for the Sheet may be a better way to go with this poster’s needs than Copy from a spreadsheet .

Since the Templates folder is in the same hidden AppData folder I would suspect that no access to the XLSTART folder would mean no access to the Microsoft\Templates folder.   However moving the storage of the custom templates to a user’s folder and inserting from the custom templates folder would be easier.

Side Note: Once you create the Book.xltx and Sheet.xltx templates in the formatting you want, place them in the XLSTART folder and open Excel and use the default workbook and insert a default sheet; Excel remembers this as the default templates even if you remove those templates from the XLSTART folder.

 

HTH, Dana:))

Reply | Quote

Again, I know all that and it’s not the point I am making.

If you don’t have access to the XLSTART folder but do have access to Templates (I have no idea what may be locked down on the OP’s computer) then you can put Sheet.xltx in there, and use that template directly when inserting a worksheet. That’s all I’m saying.

Your side note is not correct. If those templates are removed, Excel stops using them (unless you have another copy in the other XLSTART folder – the one common to all users). The use of those templates is not written into Excel’s settings.

Reply | Quote

debaser wrote:

Your side note is not correct. If those templates are removed, Excel stops using them (unless you have another copy in the other XLSTART folder – the one common to all users). The use of those templates is not written into Excel’s settings.

If you go into Excel Options to the General tab and Uncheck the setting to show a Start screen when the application starts and save your changes.  This makes Excel open directly into a new workbook when you open it.  This workbook that opens when you click the Excel shortcut IS Excel’s default workbook template.  The default formatting is Calibri Font, Font size 11with only one sheet.  If you press the + icon it will add the default sheet template which is also Calibri Font, Font size 11.  This is Excel’s default sheet template.  Neither of these default templates exist as files.  If they did you could find them and what is the names for these default templates.  Look on the Web, nobody can tell you where the Excel’s default template is located or what the “name” of it file.  To me, that means the settings for these default templates is in Excel’s app settings (maybe a registry setting).

Changing Default settings:

I made a Book.xltx and Sheet.xltx with the Font, Font size, etc. that I wanted which opened every time I opened Excel.  These stayed in place for a long time.  I then created another Book.xltx with different formatting to replace my prior Book.xltx which worked fine.  I deleted the new Book.xltx expecting it to revert back to the default template with the Calibri Font.  It did not revert to this formatting.  The default formatting with no Book.xltx is exactly the formatting of my first Book.xltx.  So with no Book.xltx any where on my system, when I click the Excel shortcut the workbook that opens is not the original default formatting and is the formatting of my original Book.xltx, so that formatting has to be stored somewhere in Excel settings.

 

I tried to reproduce this on another PC I have that I haven’t done any thing to its Excel application.  Adding the Book.xltx to the XLSTART folder does change the default formatting and when that Book.xltx is deleted Excel reverts to it original default formatting.

 

My guess why the default settings in Excel changed on that one PC is that the Book and sheet templates were in place when I perform a repair on my Office install.  I can only guess the repair used by book template for the settings in Excel., but the default formatting for a new workbook Excel has been changed.  I guess that side note applies only to that one PC.

HTH, Dana:))

Reply | Quote