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

Worksheets.Add won't let me specify a template
 

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" wrote in message
...
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

Worksheets.Add won't let me specify a template
 

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

Worksheets.Add won't let me specify a template
 

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" wrote in message
...
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

Worksheets.Add won't let me specify a template
 

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" wrote in message
...
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

Worksheets.Add won't let me specify a template
 

XL2004?

We've done 2004 Dave!

Bob


"Dave Peterson" wrote in message
...
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" wrote in message
...
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

Worksheets.Add won't let me specify a template
 

er, I meant xl2006!

(Isn't xl2004 for the mac?)

Bob Phillips wrote:

XL2004?

We've done 2004 Dave!

Bob

"Dave Peterson" wrote in message
...
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" wrote in message
...
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