如何在 Excel 中使用 VBA 将日期和时间添加到文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20635629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Add Date and Time To File Name Using VBA in Excel
提问by Behseini
Thanks to Siddharth Rout at this PostI 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
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?
你能告诉我怎么做吗？
回答by Siddharth Rout
Change
改变
FName = "C:\Users\somebody\Documents\TestSheet" & _
Format(Range("E19"), "mmm-d-yyyy") & ".xlsm"
to
到
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"
回答by simpLE MAn
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.
请注意，如果您希望时间四舍五入而不是四舍五入Round()，Int()则可以更改。此外，请注意您将在其上运行的 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
回答by Mike
I have the following working well but would like to reverse the order. File name first, date and time second. So far have not figured out a way.
我有以下工作正常，但想颠倒顺序。文件名第一，日期和时间第二。到现在也没想出办法。
Sub SaveToLocations()
' Saves active file to current plus two other backup locations, appends system date and time in front of file name in backup locations.
Dim datim As String
datim = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss_")
ActiveWorkbook.SaveCopyAs "I:\FilesBackup\" & datim & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs "E:\CS Docs\FilesBackupCS\" & datim & ActiveWorkbook.Name
ActiveWorkbook.Save
End Sub
