saveas method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vince - 09 Jan 2005 13:40 GMT

Hey all,

This could be an easy question but I can't seem to solve it yet.

In my VB program, I am making a reference to the Office Library to use the
Excel methods. Everything works fine and I save the file using

Workbook.SaveAs TheFileName

This statement brings up the confirmation box if the file already exists.
However, I do not want it do that and just overwrite the file, if it already
exists. How do I do this? I do not want to delete the file before running
this because, sometimes, I have to add a new worksheet to an existing file
and cannot delete the old worksheets.

Thanks for your time / any response.

Vince

Reply to this Message

Helmut Weber - 09 Jan 2005 14:22 GMT

Hi Vince,
you want to save an Excel-file under
the name of an already existing Excel-file.
You don't want a warning.
You want to just overwrite the file,
but Excel doesn't allow that, then,

as you don't want to delete the target file beforehand,
maybe for safety reasons, as in case of a crash all would be lost,

I'd say, what is left would be to check, whether a file
with the name of the target file already exists.
If so, rename it. After that, the target file doesn't exist anymore,
and overwriting it would not be a question.
Save the actual file under the desired name.
Delete the renamed file.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

>This statement brings up the confirmation box if the file already exists.
>However, I do not want it do that and just overwrite the file, if it already
>exists. How do I do this? I do not want to delete the file before running
>this because, sometimes, I have to add a new worksheet to an existing file
>and cannot delete the old worksheets.

???

Reply to this Message

Andrew - 09 Jan 2005 19:40 GMT

Hi Vince,

You can turn the alert dialog boxes which Excel pops up
to warn you of events such as overwriting an existing
file.

With ExcelAppRef
.Application.DisplayAlerts = False
.Workbook.SaveAs = TheFileName
.Application.DisplayAlerts = True
End With

There is a similar method in Word

Cheers,
Andrew

>-----Original Message-----
>Hey all,
[quoted text clipped - 17 lines]
>
>.

Reply to this Message

Vince - 09 Jan 2005 22:53 GMT

Hey Helmut / Andrew,

Thanks plenty for your responses.

I tried Andrew's method and it works great.

Thanks, again.

Vince

> Hi Vince,
>
[quoted text clipped - 41 lines]
> >
> >.

Reply to this Message