VBA code to check if user-input filename is a duplicate

Question

VBA newbie here. What I'm trying to do: Write a macro that brings up the Save As box with a filename pre-filled in, which the end user can edit if they choose. If the file name already exists in the destination folder, a dialog box appears asking if the user wants to overwrite that file. I've got it to mostly work.

My problem: the code, which I've pieced together and modified with help from different posts, currently only checks for a duplicate file name based on the initial file name, not what the user has actually typed in. So for example, if the prefilled filename is "NewFile_1", and the user changes this to something else, they will still get a dialog box asking if they want to overwrite "NewFile_1", despite that not being the name of the file to be saved.

Is there a way to have the code check for what the user has typed in rather than the pre-populated string used as the initial file name? I want to have a file name preloaded, but still provide the user the option to change it as well as the save location.

Here's my code:

Sub SaveTabAsTxt()

Dim FileSave As Variant
Dim SaveName As String

SaveName = Sheet4.Range("B3") & " Discounts - " & Sheet4.Range("B5")
    
SaveAsDialog:
FileSave = Application.GetSaveAsFilename(InitialFileName:=SaveName, FileFilter:="Text Files (*.txt), *.txt")
    If FileSave <> False Then
      If Len(Dir(FileSave)) Then 'checks if the file already exists
        Select Case MsgBox("A file named '" & FileSave & "' already exists at this location. Do you want to replace it?", vbYesNoCancel + vbInformation)
        Case vbYes
          'want to overwrite
          Application.DisplayAlerts = False
          wbkExport.SaveAs FileSave, ConflictResolution:=2, Addtomru:=True
          Application.DisplayAlerts = True
          Set SaveCurrentWorkbook = wbkExport
        Case vbNo
          GoTo SaveAsDialog
        Case vbCancel
          SaveCurrentWorkbook = False
          MsgBox "Save Canceled"
        End Select
      Else
        wbkExport.SaveAs FileName:=SaveName, FileFormat:=xlTextWindows
        Set SaveCurrentWorkbook = wbkExport
      End If
    Else
      SaveCurrentWorkbook = False
        MsgBox "Save Canceled"
    End If

End Sub

Sorry if I'm missing something obvious. I realize that this is messy and probably not the most efficient solution, but I'm learning as I go. I've scoured the internet and this is the only part of my code I haven't found an answer to. Many thanks in advance.

Answer 1

Using Enums to Track User's Choices

• The main issues with your code are covered in your comments by Tim Williams and CHill60.
• You could use an Enum to make the code more readable.
• Also, use VBA constants (which are members of Enums): xlLocalSessionChanges at least tells something, but 2 doesn't.
• You didn't post (any information about) the beginning or the end of the code, so I improvised but out-commented it.

Option Explicit

Private Enum eSaveDecision
    esSave = 1
    esCancelSave = vbCancel ' 2
    esOverwrite = vbYes ' 6
    esDontOverwrite = vbNo ' 7 - continue loop
    esCancelDialog = 9
End Enum

Sub SaveTabAsTxt()
    
'    ' Useful for testing!!!
'    ' Export sheet to a new workbook.
'    Sheet4.Copy
'    ' Create a reference to this new workbook.
'    Dim wbkExport As Workbook: Set wbkExport = Workbooks(Workbooks.Count)
    
    ' Retrieve the initial file name (a file name suggestion?).
    Dim SaveName As String: SaveName = Sheet4.Range("B3").Value _
        & " Discounts - " & Sheet4.Range("B5").Value

    ' Set the tracking variable to the value that keeps the execution
    ' of the code in the `Do...Loop`.     
    Dim SaveDecision As eSaveDecision: SaveDecision = esDontOverwrite
    
    ' Using the Enum, track the user's choice(s).
    Do
        ' Use a dialog to ask the user to select or input the file name.
        Dim SavePath As Variant: SavePath = Application.GetSaveAsFilename( _
            InitialFileName:=SaveName, _
            FileFilter:="Text Files (*.txt), *.txt")
        If VarType(SavePath) = vbString Then ' dialog not canceled
            ' Check if the file exists.
            If Len(Dir(SavePath)) Then ' file exists
                SaveDecision = MsgBox("A file named """ & SavePath _
                    & """ already exists at this location." & vbLf _
                    & "Do you want to replace it?", _
                    vbYesNoCancel + vbInformation + vbDefaultButton2)
            Else ' file doesn't exist
                SaveDecision = esSave
            End If
        Else ' dialog canceled
            SaveDecision = esCancelDialog
        End If
    Loop Until SaveDecision <> esDontOverwrite
    
    Dim WasFileSaved As Boolean
    
    ' Deal with the various cases.
    Select Case SaveDecision
        Case esSave
            wbkExport.SaveAs Filename:=SavePath, FileFormat:=xlTextWindows
            WasFileSaved = True
        Case esOverwrite
            Application.DisplayAlerts = False
                wbkExport.SaveAs Filename:=SavePath, _
                    ConflictResolution:=xlLocalSessionChanges, AddToMru:=True
            Application.DisplayAlerts = True
            WasFileSaved = True
        Case esCancelSave
            MsgBox "Saving canceled.", vbExclamation
        Case esCancelDialog
            MsgBox "Save dialog canceled.", vbExclamation
        Case Else ' at the moment seems impossible
            MsgBox "Unexpected result """ & SaveDecision & """!", vbCritical
    End Select
    
    ' Status:
    ' - 'ThisWorbook' (Sheet4.Parent), the one with the code, is open
    ' - 'wbkExport' is open
    ' - 'WasFileSaved' is self explanatory
    ' - 'SaveDecision' isn't interesting anymore except for testing
    Debug.Print ThisWorkbook.Name, wbkExport.Name, WasFileSaved, SaveDecision
    
'    ' Useful for testing; you don't want to end up 
'    ' with a bunch of unsaved workbooks.
'    On Error Resume Next
'        wbkExport.Close SaveChanges:=False
'    On Error GoTo 0
    
End Sub