如何在 vba 代码中执行“另存为”，用日期戳保存我当前的 Excel 工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17173898/
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 do a "Save As" in vba code, saving my current Excel workbook with datestamp?
提问by Analytic Lunatic
I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date.
我有一个 Excel 工作簿，在表单按钮上单击我想保存工作簿的副本，文件名是当前日期。
I keep trying the the following
ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(Now(), 10))but am receiving Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed.
我一直在尝试以下
ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(Now(), 10))但收到Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed.
Can anyone assist me with this? I'm still very new to developing for Excel.
任何人都可以帮助我吗？我对 Excel 开发仍然很陌生。
回答by chancea
Most likely the path you are trying to access does not exist. It seems you are trying to save to a relative location and you do not have an file extension in that string. If you need to use relative paths you can parse the path from ActiveWorkbook.FullName
您尝试访问的路径很可能不存在。您似乎正在尝试保存到相对位置，并且该字符串中没有文件扩展名。如果您需要使用相对路径，您可以从ActiveWorkbook.FullName
EDIT: Better syntax would also be
编辑：更好的语法也将是
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
回答by Anders Christensen
Easiest way to use this function is to start by 'Recording a Macro'. Once you start recording, save the file to the location you want, with the name you want, and then of course set the file type, most likely 'Excel Macro Enabled Workbook' ~ 'XLSM'
使用此功能的最简单方法是从“录制宏”开始。开始录制后，使用您想要的名称将文件保存到您想要的位置，然后当然设置文件类型，最有可能是“Excel Macro Enabled Workbook”~“XLSM”
Stop recording and you can start inspecting your code.
停止记录，您可以开始检查您的代码。
I wrote the code below which allows you to save a workbook using the path where the file was originally located, naming it as "Event [date in cell "A1"]"
我编写了下面的代码，它允许您使用文件最初所在的路径保存工作簿，并将其命名为“事件[单元格“A1”中的日期]”
Option Explicit
Sub SaveFile()
Dim fdate As Date
Dim fname As String
Dim path As String
fdate = Range("A1").Value
path = Application.ActiveWorkbook.path
If fdate > 0 Then
fname = "Event " & fdate
Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Else
MsgBox "Chose a date for the event", vbOKOnly
End If
End Sub
Copy the code into a new module and then write a date in cell "A1" e.g. 01-01-2016 -> assign the sub to a button and run. [Note] you need to make a save file before this script will work, because a new workbook is saved to the default autosave location!
将代码复制到新模块中，然后在单元格“A1”中写入日期，例如 01-01-2016 -> 将子分配给按钮并运行。[注意] 您需要在此脚本工作之前制作一个保存文件，因为新工作簿已保存到默认的自动保存位置！
回答by riderBill
It could be that your default format doesn't match the file extension. You should specify the file format along with the filename, making sure the format matches the extension:
可能是您的默认格式与文件扩展名不匹配。您应该指定文件格式和文件名，确保格式与扩展名匹配：
With someWorkbook
.SaveAs "C:\someDirector\Awesome.xlsm", fileformat:=xlOpenXMLWorkbookMacroEnabled
End With
OTOH, I don't see an extension on your .SaveAs filename. Maybe you need to supply one when doing this programmatically. That makes sense--not having to supply an extension from the GUI interface is convenient, but we programmers are expected to write unambiguous code. I suggest adding the extension and the matching format. See this msdn pagefor a list of file formats. To be honest, I don't recognize a lot o the descripions.
OTOH，我没有在您的 .SaveAs 文件名上看到扩展名。也许您需要在以编程方式执行此操作时提供一个。这是有道理的——不必从 GUI 界面提供扩展很方便，但我们程序员应该编写明确的代码。我建议添加扩展名和匹配的格式。有关文件格式列表，请参阅此 msdn 页面。老实说，我不太了解这些描述。
xlExcel8 = 56 is the .xls format
xlExcel8 = 56 是 .xls 格式
xlExcel12 = 50 is the .xlsb format
xlExcel12 = 50 是 .xlsb 格式
xlOpenXMLWorkbook = 51 is the .xlsx format
xlOpenXMLWorkbook = 51 是 .xlsx 格式
xlOpenXMLWorkbookMacroEnabled = 52 is the .xlsm format
xlOpenXMLWorkbookMacroEnabled = 52 是 .xlsm 格式
xlWorkbookDefault is alsolisted with a value of 51, which puzzles me since I thought the default format could be changed.
xlWorkbookDefault也列出了 51 的值，这让我感到困惑，因为我认为可以更改默认格式。
回答by Pedro Luque
I was struggling, but the below worked for me finally!
我很挣扎，但下面的方法终于对我有用了！
Dim WB As Workbook
Set WB = Workbooks.Open("\users\path\Desktop\test.xlsx")
WB.SaveAs fileName:="\users\path\Desktop\test.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
回答by dennis alberth mendoza iquise
Dim NuevoLibro As Workbook
Dim NombreLibro As String
NombreLibro = "LibroPrueba"
'---Creamos nuevo libro y lo guardamos
Set NuevoLibro = Workbooks.Add
With NuevoLibro
.SaveAs Filename:=NuevaRuta & NombreLibro, FileFormat:=52
End With
'*****************************
'valores para FileFormat
'.xlsx = 51 '(52 for Mac)
'.xlsm = 52 '(53 for Mac)
'.xlsb = 50 '(51 for Mac)
'.xls = 56 '(57 for Mac)
'*****************************
回答by Corey
I know this is an old post but I was looking up something similar... I think your issue was that when you use Now(), the output will be "6/20/2014"... This an issue for a file name as it has "/" in it. As you may know, you cannot use certain symbols in a file name.
我知道这是一篇旧帖子，但我正在查找类似的内容......我认为你的问题是当你使用 Now() 时，输出将是“6/20/2014”......这是一个文件的问题名称，因为它有“/”。您可能知道，不能在文件名中使用某些符号。
Cheers
干杯
回答by Isu
I successfully use the following method in one file,
我在一个文件中成功使用了以下方法，
But come up with exactly the same error again... Only the last line come up with error
但是再次出现完全相同的错误......只有最后一行出现错误
Newpath = Mid(ThisWorkbook.FullName, 1, _
Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & "ABC - " & Format(Date, "dd-mm-yyyy") & ".xlsm"
ThisWorkbook.SaveAs (Newpath)
