Automatically Overwrite File/Avoid Prompt 1

[finny]

I am creating excel spreadsheets through a web application.
I would like to be able to overwrite a file if it already exists. Can someone help me out with some code that would do this. I have searched and found nothing that looks like it will allow me to do this.

The SaveAs method doesn't look like it includes a default for an overwrite.

Any help would be greatly appreciated.

Thx in advance...finny

 

[DarkSun]

The way I do this is to check if the file exists using this code.

Private Sub FileExists()
Dim strFilename As String

strFilename = "C:\officemacros\test.tst"

If Dir(strFilename, vbDirectory) <> &quot;&quot; Then
MsgBox (&quot;The File &quot; + strFilename + &quot; exists.&quot
Else
MsgBox (&quot;The File &quot; + strFilename + &quot; doesn't exist.&quot
End If

End Sub

You would just replace the msgbox with the code you want ot run.

eg. replace MsgBox (&quot;The File &quot; + strFilename + &quot; exists.&quot

with something like:

FileSystem.Kill strFilename

then you can use SaveAs

 

[finny]

Darksun,
thx for the reply. I will use your method. I already check if the file exists in another module. I think I was hoping for a quick and easy arguement off the the SaveAs method.

Appreciate your response.

 

[DarkSun]

Another way to do it is to turn off alerts.

Application.DisplayAlerts = False

then use SaveAs

then Application.DisplayAlerts = True

This should do what you want.

 

[finny]

aha! Thx Darksun.

I coded for your fist suggestion and it worked fine. Do you know of any advantages/disadvantages between the two methods?

thx...finny

 

[DarkSun]

The second Method:

Disadvantages
1.Doesn't let you know if the file exists.
2.Doesn't give you a choice of what to do if file exists.
3.Will automatically overwrite the file.

Advantages
1.Quick and easy if you want to replace the existing file.

Just reverse the above for advantages/disadvantages for first method.

 

[finny]

Darksun,
thx for all you help and info.

I Appreciate it.


Finny