False.xls (Excel 2002 SP3)

Topic

New Reply

The code below is called from an Access (XP SP3) database, and it successfully creates a “False.xls” spreadsheet in MyDocuments, just exactly as I want EXCEPT: the name is not what I selected and the path is not what I had asked for ! . I searched this forum and found a message thread, specifically message 280839 that indicated that Torquemada had encountered this in 2003, and JohnBF helped him, but I don’t think the circumstances are exactly the same as mine. Here is my entire code for what I want to do, and if anything jumps out at you, any comments will be welcome:

Public Function SpawnWorkbook()
On Error GoTo SpawnWkbk_err

Dim xlApp As New Excel.Application
Dim NewBook As Excel.Workbook
Set xlApp = CreateObject(“excel.Application”)

Set NewBook = xlApp.Workbooks.Add(Template:=”C:Documents and SettingsmeDesktopMySpreadsheet.xls”)
With NewBook
.SaveAs FileName = “c:tempBlapSales.xls”
End With

SpawnWkbk_exit:
NewBook.Close
Set NewBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Function

SpawnWkbk_err:
MsgBox CStr(Err.Number) & “: ” & Err.Description
GoTo SpawnWkbk_exit
End Function

* I might add that the template is just another spreadsheet, I don’t know if it needs to be saved explicitely as a template? — just in case it matters.
thx
Pat

Reply | Quote

Replies

1) You should remove the line

Set xlApp = CreateObject("excel.Application")

Declaring xlApp as New Excel.Application already starts Excel.

2) The Save As line should be

.SaveAs FileName:="c:tempBlapSales.xls"

Note the := between FileName and the actual name.

Reply | Quote

THANK YOU, HANS !
I’m embarassed to say that I removed the “:” when some earlier code didn’t work. I’m also embarassed to show what little I know about Excel VB, that I basically had a “new” and a “create.” Thank you so much! It worked perfectly.
Pat W.

Reply | Quote