How can I add the date to the excel file title with vba code?-CodePudding

Good morning,

Currently I this code below to create from sheet 5 some Excel files with same name at sheet. However I would like to add current date as: "List AA 30.03.2022".

 Sub EXCELS()

'Create excel files

 Dim i As Integer
 Dim name_file As String
 For i = 5 To Sheets.Count
 name_file = Sheets(i).Name

 Worksheets(i).Copy

 With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & name_file & ".xlsx", 
 FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
Next i


End Sub

What do I need to add?

CodePudding user response：

Try this :

ThisWorkbook.Path & "\" & name_file & " " & Format(Date, "DD.MM.YYYY") & ".xlsx"

CodePudding user response：

Replace the line .SaveAs Filename:=ThisWorkbook.Path & "\" & name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook with

.SaveAs Filename:=ThisWorkbook.Path & "\" & name_file & Format(Date, " dd.mm.yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook

EDIT
per your comment, in order to replace the file completely, you'll first need to save the old file name as a variable, then delete it afterwards.

So, replace the entire With block with the below;

With ActiveWorkbook
    'variable to store the old file name:
    Dim OldFileName as String
    'assign the file's current name to the variable:
    OldFileName = .FullName
    'Now save the file with it's new name, then close it:
    .SaveAs _
        Filename:=ThisWorkbook.Path & "\" & name_file & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook
    .Close
End With
'Get rid of the leftover file with the old name:
Kill OldFileName