[SOLVED] Worksheets.Add won't let me specify a template

In Excel 2003, the documentation for Worksheets.Add suggests that I can use
the Type parameter to specify a path to a template for an added worksheet.
This works with Sheets.Add, but produces a 1004 run-time error with
Worksheets.Add
Although the workaround is simple, the on-line help ought to be changed.

The following code will demonstrate the error in Excel 97, 2000, 2002 and
2003:

Sub NewSheet()
'This statement produces Run-time error 1004, Method 'Add' of object
'Sheets' failed
Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"

Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
End Sub

I got the same problem xL2002. I usually use the workbooks.add template
method which works OK. But it is worth noting this inconsistency. Thanks

--
Cheers
Nigel



"byundt" <byundt@discussions.microsoft.com> wrote in message
news:217BFABB-D6C2-4723-8D64-6C42DC5FCA9F@microsoft.com...
> In Excel 2003, the documentation for Worksheets.Add suggests that I can
use
> the Type parameter to specify a path to a template for an added worksheet.
> This works with Sheets.Add, but produces a 1004 run-time error with
> Worksheets.Add
> Although the workaround is simple, the on-line help ought to be changed.
>
> The following code will demonstrate the error in Excel 97, 2000, 2002 and
> 2003:
>
> Sub NewSheet()
> 'This statement produces Run-time error 1004, Method 'Add' of object
> 'Sheets' failed
> Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"
>
> Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
> End Sub

Interestingly, in Excel 2003 the Worksheets.Add Type:= statement will accept
three of the four XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
xlExcel4IntlMacroSheet. It fails on xlChart.
--
Brad

byundt,
XL2000 Help:
<
Add Method (Worksheets Collection)

Creates a new worksheet. The new worksheet becomes the active sheet. Returns
a Worksheet object.
Syntax
expression.Add(Before, After, Count, Type)
expression Required. An expression that returns a Worksheets object.
.............
Type Optional Variant. The sheet type. Can be one of the following
XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
xlExcel4IntlMacroSheet. The default value is xlWorksheet.
/>
No mention of Template. Only with Workbooks.Add.
Which makes sense really.

NickHK


"byundt" <byundt@discussions.microsoft.com> wrote in message
news:217BFABB-D6C2-4723-8D64-6C42DC5FCA9F@microsoft.com...
> In Excel 2003, the documentation for Worksheets.Add suggests that I can
use
> the Type parameter to specify a path to a template for an added worksheet.
> This works with Sheets.Add, but produces a 1004 run-time error with
> Worksheets.Add
> Although the workaround is simple, the on-line help ought to be changed.
>
> The following code will demonstrate the error in Excel 97, 2000, 2002 and
> 2003:
>
> Sub NewSheet()
> 'This statement produces Run-time error 1004, Method 'Add' of object
> 'Sheets' failed
> Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"
>
> Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
> End Sub

Xl2003 add some words:

Type Optional Variant. Specifies the sheet type. Can be one of the following
XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or
xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing
template, specify the path to the template. The default value is xlWorksheet.

Maybe in xl2004, they'll add that specifying the user template will only work
with sheets.add (not worksheets.add) <vbg>.

(and that "worksheets.add type:=xlChart" won't work, either.)



NickHK wrote:
>
> byundt,
> XL2000 Help:
> <
> Add Method (Worksheets Collection)
>
> Creates a new worksheet. The new worksheet becomes the active sheet. Returns
> a Worksheet object.
> Syntax
> expression.Add(Before, After, Count, Type)
> expression Required. An expression that returns a Worksheets object.
> ............
> Type Optional Variant. The sheet type. Can be one of the following
> XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
> xlExcel4IntlMacroSheet. The default value is xlWorksheet.
> />
> No mention of Template. Only with Workbooks.Add.
> Which makes sense really.
>
> NickHK
>
> "byundt" <byundt@discussions.microsoft.com> wrote in message
> news:217BFABB-D6C2-4723-8D64-6C42DC5FCA9F@microsoft.com...
> > In Excel 2003, the documentation for Worksheets.Add suggests that I can
> use
> > the Type parameter to specify a path to a template for an added worksheet.
> > This works with Sheets.Add, but produces a 1004 run-time error with
> > Worksheets.Add
> > Although the workaround is simple, the on-line help ought to be changed.
> >
> > The following code will demonstrate the error in Excel 97, 2000, 2002 and
> > 2003:
> >
> > Sub NewSheet()
> > 'This statement produces Run-time error 1004, Method 'Add' of object
> > 'Sheets' failed
> > Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"
> >
> > Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
> > End Sub

--

Dave Peterson

XL2004?

We've done 2004 Dave!

Bob


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4309C88F.EB0C2E10@verizonXSPAM.net...
> Xl2003 add some words:
>
> Type Optional Variant. Specifies the sheet type. Can be one of the
following
> XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or
> xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing
> template, specify the path to the template. The default value is
xlWorksheet.
>
> Maybe in xl2004, they'll add that specifying the user template will only
work
> with sheets.add (not worksheets.add) <vbg>.
>
> (and that "worksheets.add type:=xlChart" won't work, either.)
>
>
>
> NickHK wrote:
> >
> > byundt,
> > XL2000 Help:
> > <
> > Add Method (Worksheets Collection)
> >
> > Creates a new worksheet. The new worksheet becomes the active sheet.
Returns
> > a Worksheet object.
> > Syntax
> > expression.Add(Before, After, Count, Type)
> > expression Required. An expression that returns a Worksheets object.
> > ............
> > Type Optional Variant. The sheet type. Can be one of the following
> > XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
> > xlExcel4IntlMacroSheet. The default value is xlWorksheet.
> > />
> > No mention of Template. Only with Workbooks.Add.
> > Which makes sense really.
> >
> > NickHK
> >
> > "byundt" <byundt@discussions.microsoft.com> wrote in message
> > news:217BFABB-D6C2-4723-8D64-6C42DC5FCA9F@microsoft.com...
> > > In Excel 2003, the documentation for Worksheets.Add suggests that I
can
> > use
> > > the Type parameter to specify a path to a template for an added
worksheet.
> > > This works with Sheets.Add, but produces a 1004 run-time error with
> > > Worksheets.Add
> > > Although the workaround is simple, the on-line help ought to be
changed.
> > >
> > > The following code will demonstrate the error in Excel 97, 2000, 2002
and
> > > 2003:
> > >
> > > Sub NewSheet()
> > > 'This statement produces Run-time error 1004, Method 'Add' of object
> > > 'Sheets' failed
> > > Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"
> > >
> > > Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
> > > End Sub
>
> --
>
> Dave Peterson

er, I meant xl2006!

(Isn't xl2004 for the mac?)

Bob Phillips wrote:
>
> XL2004?
>
> We've done 2004 Dave!
>
> Bob
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4309C88F.EB0C2E10@verizonXSPAM.net...
> > Xl2003 add some words:
> >
> > Type Optional Variant. Specifies the sheet type. Can be one of the
> following
> > XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or
> > xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing
> > template, specify the path to the template. The default value is
> xlWorksheet.
> >
> > Maybe in xl2004, they'll add that specifying the user template will only
> work
> > with sheets.add (not worksheets.add) <vbg>.
> >
> > (and that "worksheets.add type:=xlChart" won't work, either.)
> >
> >
> >
> > NickHK wrote:
> > >
> > > byundt,
> > > XL2000 Help:
> > > <
> > > Add Method (Worksheets Collection)
> > >
> > > Creates a new worksheet. The new worksheet becomes the active sheet.
> Returns
> > > a Worksheet object.
> > > Syntax
> > > expression.Add(Before, After, Count, Type)
> > > expression Required. An expression that returns a Worksheets object.
> > > ............
> > > Type Optional Variant. The sheet type. Can be one of the following
> > > XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
> > > xlExcel4IntlMacroSheet. The default value is xlWorksheet.
> > > />
> > > No mention of Template. Only with Workbooks.Add.
> > > Which makes sense really.
> > >
> > > NickHK
> > >
> > > "byundt" <byundt@discussions.microsoft.com> wrote in message
> > > news:217BFABB-D6C2-4723-8D64-6C42DC5FCA9F@microsoft.com...
> > > > In Excel 2003, the documentation for Worksheets.Add suggests that I
> can
> > > use
> > > > the Type parameter to specify a path to a template for an added
> worksheet.
> > > > This works with Sheets.Add, but produces a 1004 run-time error with
> > > > Worksheets.Add
> > > > Although the workaround is simple, the on-line help ought to be
> changed.
> > > >
> > > > The following code will demonstrate the error in Excel 97, 2000, 2002
> and
> > > > 2003:
> > > >
> > > > Sub NewSheet()
> > > > 'This statement produces Run-time error 1004, Method 'Add' of object
> > > > 'Sheets' failed
> > > > Worksheets.Add Type:="C:\VBA\Temp\Sheet1.xlt"
> > > >
> > > > Sheets.Add Type:="C:\VBA\Temp\Sheet1.xlt" 'This statement works
> > > > End Sub
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson