vba - message boxes and overwriting files

[Ron512]

I need help working through an issue with message boxes and overwriting files.

The below code is intended to check for an existing file. If a file exists then a yes/no message box opens. If yes is selected the file is overwritten, if no is selected a message box opens and the sub ends.

It works correctly if yes is selected but in no is selected it passes over the statements in the Else.

If Dir(WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name) <> "" Then
    If MsgBox("The file, " & save_name & " exists, do you want to overwite it?", vbYesNo + vbQuestion) = vbYes Then
    'overwrite the file
    wb.SaveAs Filename:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51
    End If
Else
    'exit
    MsgBox "Ensure the data is currect." & vbCrLf & "The process will end.", vbOKOnly + vbExclamation
    Exit Sub
End If

Thanks

ron

 

[ArthriticPanda]

Ron,

Try this:

Sub msgTest()

If Dir(WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name) <> "" Then
    
    Ans = MsgBox("The file, " & save_name & " exists, do you want to overwite it?", vbYesNo + vbQuestion)
    
    Select Case Ans
        Case vbYes
            'overwrite the file
            wb.SaveAs Filename:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51
        Case vbNo
            'exit
            MsgBox "Ensure the data is correct." & vbCrLf & "The process will end.", vbOKOnly + vbExclamation
            Exit Sub
        End Select
End If

End Sub

 

[Ron512]

Panda

Thanks for the quick reply. Your suggestion worked great. I added an Else so the file is saved if there was no existing file. Was that the correct way to handle it.

If Dir(WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name) <> "" Then
    
    Ans = MsgBox("The file, " & save_name & " exists, do you want to overwite it?", vbYesNo + vbQuestion)
    
    Select Case Ans
        Case vbYes
            'overwrite the file
            wb.SaveAs Filename:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51
        Case vbNo
            'exit
            MsgBox "Ensure the data is correct." & vbCrLf & "The process will end.", vbOKOnly + vbExclamation
            Exit Sub
        End Select
Else
    wb.SaveAs Filename:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51

End If

Ron

 

[ArthriticPanda]

Yes that would be perfectly acceptable Ron.