I’m having a small problem with the New Workbook Taskpane in Excel 2003 (11.6355.6360). I was investigating a problem dealing with application level events and book.xlt when I “discovered” that creating a new workbook from the task pane does not do what I expect. I created a template file named book.xlt and saved it in my xlstart directory. When I press Ctrl+N, a new workbook based on book.xlt was created. When I choose File > New, I get:
I dutifully choose Blank workbook, and I get a new workbook but it’s not based on book.xlt. What the heck is the point of book.xlt if File > New doesn’t use it? You could make the argument that choosing Blank Workbook gives you a blank workbook, but it seems to defeat the purpose. Am I doing something wrong here?
Looks like the Blank workbook selection is reserved for Excel’s use only.
Excel often does what it wants when creating new workbooks and tabs. For example, ever notice that when you hit Ctrl-F11, Excel creates a new sheet called Macro1? No module in VBA is started mind you. Excel just creates a new tab. Hit Ctrl-F11 again, and you get a new sheet called Macro2.
Is this just a goof, or am I the last person to know about something cool.
Never Mind. Apparently this some sort of throw-back functionality for Excel 4.0 I guess I should be pretty happy that I don’t remember using this. Means I’m not as old as some.
We had a discussion last week over at JMT (Colo and crew) about this very issue. It seems that when using XL 97 (and even 2002) your method would work. But I can’t get it to work in XL 2003.
I never have been able to use Excel Templates because of our network settings. They erase everything in the xlstart directory, either on open/close of the program or login/logout. I never could figure out which triggered it.
D@mn IT departments.
sheet.xlxt templates aren’t working properly in Excel 2007 beta. I created a sheet template and saved it in XLStart.
When I right-click a sheet tab and choose Insert, then click Worksheet, my new sheet.xltx template is inserted as it should be. But when I insert a new sheet using the New Worksheet control (next to the last sheet tab), the sheet that’s inserted is the normal one, not the template.
I think these sheet/workbook templates are so rarely used that getting them to work as advertised is a very low priority
Mike –
“I guess I should be pretty happy that I don’t remember using this. Means I’m not as old as some.”
So your memory’s shot, and that’s proof you’re not losing it??
John –
The template behavior in 2003 is confusing and erratic, so I haven’t even tried 2007 templates yet. I use templates all the time in my various projects, but I never rely on book1.xlt or sheet1.xlt. Instead I give them more descriptive names, and my projects use UI elements to create new sheets and workbooks explicitly based on the templates. Sometimes I use a listbox to allow the user to select a template.
Dick –
I guess you’re supposed to use that “On my computer…” button to get to book1.xlt.
There are other problems with the Taskpane….
In XP the “file search” option was integrated to the “new workbook”, Getting started etc…
In office 2003 its sperate… You can switch from the file search mode to other modes but not vice versa…
Also I found the file search to be quite eratic.
Ex : If you specify a file type as Excel file. and tried to search for a file with file name containing some text (ex : sam) It will list all files, Excel and non excel which have sam in the file name…. This was some thing that was not happening in XP….may be its an ehancement..in 2003
On the Subject of Templates…
I liked the built in templates that shipped with office 97/2000
Ex the Sales invoice template/Purchase order template etc – was from Village software – had data tracking feature built in
In XP/2003 the data tracking addin was not shipped with the box…had to be downloaded from MS
Also the Invoice templates there are quite feature less…..
Is the template wizard with data tracking – supported in 2007….
I always wondered why this addin was not modified using ADO to write data to a closed file…rather than opening the database file to update data…
I’d been looking at this myself in connection with a request to force a ‘corporate template’ on users the same way you would in Word. It seems that as well as Book.xlt and Sheet.xlt you can have Workbook.xlt which is what Excel seems to use by default. Also if you allow users the freedom to mess with Tools/Options they can change the standard font which it makes a registry entry for until you restart Excel at which point the change seems to get incorporated in Workbook.xlt. Add to this the complication that any changes you make to the UI get stored in Excel11.xlb and the fact that the Headers and Footers that get set on installation can’t be removed, unless you know something I don’t, and enforcing a standard becomes a bit of a nightmare. I can fully understand why with an ‘integrated suite’ you might want to set the standard font in word to Times New Roman 11 and do the same in Excel but it’s not made easy to enforce.
Hello Dick,
I assume that you have various XLStart-folders on your system.
Make sure that book.xlt (and perhaps sheet.xlt) is in the presently used XLStart folder.
This code shows you the correct path of XLStart-Folder:
Sub Show_StartupPath()
MsgBox Application.StartupPath
End Sub
We had the same problem with this solution in a german-forum.
Regards,
Beate
Thanks. That did it!
Beate and Rich,
Are you guys sure? I get the same behavior as Dick regardless of whether the Book.xlt is within the XLStart or the AltXLStart directory. Is there anything else that you guys are doing here? If not, then I wonder if some sort of patch or update may have caused this? I’m using ‘11.6355.6360’, which seems to be the same as Dick’s. Out of curiousity, what version are you guys on?
Dick,
I cannot test 2002 currently, but I definately get the same behavior as you in Excel 2003. To be honest, I never liked the extra clicks required to use the Task Pane anyway. The older ‘New’ control (ID=2520) does utilize the Book.xlt natively, but Excel does not seem to trust it regardless of whether it resides in the XLStart or the AltStartup so if the book.xlt contains any macros (which mine does) then the user is forced to choose ‘Enable’ from the dialog box, which is rather annoying. My solution is to remove the ‘New…’ control (ID=18) and/or ‘New’ control (ID=2520) from the File menu and replace it with a custom control button that uses VBA to call Workbooks.Add(Template:=”Book.xlt”) and simply give it “New” for the caption. This is very easy to execute and seamless to the user.
In Excel 2007, if the book.xlt is ignored, then I guess we’d have to hook (or “repurpose”) the File > New > Blank Workbook control and use code to directly open the Book.xlt, similar to how I described above. In theory this should not be a big deal to do, but until Excel 2007 is out “for real”, I am personally not likely to be messing with the CustomUI XML much.
Hi Dick
Maybe it use the workbook here.
C:WINDOWSShellNew
This workbook is used when you right click on the desktop and
choose New Excel worksheet.
Excel 2003 (11.6560.6568)
And yes, it is working. I click on the New icon on toolbar, and I get the new document with the new color scheme.
Sorry, that was my post above (forgot my full name)
Hmmm… Ok, so you’re on 11.6560.6568, which seems slightly newer than what I’m using (and what Dick said he’s on). I guess something may have been fixed? I dunno. I don’t even know why I’m behind on the patches since I should be on auto-update.
If it’s not a patch issue, then I don’t know why it I can’t get it to work on my setup. I can only guess that it’s the patch, or I’m dumb…