Is there a template option for Workbooks.Add(xlWBATWorksheet)?

[Hank Scorpio]

This has been bugging me all day.

If you save a template named book.xlt in your xlStart folder, then any
new standard workbooks will inherit the styles and formatting of that
template.

That's fine.

If you save a single sheet template named sheet.xls, then any new
worksheet that is added to an existing workbook will inherit the
styles and formatting of that template sheet.

That's fine too.

But for the life of me, I cannot find any way to create a template
which will apply if you create a single sheet workbook using the

Application.Workbooks.Add(xlWBATWorksheet)

method. It doesn't use book.xlt's, it doesn't use sheet.xlts, and I
cannot find ANYWHERE that suggests whether it can use a template and
if so, what that template should be named. (Nor have I fluked the
discovery of that through trial and error.)

I couldn't find anything through web searches, and unfortunately
Google has so comprehensively cactused the newsgroup search engine
that it's impossible to find anything there either.

I'm aware that there are ways to work around this for your own
application, but we use an external application which generates single
sheet files (presumably) using the above method and it is ANNOYING to
have to merge our standard styles into them.

Does anyone have any thoughts?

 

[Joel]

Instead, just open a one sheet workbook that contains your formating. You
can open it in Read mode so it doesn't get changed. Then use SAVEAS to
change the file to a different name.

 

[Dave Peterson]

Workbooks.Add has its own template parm that you can use:

Workbooks.Add template:="c:\your path to your template\mytemplate.xlt"
Workbooks.Add template:=Application.StartupPath & "\sheet.xlt"

 

[Hank Scorpio]

Workbooks.Add has its own template parm that you can use:

Workbooks.Add template:="c:\your path to your template\mytemplate.xlt"
Workbooks.Add template:=Application.StartupPath & "\sheet.xlt"

Thanks Dave, but as I did mention:

I'm afraid that the question therefore still stands.

 

[Hank Scorpio]

Instead, just open a one sheet workbook that contains your formating. You
can open it in Read mode so it doesn't get changed. Then use SAVEAS to
change the file to a different name.

Thanks, but as I said...

 

[Dave Peterson]

I'd go back to the developers and tell them what you need.

But you may want to try this from Gord Dibben:
http://groups.google.co.uk/group/mi...711/6347f92429e904cc?q=shellnew+author:dibben
or
http://is.gd/2aTyp

Maybe it'll work in code, too.

(I didn't try it.)

 

[Hank Scorpio]

I'd go back to the developers and tell them what you need.

It's a commercial application; actually more than one. What I really
*need* is for MS not to have holes in its ability to customise the
formatting of sheets which are generated by automation. But given that
their interest these days seems to lie considerably more in the
direction of New! User! Interfaces! and destroying functionality when
it becomes all too hard to maintain rather than fixing bugs or
useability gaps, I ain't holding my breath.

Thanks for taking the time anyway. Nice to see at least a few familiar
names (you and Gord) still around from my time in the groups lo those
many years ago; it looks like a lot have departed. Hopefully I won't
leave it another 3 years before I drop in again, though I may need to
lever myself into using xl2007 before then. 8^>