运行时错误'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（）
 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 
  pre> 
 
 
编辑：添加 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 wbNew reference. It's completely unnecessary and should not be combined with ActiveWorkbook. Basically, you should have either a reference to a workbook, or use the predefined ActiveWorkbook reference. I'd also recommend using ThisWorkbook 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 between With and End With begins with a ., the statement isn't doing anything at all.

                        
这篇关于运行时错误'1004'：对象“_Workbook”的方法“SaveAs”失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！