代码之家  ›  专栏  ›  技术社区  ›  Marya

如何使用vba代码将日期添加到excel文件标题中?

  •  0
  • Marya  · 技术社区  · 3 年前

    早上好

    目前,我使用下面的代码从工作表5创建一些与工作表同名的Excel文件。然而,我想将当前日期添加为:“列出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
    

    我需要补充什么?

    2 回复  |  直到 3 年前
        1
  •  1
  •   CGDPaul    3 年前

    试试这个:

    ThisWorkbook.Path & "\" & name_file & " " & Format(Date, "DD.MM.YYYY") & ".xlsx"
    
        2
  •  1
  •   Spencer Barnes    3 年前

    更换线路 .SaveAs Filename:=ThisWorkbook.Path & "\" & name_file & ".xlsx", FileFormat:=xlOpenXMLWorkbook 具有

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

    编辑
    根据您的评论,为了完全替换文件,您首先需要将旧文件名保存为变量,然后将其删除。

    所以,把整个 With 用下面的代码阻塞;

    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 
    
    推荐文章