Output to Excel Questions

[Daveyk01]

Hello there,

I can export to Excel but only if an "example sheet" already exists. In the example below, NewFilename might = "Week 9 Labor Hours.XLS". If that Excel files doesn't already exist, I get a runtime error "1004 Week 9 Labor Hours.XLS could not be found....". If it already exists, it opens the Excell files and edits it properly.

NewFileName = "Week " + Str(ISOWeekNum(rs.Fields("WorkDate"))) + " Labor Hours.XLS"
' Create Sheet
Set ExcelApp = CreateObject("Excel.Application")
'With the new workbook do the following
With ExcelApp
.workbooks.Open NewFileName

This works:
With ExcelApp
.workbooks.Open "d:\Test.XLS" 'File to open
.Visible = False 'Can be set to false if you dont want to see Excel do its thing

My question, or where I need help is how do I create a brand new Excell output file? I would like to have my program think of a name and export the data to that name. I can't quite seem to grasp that one.

Thanks.

 

[boblarson]

First, test to see if it exists (this checks to see if it doesn't exist):

If Dir(NewFileName)= "" Then


and then

With ExcelApp
.Workbooks.Add

 

[Daveyk01]

First, test to see if it exists (this checks to see if it doesn't exist):

If Dir(NewFileName)= "" Then


and then

With ExcelApp
.Workbooks.Add

Thanks Bob. I swear I thought that maybe I tried that, but I probably had something slightly wrong <g>. I will try that very soon.

Thanks again.