saveas method

[Vince]

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

 

[Helmut Weber]

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.

???

 

[Andrew]

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

 

[Vince]

Hey Helmut / Andrew,

Thanks plenty for your responses.

I tried Andrew's method and it works great.

Thanks, again.

Vince