Creating an XCEL Work Book

[marilyn]

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
strFnameTmp1 = "c:\Data\Burn.xls"
If FileExists(strFnameTmpl) Then
Set oBook = oExcel.Workbooks.Open(strFnameTmpl)
Else
Set oBook = oExcel.Workbooks.Add(strFnameTmpl)
End If

On the Set obook = OExcel.workbooks.add(strFnameTmpl), the
following runtime error occurred: "Burn.xls could not be
found". I thought the ADD command allows the creation of
a new workbook. What am I doing wrong?

Thank you

 

[Ken Snell [MVP]]

Change
Set oBook = oExcel.Workbooks.Add(strFnameTmpl)

to
Set oBook = oExcel.Workbooks.Add()
oBook.SaveAs strFnameTmpl

When you use the optional argument for the Add method, it tries to use that
filename as a template from which to make the new file.

 

[Douglas J. Steele]

Your file is stored in strFnameTmp1, but you're using strFnameTmpl when you
try to add the worksheet.

Even once you correct that, though, it's not going to do what you want. If
you pass a file name to the Add method, it'll use that file as a template
when creating a new file. You cannot pass it a non-existant file name. To
just create a new template, use

Set oBook = oExcel.Workbooks.Add

You then save the book:

oBook.SaveAs strFnameTemp1

(You can either save it immediately, and then oBook.Save when you're done,
or wait until the end, and just save it once)