CODEWITHSUNDEEP
excelvba
Patrick Honey
Patrick Honey

Reputation: 3

Save under current file name with date attached

I have several Excel files to save under their current file name with today's date attached but in a different folder.

As these files all have different names my approach is get their file name as a variable and use it for the name to save under. Since I want to avoid writing a macro for each file, the idea of putting the desired name into one cell and then using that value (which works) for saving it, is not an option.

Sub Save_Workbook()

' Save Sheet with Current Date
' Adjust Absolute Path to Location Where to Save the Workbook

Dim FName As String

FName = ActiveWorkbook.Name

ActiveWorkbook.SaveAs ("C:\Users\ME\FOLDER1\FOLDER2\SHEETS1\SHEETS2\ FName " & Format(Now(), "DD-MM-YYYY" & ".xlsx"))

End Sub

This macro saves the file in the desired folder and with the date attached, but it does not pick up the self-defined variable FName. The resulting file name is FName 29-09-2018 for example.

Upvotes: 0

Views: 7083

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

You've included FName within the string so it's being treated as just text.

ActiveWorkbook.SaveAs ("C:\Users\ME\FOLDER1\FOLDER2\SHEETS1\SHEETS2\ FName " & Format(Now(), "DD-MM-YYYY" & ".xlsx"))

should be 

ActiveWorkbook.SaveAs ("C:\Users\ME\FOLDER1\FOLDER2\SHEETS1\SHEETS2\" & FName & Format(Now(), "DD-MM-YYYY" & ".xlsx"))

or you could remove FName completely and use the ActiveWorkbook.Name: 

ActiveWorkbook.SaveAs ("C:\Users\ME\FOLDER1\FOLDER2\SHEETS1\SHEETS2\" & ActiveWorkbook.Name & Format(Now(), "DD-MM-YYYY" & ".xlsx"))

You may want to add a space between FName and the date. Rather than include an extra & " " & next to Fname you could use: 

Format(Now(), " DD-MM-YYYY" & ".xlsx")

This adds the extra space in the date formatting.

Upvotes: 1

Related Questions