PDA

View Full Version : VBA code to check if file exixt and if yes than overwrite and no than exit



redjoy
03-10-2012, 08:27 PM
Please help me with my code, the situation is if file exist than overwrite the file and if it doesn't than exit.

redjoy
03-10-2012, 08:30 PM
my code is
Private Sub cmdsaveas_Click()
FNAME = Sheet2.Range("B4")
spath = "C:\Users\Public\Documents\Document_Test\"
If Dir(spath, vbDirectory) = vbNullString Then
MkDir spath
End If
ThisWorkbook.SaveAs Filename:=spath & FNAME + ".xls"
msg = "Your report was saved as" + " " + FNAME + ".xls"
MsgBox msg
End Sub

So here if the file already exist than I get an Windowns prompt that if you want to overwrite the file, so here if I press "No" or "cancel" than it gives me a error.

Please help me with this :(

Crocus Crow
03-11-2012, 10:44 AM
if file exist then overwrite the file and if it doesn't then exit.Private Sub cmdsaveas_Click()
Dim Fname As String, spath As String

Fname = Sheet2.Range("B4").Value & ".xls"
spath = "C:\Users\Public\Documents\Document_Test\"
If Dir(spath, vbDirectory) = vbNullString Then
MkDir spath
End If
If Dir(spath & Fname) <> "" Then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=spath & Fname
Application.DisplayAlerts = True
MsgBox "Your report was saved as " & Fname
End If
End Sub