vba 运行时错误“1004”：对象“_Workbook”的方法“SaveAs”失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32138908/
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
Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed
提问by luligoy
Users have an Excel spreadsheet. To save it, they press a button which runs the below VBA code.
用户有一个 Excel 电子表格。为了保存它，他们按下运行以下 VBA 代码的按钮。
The code attempts to save the Excel spreadsheet to a network location amending the file name with today's date.
该代码尝试将 Excel 电子表格保存到网络位置，并使用今天的日期修改文件名。
Intermittently the code will fail with
代码会间歇性地失败
"Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed".
“运行时错误‘1004’：对象‘_Workbook’的方法‘SaveAs’失败”。
The script is:
脚本是：
Public Sub Copy_Save_R2()
Dim wbNew As Workbook
Dim fDate As Date
fDate = Worksheets("Update").Range("D3").Value
Set wbNew = ActiveWorkbook
With wbNew
ActiveWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\" & "R2 Portfolio - CEC A " & Format(fDate, "mm-dd-yyyy")
End With
Sheets("Update").Activate
End Sub
回答by Eric Harlan
As Hugo stated, it could be an issue with the mapped drive. I prefer to use the full UNC path (\\Thismachine\...), in case the workbook gets used on a machine that doesn't have the mapped drive set up.
正如 Hugo 所说，这可能是映射驱动器的问题。我更喜欢使用完整的 UNC 路径 (\\Thismachine\...)，以防在没有设置映射驱动器的机器上使用工作簿。
I thought the missing extension could be the problem, but I just tested it in Excel 2013 and it automatically added .xlsx to the filename.
我认为缺少的扩展名可能是问题所在，但我只是在 Excel 2013 中对其进行了测试，它会自动将 .xlsx 添加到文件名中。
The issue is probably due to the . Basically, you should have either a reference to a workbook, or use the predefined wbNewreference. It's completely unnecessary and should not be combined with ActiveWorkbookActiveWorkbookreference. I'd also recommend using ThisWorkbookinstead, since the user might click on another book while code is running.
这个问题可能是由于. 基本上，您应该拥有对工作簿的引用，或者使用预定义的wbNew参考。这是完全没有必要的，不应与ActiveWorkbookActiveWorkbook引用。我还建议ThisWorkbook改用，因为用户可能会在代码运行时点击另一本书。
Public Sub Copy_Save_R2()
Dim wbNew As Workbook
Dim fDate As Date
fDate = Worksheets("Update").Range("D3").Value
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\R2 Portfolio - CEC A " & Format(fDate, "mm-dd-yyyy") & ".xlsx"
Application.DisplayAlerts = True
ThisWorkbook.Sheets("Update").Activate
End Sub
Edit: Added Application.DisplayAlertscommands to prevent any Save popups, such as using .xlsx instead of .xlsm, and overwriting an existing copy.
编辑：添加Application.DisplayAlerts了防止任何保存弹出窗口的命令，例如使用 .xlsx 而不是 .xlsm，并覆盖现有副本。
Edit 2018-08-11: Added escape backslashes to fix UNC path display. Added strike-through to inaccurate statement about the Withstatement (see comments below). Basically, since nothing between Withand End Withbegins with a ., the statement isn't doing anything at all.
编辑 2018-08-11：添加转义反斜杠以修复 UNC 路径显示。为不准确的声明添加了删除线With（见下面的评论）。基本上，由于With和之间没有任何内容End With以 a 开头.，因此该语句根本没有做任何事情。
回答by DEG
This worked for me. Ensure your workbook is not shared. I guess "Shared" workbooks have limitations.
这对我有用。确保您的工作簿未共享。我猜“共享”工作簿有局限性。
Saw this here: https://www.ozgrid.com/forum/forum/help-forums/excel-general/27843-save-xls-as-txt
在这里看到：https: //www.ozgrid.com/forum/forum/help-forums/excel-general/27843-save-xls-as-txt
In the "Review" tab click "Share Workbook" and ensure "Allow changes by more than one user at the same time. This allows workbook merging" is unchecked.
在“审阅”选项卡中单击“共享工作簿”并确保“允许多个用户同时更改。这允许工作簿合并”未选中。
回答by SGJ
I was also looking for the cause of this error, and then remembered I was working on a version of my spreadsheet that had been recovered. Once I manually saved the recovered file and reopened it there was no problem with the vba code to save the workbook.
我也在寻找此错误的原因，然后想起我正在处理已恢复的电子表格版本。一旦我手动保存了恢复的文件并重新打开它，保存工作簿的 vba 代码就没有问题了。
回答by Luiz Fernando Lemes de Oliveir
When it happened to me, I added a command before the save.
当它发生在我身上时，我在保存之前添加了一个命令。
On Error Resume Next
Kill TargetFullname
On Error GoTo 0
wb.SaveCopyAs TargetFullname
(I also use application.display=false)
（我也用application.display=false）
