vba 尝试将 XLSM 保存为 CSV 时出现“对象 _workbook 的方法保存失败”错误

Getting "method saveas of object _workbook failed" error while trying to save an XLSM as CSV

提问by Riccardo

I'm trying to save a macro-enabled Excel workbook as a csv file, overwriting the old one (below I had to change the name of the folder and the Sheet, but that doesn't seem to be the issue).

我正在尝试将启用宏的 Excel 工作簿另存为 csv 文件，覆盖旧的（下面我不得不更改文件夹和工作表的名称，但这似乎不是问题）。

 Sub SaveWorksheetsAsCsv()

 Dim SaveToDirectory As String
 Dim CurrentWorkbook As String
 Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
 SaveToDirectory = "\MyFolder\"

 Application.DisplayAlerts = False
 Application.AlertBeforeOverwriting = False

 Sheets("My_Sheet").Copy

 ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV
 ActiveWorkbook.Close SaveChanges:=False
 ThisWorkbook.Activate

 ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat

 Application.DisplayAlerts = True
 Application.AlertBeforeOverwriting = True

 End Sub

Sometimes it fails with

有时它会失败

Runtime Error 1004: method saveas of object _workbook failed**)

运行时错误 1004：对象 _workbook 的方法保存失败**）

The debugger points out:

调试器指出：

 ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV

I googled and some of the solutions I tried were:

我用谷歌搜索，我尝试过的一些解决方案是：

• Specifiying that the directory is a string
• Avoid any special character in the file name or folder (seen here)
• Copy paste the worksheet as value before saving it as .csv (seen here)
• Specifying the FileFormat with the .csv code number (seen here)
• Disabling/Re-enabling some of the alerts
• Adding other fields in the ActiveWorkbook.SaveAs row, regarding passwords, creating backups etcetc

• 指定目录是字符串
• 避免在文件名或文件夹中使用任何特殊字符（参见此处）
• 在将工作表保存为 .csv 之前将其复制粘贴为值（见此处）
• 使用 .csv 代码指定 FileFormat（见此处）
• 禁用/重新启用某些警报
• 在 ActiveWorkbook.SaveAs 行中添加其他字段，关于密码、创建备份等

Still, it might run correctly up to 50-60 times in a row, and then at some point fail again.

尽管如此，它可能会连续正确运行 50-60 次，然后在某些时候再次失败。

Any suggestion, except stop using VBA/Excel for this task, which will happen soon, but I can't for now.

任何建议，除了停止使用 VBA/Excel 来完成这项任务，这很快就会发生，但我现在不能。

EDIT: Solved thanks to Degustaf suggestion. I made only two changes to Degustaf's suggested code:

编辑：感谢德古斯塔夫的建议解决了。我只对 Degustaf 的建议代码做了两处更改：

• ThisWorkbook.Sheetsinstead of CurrentWorkbook.Sheets
• FileFormat:=6instead of FileFormat:=xlCSV(apparently is more robust to different versions of Excel)

• ThisWorkbook.Sheets代替 CurrentWorkbook.Sheets
• FileFormat:=6而不是FileFormat:=xlCSV（显然对不同版本的 Excel 更健壮）

---

---

Sub SaveWorksheetsAsCsv()

Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim TempWB As Workbook

Set TempWB = Workbooks.Add

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
SaveToDirectory = "\MyFolder\"

Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False

ThisWorkbook.Sheets("My_Sheet").Copy Before:=TempWB.Sheets(1)
ThisWorkbook.Sheets("My_Sheet").SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=6
TempWB.Close SaveChanges:=False

ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
ActiveWorkbook.Close SaveChanges:=False

Application.DisplayAlerts = True
Application.AlertBeforeOverwriting = True
End Sub

采纳答案by Degustaf

I generally find that ActiveWorkbookis the problem in these cases. By that I mean that somehow you don't have that workbook (or any other) selected, and Excel doesn't know what to do. Unfortunately, since copydoesn't return anything (the copied worksheet would be nice), this is a standard way of approaching this problem.

我通常发现这ActiveWorkbook是这些情况下的问题。我的意思是，不知何故您没有选择该工作簿（或任何其他），并且 Excel 不知道该怎么做。不幸的是，由于copy不返回任何内容（复制的工作表会很好），这是解决此问题的标准方法。

So, we can approach this as how can we copy this sheet to a new workbook, and get a reference to that workbook. What we can do is create the new workbook, and then copy the sheet:

因此，我们可以将其视为如何将此工作表复制到新工作簿，并获得对该工作簿的引用。我们可以做的是创建新的工作簿，然后复制工作表：

Dim wkbk as Workbook

Set Wkbk = Workbooks.Add
CurrentWorkbook.Sheets("My_Sheet").Copy Before:=Wkbk.Sheets(1)
Wkbk.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV
Wkbk.Close SaveChanges:=False

Or, there is an even better approach in a situation like this: WorkSheetsupports the SaveAsmethod. No copy necessary.

或者，在这种情况下还有更好的方法：WorkSheet支持该SaveAs方法。无需复印。

CurrentWorkbook.Sheets("My_Sheet").SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV

I will warn you to resave the workbook to its original name afterwards, if it is staying open, but you already have that in your code.

我会警告您之后将工作簿重新保存为其原始名称，如果它保持打开状态，但您的代码中已经有了它。

回答by Mike Benstead

This is a year old, but I'll add something for future readers

这是一岁了，但我会为未来的读者添加一些东西

You won't find a lot of documentation in Excel help for Run-time error 1004 as Microsoft doesn't consider it to be an Excel error.

您不会在 Excel 帮助中找到大量有关运行时错误 1004 的文档，因为 Microsoft 不认为它是 Excel 错误。

The answers above are 100% valid but sometimes it helps to know what is causing the problem so you can avoid it, fix it earlier or fix it more easily.

上面的答案 100% 有效，但有时有助于了解导致问题的原因，这样您就可以避免它、更早地修复它或更容易地修复它。

The fact that this is an intermittent fault, and it is fixed by saving with the full path and file name tells me that either your macro may be trying to save an .xlsb file to the autorecover directory after an auto file recovery.

这是一个间歇性故障，通过使用完整路径和文件名进行保存这一事实告诉我，在自动文件恢复后，您的宏可能试图将 .xlsb 文件保存到自动恢复目录。

Alternatively, you may have edited the file's path or filename yourself.

或者，您可能自己编辑了文件的路径或文件名。

You can check the path and filename with:- MsgBox ThisWorkbook.FullName

您可以使用以下命令检查路径和文件名：- MsgBox ThisWorkbook.FullName

You should see something like this in the message box.

您应该会在消息框中看到类似的内容。

C:\Users\Mike\AppData\Roaming\Microsoft\Excel\DIARY(version 1).xlxb

C:\Users\Mike\AppData\Roaming\Microsoft\Excel\DIARY（版本 1）.xlxb

If so the solution is (as stated above by others) to save your file to its correct path and file name. This can be done with VBA or manually.

如果是这样，解决方案是（如上所述）将您的文件保存到正确的路径和文件名。这可以使用 VBA 或手动完成。

I am now in the habit of manually saving the file with its correct path and filename as a matter of course after any autorecover action as it takes seconds and I find it quicker (if this is not a daily occurrence). Thus, the macros will not encounter this fault you run it. Remember that while my habit of manually saving .xlxb files to .xlsm files immediately after a recovery won't help a novice that you give the worksheet to.

我现在习惯于在任何自动恢复操作之后手动使用正确的路径和文件名手动保存文件，因为它需要几秒钟，而且我发现它更快（如果这不是每天都会发生）。这样，宏就不会遇到你运行它的这个故障。请记住，虽然我在恢复后立即将 .xlxb 文件手动保存为 .xlsm 文件的习惯对您将工作表提供给的新手没有帮助。

A note on Hyperlinks

关于超链接的说明

After this error: If you have hyperlinks in your worksheet created with Ctrl+kin all likelihood, you will have something like "AppData\Roaming\Microsoft\", "\AppData\Roaming\", "../../AppData/Roaming/"or "....\My documents\My documents\" in multiple hyperlinks after file recovery. You can avoid these by attaching your hyperlinks to a text box or generating them with the HYPERLINK function.

出现此错误后：如果您的工作表中很可能有使用Ctrl+创建的超链接k，您将看到类似“AppData\Roaming\Microsoft\”、“\AppData\Roaming\”、“../../AppData/Roaming”的内容/"或"....\我的文档\我的文档\"在文件恢复后的多个超链接中。您可以通过将超链接附加到文本框或使用 HYPERLINK 函数生成它们来避免这些。

Identifying and Repairing them is a little more complicated

识别和修复它们有点复杂

First, examine the hyperlinks and determine the erroneous strings and the correct string for each error. Over time, I have found several.

首先，检查超链接并为每个错误确定错误的字符串和正确的字符串。随着时间的推移，我发现了几个。

Excel doesn't provide a facility in the 'Go To Special' menu to search for hyperlinks created with Ctrl+k.

Excel 没有在“转到特殊”菜单中提供工具来搜索使用Ctrl+创建的超链接k。

You can automate the identification of erroneous hyperlinks in a helper column, say column Z and using the formula

您可以自动识别辅助列中的错误超链接，例如 Z 列并使用公式

=OR(ISNUMBER(SEARCH("Roaming", Link2Text($C2),1)),ISNUMBER(SEARCH("Roaming", Link2Text($D2),1)))

where Link2Text is the UDF

其中 Link2Text 是 UDF

Function Link2Text(rng As Range) As String ' DO NOT deactivate. ' Locates hyperlinks containing 'roaming' in column Z.

Function Link2Text(rng As Range) As String ' 请勿停用。' 在 Z 列中定位包含 'roaming' 的超链接。

' Identify affected hyperlinks
    If rng(1).Hyperlinks.Count Then
    Link2Text = rng.Hyperlinks(1).Address
    End If

  End Function

My VBA to correct the errors is as follows

我的VBA纠正错误如下

Sub Replace_roaming()

Sub Replace_roaming()

' Select the correct sheet Sheets("DIARY").Select

' 选择正确的工作表 Sheets("DIARY").Select

Dim hl As Hyperlink
For Each hl In ActiveSheet.Hyperlinks
    hl.Address = Replace(hl.Address, "AppData\Roaming\Microsoft\", "")
Next
    For Each hl In ActiveSheet.Hyperlinks
    hl.Address = Replace(hl.Address, "AppData\Roaming\", "")
Next

    For Each hl In ActiveSheet.Hyperlinks
    hl.Address = Replace(hl.Address, "../../AppData/Roaming/", "..\..\My documents\")
Next
    For Each hl In ActiveSheet.Hyperlinks
    hl.Address = Replace(hl.Address, "..\..\My documents\My documents\", "..\..\My documents\")
Next

Application.Run "Recalc_BT"

' Move down one active row to get off the heading
    ActiveCell.Offset(1, 0).Select

' Check active row location
    If ActiveCell.Row = 1 Then
    ActiveCell.Offset(1, 0).Select
    End If

' Recalc active row
   ActiveCell.EntireRow.Calculate

' Notify
    MsgBox "Replace roaming is now complete."

End Sub

I also recommend you get in the habit of doing regular backups and not relying on autorecover alone. If it fails, you have nothing since your last full backup.

我还建议您养成定期备份的习惯，而不要单独依赖自动恢复。如果失败，则自上次完整备份以来您一无所有。

While the worksheet is being fragile backup often, like every hour or after any significant import of new data.

虽然工作表经常是脆弱的备份，例如每小时或在任何重要的新数据导入之后。

The following shortcuts will backup your worksheet in seconds: Ctrl+O, [highlight the filename], Ctrl+C, Ctrl+V, [ X ]. Regular backups allow you to go immediately to your most recent backup without having to restore from last night's backup file especially if you have to make a request of another person to do this.

以下快捷方式将在几秒钟内备份您的工作表： Ctrl+ O、[突出显示文件名]、Ctrl+ C、Ctrl+ V、[X]。定期备份可让您立即进入最近的备份，而无需从昨晚的备份文件中恢复，尤其是在您必须请求他人执行此操作时。

回答by David Gill

I had a similar issue however for me the problem was I was creating the Filename based on strings extracted from a workbook and sometimes these strings would have characters that can't be in a filename. Removing these characters did the trick for me!

我有一个类似的问题但是对我来说问题是我根据从工作簿中提取的字符串创建文件名，有时这些字符串会包含不能在文件名中的字符。删除这些字符对我来说很有效！

回答by JMMach

Try combining the Path and the CSV file name into a string variable and drop the .csv; that is handled by the FileFormat. Path must be absolute starting with a drive letter or Server Name: Dim strFullFileName as StringstrFullFileName = "C:\My Folder\My_Sheet"If on a Server then it would look something like this: strFullFileName = "\\ServerName\ShareName\My Folder\My_Sheet"Substiture ServerName with your Server name and substitute ShareName with the your network Share name e.g. \\data101\Accounting\My Folder\My_SheetActiveWorkbook.SaveAs Filename:=strFullFileName,FileFormat:=xlCSVMSDOS, CreateBackup:=False

尝试将 Path 和 CSV 文件名组合成一个字符串变量并删除 .csv；由 FileFormat 处理。路径必须是以驱动器号或服务器名称开头的绝对路径： Dim strFullFileName as StringstrFullFileName = "C:\My Folder\My_Sheet"如果在服务器上，则它看起来像这样： strFullFileName = "\\ServerName\ShareName\My Folder\My_Sheet"用您的服务器名称替换 ServerName 并用您的网络共享名称替换 ShareName 例如\\data101\Accounting\My Folder\My_SheetActiveWorkbook.SaveAs Filename:=strFullFileName,FileFormat:=xlCSVMSDOS, CreateBackup:=False