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

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 <prof_ofw...@yahoo.com> 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

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 <prof_ofw...@yahoo.com> 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