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.

• Tim Williams: Else: wbkExport.SaveAs FileName:=SaveName should be Else: wbkExport.SaveAs FileName:=FileSave otherwise you're ignoring the name the user selected from the call to GetSaveAsFilename Before calling GoTo SaveAsDialog I would set SaveName to FileSave - that would be easier for your user I think.
• CHill60: Another observation - you have SaveCurrentWorkbook = False and Set SaveCurrentWorkbook = wbkExport. Is SaveCurrentWorkbook meant to be a boolean or a workbook? I suspect you should have it as a boolean to indicate success in saving - in which case you could convert SaveTabAsTxt to a function and return the value. You already have a copy of the workbook in wbkExport, no need for another instance
• Tim Williams: Also worth noting there is a Worksheet.SaveAs method, which might be safer in this context than relying on a particular sheet being active when using Workbook.SaveAs

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