Adding Time Stamp to File Save

I have been using the following file SaveAs process, but recently found that when saving if someone else has the file open, it fails.  How would I add a time stamp to the file save as to prevent this from happening?  I have some cases were I was overwritting the previous file with the new current file, but see that is not going to work.


Sub SaveFileToWDrive()
Dim FName As String
'
 FName = "W:\Holland\rrw5\Bruce Shortage Report "
 FName = FName & Format(Date, "ddmmmyy") & ".xlsx"
    'ChDir "W:\Holland\rrw5"
    ActiveWorkbook.SaveAs Filename:= _
        FName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    MsgBox ("File Saved to " & FName)
    
End Sub

Open in new window

RWayneHAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

pls try

Format(Date, "ddmmmyy") & "_" & Format(Time, "HHMM")

Open in new window

Regards
0
 
RWayneHAuthor Commented:
I may have got this to work, however I need a few more days to test.
0
 
RWayneHAuthor Commented:
Thanks for the help -R-
0
All Courses

From novice to tech pro — start learning today.