Checking if file exists in VBA

  • Thread starter Thread starter NADavies
  • Start date Start date
N

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
 
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
 
To force an overwrite in all cases use Application.DisplayAlerts = False
before the save.
 
Back
Top