Saving file to its current location and current file name with current date and "FINAL"

[ironwill20]

I am using 2010 excel and pretty new with VBA codes. I've been looking for a code to save a workbook to its current location and on its current file name but must add the current date and the word FINAL. For example Myfile_08202012_FINAL.xlsx

I have no luck finding the code. Appreciate any help!

[HaHoBe]

Hi, ironwill20,

*.xlsx will not support macros. It could be done like

With ActiveWorkbook
  .SaveAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_FINAL", FileFormat:=51
End With

Ciao,
Holger

[ironwill20]

Holger....Wow...thanks it really does work.. thanks again.. can I ask a another one? What would be the code If i just wanted to create a backup copy in the same location with this filename --- "Myfile_08202012_backup". I don't want my active workbook to save into this backup filename as I'd like to use the code the one for "FINAL". Basically, I just need a macro the saves a backup so when I run the final one and locked the entire workbook, I know I have a backup copy which is unlocked/unprotected. Hope this is clear...

this is the code i hav

With ActiveWorkbook
  .SaveAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_backup", FileFormat:=51
    End With


    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect Password:="password"
        Next ws
        
    With ActiveWorkbook
  .SaveAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_FINAL", FileFormat:=51
    End With

The proplem is when it ran the Final save... the filename becomes like this "myfile_08202012_backup_08202012_FINAL.xlsx" I just need the final one say myfile_08202012_FINAL.xlsx

Hi, ironwill20,

*.xlsx will not support macros. It could be done like

With ActiveWorkbook
  .SaveAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_FINAL", FileFormat:=51
End With

Ciao,
Holger

[HaHoBe]

Hi, ironwill20,

if you want to save the backup as *.xlsx as well itīs a way of using a variable to store the name and open that file again. VBA offers the SaveCopyAs which will leave you in th file and not alter that name but also that will not alter the extension of the file (in ,my case itīs *.xlsm). Please be careful with Application.DisplayAlerts as it will turn off all alarms (workbook not saved, no message when deleting worksheets etc.), so in case of a run-time error please restore this feature:

Application.DisplayAlerts = False
With ActiveWorkbook
  .SaveCopyAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_backup"
End With


For Each ws In ActiveWorkbook.Worksheets
    ws.Protect Password:="password"
Next ws
    
With ActiveWorkbook
  .SaveAs .Path & "\" & Left(.Name, Len(.Name) - 5) & Format(Date, "_yyyymmdd") & "_FINAL", FileFormat:=51
End With
Application.DisplayAlerts = True

Caio,
Holger