Add worksheet as template

[Guest]

Hello,
I have an Excel/Word VBA app that uses templates for both. Trying to make it
compatible for Office 97 forward, I had a problem using the
Application.TemplatesPath to find the templates as the older and newer
versions use difference locations.
I am thinking perhaps the simplest solution is to do as one respondent said,
to just put my Word and Excel templates in my Program Files\Application
folder. I have Word templates and also Excel workbook and worksheet
templates. For the Word .dot and Excel Workbook templates I can then set the
path in the code. The only problem I am having now is trying to use the add
method of the Worksheet to add the worksheet template to a workbook.
The MS website doesn't give very specific instructions for adding a sheet as
a template. I have tried to add from my template after the last sheet such as:
Worksheets.Add.Move (after:=
Worksheets(Worksheets.Count),Type:="C:\Path\TEmplateName.xlt")
and also
Worksheets.Add.Move Type:="C:\Path\TEmplateName.xlt"

However, I get error Application-defined or object-defined error message.
Any idea what the right syntax is to get this done.
Thanks again,
Van

 

[Dave Peterson]

Use Sheets.add instead of worksheets.add:

Sheets.Add Type:="C:\Path\TEmplateName.xlt"

 

[Guest]

Thanks, Dave. That worked.
I am trying to move the added sheet to the last postion in one step, using
the approximate syntax in some old Excel documention but get an error.

sheets.Add.Move (after:= Worksheets(Worksheets.Count), type:=
"C:\Path\TemplateSheet.xlt")
Do you know how to correct this, or how to do in one line, or do I have to
do in two steps?
Thanks again, God bless
Van

 

[Guest]

A bit late, but thanks again for your help Dave.
God bless
Van