excel 2010 save as default template & other template issues

I don't see an answer to this but I may not be searching for the right terms. I want to save a file opened in Excel to the default template, Book.xltx (see below). On my system that is (weirdly) located in "Word Templates" which I suppose is a default I set in Word 2010. For example, I open a csv file and save as the same filename in xlsx format. How can I automatically apply the default sheet template to the csv when I open it or when I save it?

The Book.xltx template works when I select it from the My Templates menu when I create a new workbook but isn't the default for a blank workbook (another problem I suppose). It names it Book.xltx1 which is also stupid, can I get it to name the new workbook Book.xlsx or similar?
orerockonAsked:
Who is Participating?
 
Rory ArchibaldCommented:
There is no book.xltx template unless you create one.
0
 
Rory ArchibaldCommented:
Book.xltx is the default for new workbooks if you use Ctrl+N (or any method other than File-New-Blank workbook).
0
 
rspahitzCommented:
Are you asking how to make a new default template, or how to apply a template to a CSV file?

In the first case, simple create your template as a regular spreadsheet then, as rory indicated, save as Book.xltx in the default templates folder.

In the case of applying that to another file, I don't think there is a direct way to do that.  A template is a one-time set-up file and is not like HTML stylesheets that will apply to existing files every time you update the stylesheet.
So the only practical way to work this is to open a new spreadsheet based on the template you want (such as the default) then import the CSV file into a new sheet and reference the pieces as needed from that sheet.
You could also write some VBA to handle all of this, but it's not a trivial task so it is more subject to problems.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
orerockonAuthor Commented:
rorya: ctrl-N doesn't use the Book.xltx template in C:\Program Files\Microsoft Office\Office14\1033. Neither does it use Sheet.xltx when I rename Book.xltx and save it as Sheet.xltx. Also file-new I still have to click my templates & select Book.xltx to get it to work.

rspahitz: Good advice, however where is the default templates folder? I only find an xltx file extension in C:\Program Files\Microsoft Office\Office14\1033 in Win 7 search. And a bunch of junk in Office 2010/Templates.

0
 
Rory ArchibaldCommented:
The template should be in one of your XLSTART folders.
0
 
rspahitzCommented:
I found that the best way to find the templates folder is to try to save something with type templates.  On the save-as window, as soon as you pick Excel Templates in the bottom drop-down list, it changes to the templates folder...copy the path then cancel the save.

In my win7 setup it's here:

C:\Users\{current user}\AppData\Roaming\Microsoft\Templates
0
 
rspahitzCommented:
Looking further, that might be only for custom templates.
I also see this for the macros workbook:

C:\Users\{current user}\AppData\Roaming\Microsoft\Excel\XLSTART

Since I don't see Book.xltx, I'm not sure if it's because I haven't customized it (and it uses a default blank set) or because it's in a different location.  I'll search and see if I locate it.
0
 
orerockonAuthor Commented:
Thanks all, I never did resolve this, but thatnks for the suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.