获取对象_workbook的“方法saveas失败”尝试将XLSM保存为CSV时出错 [英] Getting "method saveas of object _workbook failed" error while trying to save an XLSM as 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
表格（My_Sheet）。复制
ActiveWorkbook.SaveAs文件名：= SaveToDirectory& My_Sheet& .csv，FileFormat：= xlCSV
ActiveWorkbook.Close SaveChanges：= False
ThisWorkbook.Activate
ThisWorkbook.SaveAs文件名：= CurrentWorkbook，FileFormat：= CurrentFormat
Application.DisplayAlerts = True
Application.AlertBeforeOverwriting = True
End Sub
奇怪的是，有时，但并不总是失败，标题（运行时错误1004：对象_workbook的方法saveas失败）的错误消息与调试器弹出指出我这行：
ActiveWorkbook.SaveAs文件名：= SaveToDirectory& My_Sheet& .csv，FileFormat：= xlCSV
我已经把它搞定了，显然确实发生了很多事情人们和我尝试的一些解决方案是：
- 指定目录是字符串
- 避免文件名或文件夹中的任何特殊字符（见此处）
- 复制粘贴工作表作为值保存为.csv（见 here ）
- 使用.csv代码编号指定FileFormat（见 here ）
- 禁用/重新启用某些警报
- 在ActiveWorkbook.SaveAs行中添加其他字段，关于密码，创建备份等等
仍然，它可能连续运行50-60次，然后在某一点再次失败。
任何建议或我可以寻找的东西来解决这个问题（除了停止使用VBA / Excel这个任务，这将很快发生，但我现在不能）。
提前感谢你的时间。 / p>
编辑：感谢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
设置TempWB = Workbooks.Add
CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
SaveToDirectory =\\MyFolder\
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
ThisWorkbook.Sheets（My_Sheet）。复制之前：= TempWB.Sheets（1）
ThisWorkbook.Sheets（My_Sheet）。SaveAs文件名：= SaveToDirectory& My_Sheet& .csv，FileFormat：= 6
TempWB.Close SaveChanges：= False
ThisWorkbook.SaveAs文件名：= CurrentWorkbook，FileFormat：= CurrentFormat
ActiveWorkbook.Close SaveChanges：= False
Application.DisplayAlerts = True
Application.AlertBeforeOverwriting = True
End Sub
我通常发现 ActiveWorkbook 是在这些情况下的问题。那个我的意思是你不知道你没有选择那个工作簿（或任何其他），而Excel不知道该怎么做。不幸的是，由于 copy 不返回任何东西（复制的工作表会很好），这是处理此问题的一种标准方法。
所以，我们可以解决这个问题，因为我们如何将此工作表复制到新的工作簿，并获得该工作簿的引用。我们可以做的是创建新的工作簿，然后复制工作表：
Dim wkbk as Workbook
设置Wkbk = Workbooks.Add
CurrentWorkbook.Sheets（My_Sheet）。复制之前：= Wkbk.Sheets（1）
Wkbk.SaveAs文件名：= SaveToDirectory& My_Sheet& .csv，FileFormat：= xlCSV
Wkbk.Close SaveChanges：= False
或在这种情况下，有一个更好的方法： WorkSheet 支持 SaveAs 方法。没有必要的副本。
CurrentWorkbook.Sheets（My_Sheet）。SaveAs文件名：= SaveToDirectory& My_Sheet& .csv，FileFormat：= xlCSV
我会警告您将工作簿重新保存为原始名称afterwarsd，如果它保持开放，但你已经有你的代码。
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
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:
- 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
Still, it might run correctly up to 50-60 times in a row, and then at some point fail again at the row.
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).
Thanks in advance for your time.
EDIT: Solved thanks to Degustaf suggestion. I made only two changes to Degustaf's suggested code:
- ThisWorkbook.Sheets instead of CurrentWorkbook.Sheets
- FileFormat:=6 instead of FileFormat:=xlCSV (apparently is more robust to differente versions of 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
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
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
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.
这篇关于获取对象_workbook的“方法saveas失败”尝试将XLSM保存为CSV时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
