Save file in new location.

[Nanogirl21]

Is there a VBA code that I can add the end of my macro that will save the file with todays date (YYYY-MM-DD format) at a specific location? The location is different than the orginal document.

Location: C:\Users\nanogirl\Desktop\2018 WIP\Reports
File Name Example: Metrics 2018-05-24.xlsb

Thank You

 

[John_w]

Re: Save file in new locaiton.

Assuming the active workbook name is currently "Group Lunch.xlsb" (without a date) then this will save it as "Group Lunch 2018-05-30.xlsb" in the specified folder.

    Dim p As Long, fileName As String
    
    With ActiveWorkbook
        p = InStrRev(.Name, ".")
        fileName = "C:\path\to\folder\" & Left(.Name, p - 1) & Format(Date, " yyyy-mm-dd") & Mid(.Name, p)
        .SaveAs fileName
    End With

 

[Nanogirl21]

Re: Save file in new locaiton.

Assuming the active workbook name is currently "Group Lunch.xlsb" (without a date) then this will save it as "Group Lunch 2018-05-30.xlsb" in the specified folder.

    Dim p As Long, fileName As String
    
    With ActiveWorkbook
        p = InStrRev(.Name, ".")
        fileName = "C:\path\to\folder\" & Left(.Name, p - 1) & Format(Date, " yyyy-mm-dd") & Mid(.Name, p)
        .SaveAs fileName
    End With

Worked Perfectly! Thank you.