Checking if file exists in VBA

[NADavies]

My issue is with Exel 2000, more used to Access than Exel
so bear with me!

Have the following simple code to save a spreadsheet which
works fine as long as the file name being saved to doesn't
already exist.

ActiveWorkbook.SaveAs Filename:= "C:\Test.XLS", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Really need to handle this gracefully and not just return
an error code to the user.

Any help would be appreaciated.

Regards

Nigel

 

[Tom Ogilvy]

If you want to overwrite the existing file without prompt:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= "C:\Test.XLS", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True

or
On Error Resume Next
Kill "C:\Test.xls"
On Error goto 0
ActiveWorkbook.SaveAs Filename:= "C:\Test.XLS", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False


Or if you want to not save if it exists
if dir("C:\Test.xls") <> "" then
msgbox "File exists, quitting"
exit sub
End if
ActiveWorkbook.SaveAs Filename:= "C:\Test.XLS", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[Jim Rech]

To force an overwrite in all cases use Application.DisplayAlerts = False
before the save.

 

[pfsardella]

See John Walkenbach's FileExists function.

http://j-walk.com/ss/excel/tips/tip54.htm

HTH
Paul