Allowing xlBook.SaveAs in Access code to overwrite existing workbook?

[Ed from AZ]

I'm making my first forays into Access with code. As part of a data
export sub, I create an instance of Excel and add a new workbook. I
create the name of the workbook with the date in it. As I'm testing
this, Excel keeps giving me a warning that a workbook already exists.
(All this is in Excel and Access 2003, by the way.)

How can I write the code in Access to allow Excel to overwrite the
previous file in the SaveAs?

Ed

 

[Ed from AZ]

Never mind - I took a different route. I use DIR to test for the file
name -if it exists, I add a number to it to make a new file name.

Ed

 

[Dave Peterson]

That is one way.

Another way would be to use:

xlapp.displayalerts = false
'do the saveas
xlapp.displayalerts = true

where xlapp is an object variable that represents your instance of excel.

Another alternative (maybe) would be to include the date and time:

dim myFilename as string
myfilename = "c:\somefolder\someprefix" & format(now,"yyyymmdd_hhmmss") & ".xls"

I don't think I've ever saved a file within the same second.