Save a file without overwriting an existing file

Question

I need to rewrite this macro so it won't overwrite the file. I tried various solutions, but I can't get them to work.

Here is the macro I have written so far:

Sub email_workbook()     
    
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    TempFilePath = Environ$("temp") & "\"
    TempFileName = Range("H22") & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "person1@PLACE.COM"
        .CC = "MPERSON@PLACE.COM" & " " & "LPERSON@PLACE.COM"
        .BCC = ""
        .Subject = "SUBJECT" & Range("H22")
        .Body = "Please review ETC.ETC."
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        .Display
    End With
    On Error GoTo 0
        
    Dim myFile As String

    myFile = ActiveWorkbook.Name
       
    Application.DisplayAlerts = False ' Disregard overwriting message.
    ActiveWorkbook.SaveAs Filename:="U:\Public\WAKKA\WAKKAWAKKA - To Review"
 
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Call SaveFileExcel

End Sub

Sub SaveFileExcel() 
    Dim path As String 
    Dim filename1 As String

    path = "U:\Public\WAKKA - WAKKAWAKKA"
    filename1 = Range("W1").Text 
    Application.DisplayAlerts = True

    'If Dir("f:ull\path\with\filename.xls") <> "" Then 
    '    Kill "f:ull\path\with\filename.xls"
    'End If ActiveWorkbook.SaveAs 

    Filename:=path & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
End Sub

The format with which the file name is created is important for downstream usage, so a timestamp (with actual time) isn't an option.

How can I add a "-2" or "-3", etc. to the end of the file name?

Sub SaveFileExcel() Dim path As String Dim filename1 As String path = "U:\Public\WAKKA - WAKKAWAKKA" filename1 = Range("W1").Text Application.DisplayAlerts = True 'If Dir("f:ull\path\with\filename.xls") <> "" Then ' Kill "f:ull\path\with\filename.xls" 'End If ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True End Sub — logan, May 23 '19 at 13:16
Don't put code in a comment. If you need to show more code or update what you've shown, then edit your post. — lurker, May 23 '19 at 13:23
Welcome to SO. The only way to avoid overwriting a file is to save it in a different location or with a different name. — Foxfire And Burns And Burns, May 23 '19 at 13:56
Thank you for your suggestions, but the format in which the file name is created is important for downstream usage, so a timestamp (With actual time) isn't an option - does anyone have a simple insert that can add a "-2" or "-3", etc. to the end of the file name? — logan, May 28 '19 at 20:37

score 1 · Answer 1 · answered May 23 '19 at 13:44

1

You need to decide what the new name would be if the file already exists... adding a timestamp to the filename usually helps for keeping it unique.

Just reusing your code:

Sub SaveFileExcel()
Dim path As String
Dim filename1 As String
path = "U:\Public\WAKKA - WAKKAWAKKA"
filename1 = Range("W1").Text
Application.DisplayAlerts = True

If Not Dir(path & filename1 & ".xlsm") <> "" Then
    filename1 = filename1 & "file_already_exists_with_same_name"
End If

ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Application.DisplayAlerts = True
End Sub

answered May 23 '19 at 13:44

FAB

2,505
1
10
21

Thank you for your suggestions, but the format in which the file name is created is important for downstream usage, so a timestamp (With actual time) isn't an option - does anyone have a simple insert that can add a "-2" or "-3", etc. to the end of the file name? – logan May 28 '19 at 20:41
I think my answer is adequate for your initial question, what you are asking now is a different question and has already been answered on SO before, see [VBA: Saving without overwriting existing files](https://stackoverflow.com/questions/52492504/vba-saving-without-overwriting-existing-files) – FAB May 28 '19 at 20:51

Save a file without overwriting an existing file

1 Answers1