如何在Excel中使用VBA将日期和时间添加到文件名

Thanks to Siddharth Rout at this Post I learned how to save a sheet to a new Worksheet. Now my question is how I can add Date and Time of file creation like:

感谢Siddharth Rout在这篇文章中我学会了如何将工作表保存到新的工作表中。现在我的问题是我如何添加文件创建的日期和时间,如:

TestSheet_25May2013_5pm.xls

Sub SaveSheet()
    Dim FName As String

    ActiveSheet.Copy
    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False


    FName = "C:\Users\somebody\Documents\TestSheet" & Format(Range("E19"), "mmm-d-yyyy") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=FName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled



End Sub

can you please let me know how to do this?

你能告诉我怎么做吗?

2 个解决方案

#1

Change

FName = "C:\Users\somebody\Documents\TestSheet" & _
        Format(Range("E19"), "mmm-d-yyyy") & ".xlsm"

FName = "C:\Users\somebody\Documents\TestSheet_" & _
        Format(Date, "ddmmmyyyy") & ".xlsm"

If you are picking the date from Range("E19") then ensure that the cell has a valid date.. In such a case the code becomes

如果从Range(“E19”)中选择日期,则确保单元格具有有效日期。在这种情况下,代码变为

FName = "C:\Users\somebody\Documents\TestSheet_" & _
        Format(Range("E19"), "ddmmmyyyy") & ".xlsm"

#2

To complete Siddharth's solution, here is the code to also include the time in the file name:

要完成Siddharth的解决方案,这里的代码还包括文件名中的时间:

Function SaveDateTime() as String

    Dim SaveTime As Integer
    SaveTime = Round(Timer / 3600, 0)

    Dim AMPM As String: AMPM = "AM"
    If SaveTime >= 12 Then
        AMPM = "PM"
        If SaveTime > 12 Then
            SaveTime = SaveTime - 12
        End If
    End If

    SaveDateTime = "C:\Users\somebody\Documents\TestSheet_" & _
                    Format(Date, "ddmmmyyyy") & "_" & _
                    SaveTime & AMPM & ".xlsm"

End Function

Note that you could change Round() by Int() if you want the time to round down instead of just round. And also, pay attention on the language settings on the PC you will run this on because the date format depends on it.

请注意,如果您希望时间向下舍入而不是仅舍入,则可以通过Int()更改Round()。此外,请注意您将在此处运行的PC上的语言设置,因为日期格式取决于它。

Edited: Even simpler solution

编辑:更简单的解决方案

Function SaveDateTime() as String

    SaveDateTime = "C:\Users\somebody\Documents\TestSheet_" & _
                    Format(Date, "ddmmmyyyy") & "_" & _
                    Format(Time, "hAM/PM") & ".xlsm"

End Function

#1