Templates and CreateObject

[John]

I have another MS app that opens Excel using the CreatObject method. I
also have a one sheet Excel workbook template saved as an *.xlt in a
folder on my "C" drive. If Excel is already open the following code
snippit will successfully replicate my pre-formatted template sheets as
many times as needed. However, if Excel is not already open and the
CreateObject Method is used, the template will open but the loop that
adds sheets does not replicate the pre-formatted first template sheet.
All I get is the one pre-formatted template sheet and the rest are
generic worksheets.

On Error Resume Next
'set up existing instance of Excel, or if Excel is not running, start it
Set XL = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0
Set XL = CreateObject("Excel.Application")
End if
XL.Workbooks.Open FileName:="C:\myfolder\sheetform.xlt", Editable:=True
Set s = XL.Workbooks(ActiveWorkbook)
For i = 1 to n
s.Sheets.Add Type:="worksheet"
Next

Anyone have any suggestions?

John

 

[jaf]

Hi John,
To create a new workbook from a template use...
XL.Workbooks.Add FileName:="C:\myfolder\sheetform.xlt

If you want to edit the template use...
XL.Workbooks.Open FileName:="C:\myfolder\sheetform.xlt