Save as with VBA

[dzon_doe]

Hi,

I wrote the save as macro:

Sub actionSaveFile()

Dim nazwa As String

nazwa = Sheets("Dane").Range("AF1")

    sFName = Application.GetSaveAsFilename(InitialFileName:=nazwa, fileFilter:="XLSM (*.xlsm), *.xslm")
    
    If sFName <> False Then
    ActiveWorkbook.SaveAs sFName, 52
    End If
    
End Sub

However, I noticed that macro is saving files even if there is a file with the same name. How to prevent this and force excel to show the prompt for a file name change?

Kind regards,
dzon_doe

 

[Anthony47]

Try this version:

Sub actionSaveFile()
Dim nazwa As String
'
nazwa = Sheets("Dane").Range("AF1")
'
ReFile:
    sfname = Application.GetSaveAsFilename(InitialFileName:=nazwa, fileFilter:="XLSM (*.xlsm), *.xslm")
    If sfname <> False Then
        If Dir(sfname) = "" Then
            ActiveWorkbook.SaveAs sfname, 52
        Else
            MsgBox ("File Already exists, make a different choice")
            GoTo ReFile
        End If
    End If
End Sub

Bye