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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed from AZ - 26 Jan 2008 14:54 GMT

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

Reply to this Message

Ed from AZ - 26 Jan 2008 20:10 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> Ed

Reply to this Message

Dave Peterson - 26 Jan 2008 20:16 GMT

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.

> 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.
[quoted text clipped - 11 lines]
> >
> > Ed

Signature

Dave Peterson

Reply to this Message