If file exists save as with timestamp

Question

I am trying to get the following to work in my code. I was the macro to svae the file and if it already exists then to save as with an additional timestamp at the end. I have the following but it stops at "ActiveWorkbook.SaveAs (FPath & "\" & FName & Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss") & "xlsx")" Any suggestions what I am doing wrong?

'~~> Save the file

Dim TestStr As String

TestStr = ""

On Error Resume Next
TestStr = Dir(FPath & "\" & FName & ".xlsx")

On Error GoTo 0
If TestStr = "" Then
MsgBox "New file name: " & FName & ".xlsx"
ActiveWorkbook.SaveAs filename:=FPath & "\" & FName & ".xlsx", FileFormat:=51
Workbooks(FName & ".xlsx").Close

'~~> Mark as saved
Workbooks("Sample.xlsm").Worksheets("Front Page").Cells(Application.Match("Vendor", Worksheets("Front Page").Range("A1:A42"), 0), 3).Value = "OK"

Else
MsgBox "File " & FName & ".xslx" & " already exist." & vbCrLf & "New vesion saved with timestamp."
ActiveWorkbook.SaveAs (FPath & "\" & FName & Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss") & "xlsx")
Workbooks(FName & Format(Now(), "yyyy-MM-dd hh:mm:ss") & ".xlsx").Close

End If

Exit Sub

what do you mean "it stops"? Errors? What have you tried? There's tips in "minimal reproducible example" as also how to create a minimal reproducible example. Also a handy checklist from the site's top user here. — ashleedawg
Also, On Error Resume Next should generally be avoided - and especially when troubleshooting a problem - since you're telling VBA that you don't even want to know about errors/mistakes. It's beneficial to add Option Explicit as the first line of [every] module [always] to help ensure variables are properly declared/handled, this preventing mamy potential issues. — ashleedawg

Xabier Xabier · Accepted Answer · 2018-08-29T09:58:47

You can't have : in filenames also you were missing a full stop Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss") & "xlsx") before the "xlsx" so the following will achieve what you expect:

Dim TestStr As String

TestStr = ""

On Error Resume Next
TestStr = Dir(FPath & "\" & FName & ".xlsx")

On Error GoTo 0
If TestStr = "" Then
    MsgBox "New file name: " & FName & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".xlsx", FileFormat:=51
    Workbooks(FName & ".xlsx").Close

    '~~> Mark as saved
    Workbooks("Sample.xlsm").Worksheets("Front Page").Cells(Application.Match("Vendor", Worksheets("Front Page").Range("A1:A42"), 0), 3).Value = "OK"
Else
    MsgBox "File " & FName & ".xslx" & " already exist." & vbCrLf & "New vesion saved with timestamp."
    ActiveWorkbook.SaveAs (FPath & "\" & FName & Format(DateTime.Now, "yyyy-MM-dd hh-mm-ss") & ".xlsx")
    Workbooks(FName & Format(Now(), "yyyy-MM-dd hh-mm-ss") & ".xlsx").Close
End If

Exit Sub

If file exists save as with timestamp

1 Answers