CODEWITHSUNDEEP
excelexcel-2010vba
AMM
AMM

Reputation: 135

VB Save to current path and filename, plus add current date to filename

I am very, very new to VB and am grateful to have found this site!

I searched and reviewed every auto-suggested similar Q&A links for my question but didn't find exactly what I was looking for (perhaps because I didn't understand what I was looking at... ;-).

I used this Excel macro from another user's question, answered by Siddharth Rout:

Macro with Save Current Date

Sub Save()
    Dim FilePath As String
    Dim NewName As String

    FilePath = "X:\": NewName = FilePath & "file" & Format(Date, "MM-DD-YYYY") & ".xlsm"

    ActiveWorkbook.SaveAs Filename:=NewName, FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

This works perfectly if I want the file to be named "file" (or whatever I insert in the quotes).

But, how would I edit this macro so that it would work on any open file so that the current date is added to the file's existing path and filename?

Thanks in advance for your help!

Andy

Upvotes: 3

Views: 12136

Answers (1)

GSerg
GSerg

Reputation: 78155

dim last_dot as long
last_dot = InStrRev(ActiveWorkbook.FullName, ".")

dim NewName as string
NewName = Left$(ActiveWorkbook.FullName, last_dot - 1) & Format$(Date, "MM-DD-YYYY") & Mid$(ActiveWorkbook.FullName, last_dot)

Upvotes: 3

Related Questions