获取“方法对象的saveas _workbook失败”尝试将XLSM另存为CSV时出错 [英] Getting "method saveas of object _workbook failed" error while trying to save an XLSM as CSV

问题描述

我试图通过这个宏保存（宏启用）excel工作簿作为csv文件，覆盖旧的（下面我更改了文件夹和Sheet的名称，但似乎不是成为问题！）：

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

 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

奇怪的是，有时，但不总是，它失败，标题的错误消息（运行时错误1004：对象_workbook的方法saveas失败）弹出与调试器指向这一行：

Oddly enough, sometimes, but not always, it fails and the error message of the title (Runtime Error 1004: method saveas of object _workbook failed) pops out with the debugger pointing me out this row:

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

我搜索了，显然它确实发生了很多人们和我尝试的一些解决方案是：

I googled it up and apparently it does happen a lot to people and some of the solutions I tried were:

• 指定目录是一个字符串


• 避免文件名或文件夹中的任何特殊字符（请参见此处）


• 将工作表复制粘贴为值，然后保存为.csv（参见此处）


• 使用.csv代码号指定FileFormat（见此处）


• 停用/

• 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

仍然，它可能连续运行正确达50-60次，然后在某一点再次在该行失败。

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

任何suggetion或东西可以查找解决这个问题（除非停止使用VBA / Excel这个任务，这将很快发生，但我现在不能）。

Any suggetion or things I could look up to solve this problem (except stop using VBA/Excel for this task, which will happen soon, but I can't for now).

提前感谢

编辑：解决感谢Degustaf建议。我只对Degustaf的建议代码做了两个更改：

• 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

推荐答案

我通常发现 ActiveWorkbook 问题在这些情况下。我的意思是，不知何故你没有选择的工作簿（或任何其他），而Excel不知道该怎么办。不幸的是，由于 copy 不返回任何东西（复制的工作表会很好），这是一个标准的方法来处理这个问题。

I generally find that ActiveWorkbook is 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 copy doesn't return anything (the copied worksheet would be nice), this is a standard way of approaching this problem.

所以，我们可以这样做，如何将这个工作表复制到一个新的工作簿，并获得对该工作簿的引用。我们可以做的是创建新的工作簿，然后复制工作表：

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

，在这种情况下有一个更好的方法： WorkSheet 支持 SaveAs 方法。无需复制。

Or, there is an even better approach in a situation like this: WorkSheet supports the SaveAs method. No copy necessary.

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

我会警告您将工作簿重新保存为其原始名称afterwarsd，如果它是保持开放，但你已经在你的代码。

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

这篇关于获取“方法对象的saveas _workbook失败”尝试将XLSM另存为CSV时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！