如何在Excel中使用VBA向文件名添加日期和时间 [英] How to Add Date and Time To File Name Using VBA in Excel

问题描述

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

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:

TestSheet_25May2013_5pm.xls

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?

推荐答案

更改

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

到

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

如果您要从Range("E19")中选择日期，请确保该单元格具有有效的日期.在这种情况下，代码将变为

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

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

这篇关于如何在Excel中使用VBA向文件名添加日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！