Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Update filename with the same name with date and time

0

Dear Sirs,

I have the following  saveit Macro to safe file with date and time. 

It work perfectly.

How to develop macro that can be used for any file, without the need to define the workbook  name before running the macro.

Refer to the code 

wbNam = "OttoNIE_" -------> wbName = existing file name

Best regards

Arsil Hadjar

 Sub SaveIt()
Dim dt As String, wbNam As String
wbNam = "OttoNIE_"
'dt = Format(CStr(Now), "yyy_mm_dd_hh_mm")
dt = Format(CStr(Now), "dd_mmm_yyyy_hh_mm")
ActiveWorkbook.SaveAs FileName:=wbNam & dt, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
End Sub
Answer
Discuss

Answers

0
Selected Answer

Please try this code.

Sub SaveIt()
    ' 24 May 2018
    
    Dim WbName As String
    Dim Sp() As String
    Dim Tmp As String
    
    ' split a name like "TXL 180126 General 2018-05-24_1644.xlsx"
    '              into "TXL 180126 General " and "-05-24_1644.xlsx"
    Sp = Split(ActiveWorkbook.Name, CStr(Year(Date)))
    WbName = Sp(0)
    ' if the name doesn't include a date remove the file extension:-
    If UBound(Sp) = 0 Then
        Sp = Split(WbName, ".")
        ReDim Preserve Sp(UBound(Sp) - 1)
        WbName = Join(Sp, ".")
    End If
    
    Tmp = Right(WbName, 1)
    ' if WbName doesn't end on space or underscore, append a space
    If InStr(" _", Tmp) = 0 Then WbName = WbName & " "
    
    ' modify the intervening characters as required
    ' but avoid characters not allowed in file names:-
    Tmp = Format(Now(), "yyyy-mm-dd hhmm")
    ' the 'FileFormat' instruction will add back the extension.
    ' (It may be different form what it was originally.)
    ActiveWorkbook.SaveAs Filename:=WbName & Tmp, _
                          FileFormat:=xlOpenXMLWorkbook, _
                          CreateBackup:=False
End Sub

I presumed that the workbook's file name might already include a date and time and, since I changed the date/time format, that it might not have the same format created by this code, either. The code should work under all these eventualities. Please test it thoroughly before deployment.

Discuss

Discussion

Dear Variatus,
Many thanks for the solution, it work perfectly

Best Regards
Arsil Hadjar
Arsil (rep: 32) May 24, '18 at 8:31 pm
Hello Arsil, I'm glad it worked for you. Please be sure to "Select" the answer. Thank you.
Variatus (rep: 4889) May 24, '18 at 10:05 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login