运行时错误'1004':对象“_Workbook”的方法“SaveAs”失败 [英] Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed
问题描述
用户有一个Excel电子表格,当他们去保存它们时,他们按一个在宏中运行以下vba代码的按钮。该脚本尝试将Excel电子表格保存到网络位置,以今天的日期修改文件名。间歇性地,脚本将以运行时错误1004'失败:对象_Workbook的方法SaveAs失败。有谁知道原因是什么?脚本是:
Public Sub Copy_Save_R2()
Dim wbNew As Workbook
Dim fDate As Date
fDate = Worksheets(Update)。Range(D3)。value
设置wbNew = ActiveWorkbook
带有wbNew
ActiveWorkbook.SaveAs文件名:=Q:\R2 Portfolio Prints \#Archive - R2 Portfolio \& R2投资组合 - CEC A&格式(fDate,mm-dd-yyyy)
结束
表单(更新)激活
End Sub
正如Hugo所说,这可能是映射驱动器的一个问题。我更喜欢使用完整的UNC路径(\\Thismachine\ ...),以防工作簿在没有映射驱动器设置的机器上使用。
我认为缺少的扩展名可能是问题,但我只是在Excel 2013中进行了测试,并自动将.xlsx添加到文件名。
<罢工>这个问题可能是由于 wbNew
引用。这是完全不必要的,不应该与 ActiveWorkbook
组合。基本上,您应该有一个对工作簿的引用,或使用预定义的 ActiveWorkbook
引用。我还建议使用 ThisWorkbook
,因为用户可以在代码运行时点击另一本书。
Public Sub Copy_Save_R2()
pre>
Dim wbNew As Workbook
Dim fDate As Date
fDate = Worksheets(Update)。Range( D3)。值
Application.DisplayAlerts = False
ThisWorkbook.SaveAs文件名:=Q:\R2投资组合打印\档案 - R2投资组合\\ R2投资组合 - CEC A &格式(fDate,mm-dd-yyyy)& .xlsx
Application.DisplayAlerts = True
ThisWorkbook.Sheets(Update)。激活
End Sub
编辑:添加
Application.DisplayAlerts
命令以防止任何保存弹出窗口,例如使用.xlsx而不是。 xlsm,并覆盖现有副本。
编辑2018-08-11:添加了转义反斜杠来修复UNC路径显示。添加了对
与
声明(见下面的注释)的不准确声明的透明度。基本上,由于与
和End With
之间没有以开头。
,声明根本没有做任何事情。Users have an Excel spreadsheet that when they go to save it, they press a button which runs the below vba code within a macro. The script attempts to save the Excel spreadsheet to a network location amending the file name with today's date. Intermittently the script will fail with "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed". Does anyone know what the cause is? 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
解决方案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.
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.
The issue is probably due to the. Basically, you should have either a reference to a workbook, or use the predefinedwbNew
reference. It's completely unnecessary and should not be combined withActiveWorkbook
ActiveWorkbook
reference. I'd also recommend usingThisWorkbook
instead, since the user might click on another book while code is running.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.DisplayAlerts
commands to prevent any Save popups, such as using .xlsx instead of .xlsm, and overwriting an existing copy.Edit 2018-08-11: Added escape backslashes to fix UNC path display. Added strike-through to inaccurate statement about the
With
statement (see comments below). Basically, since nothing betweenWith
andEnd With
begins with a.
, the statement isn't doing anything at all.这篇关于运行时错误'1004':对象“_Workbook”的方法“SaveAs”失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!