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

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

Allowing xlBook.SaveAs in Access code to overwrite existingworkbook?
 

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

On Jan 26, 7:54*am, Ed from AZ wrote:
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

Allowing xlBook.SaveAs in Access code to overwrite existingworkbook?
 

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.

Ed from AZ wrote:

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

On Jan 26, 7:54 am, Ed from AZ wrote:
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

--

Dave Peterson