我正在尝试将带宏的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
有时会出现以下错误:
运行时错误1004:对象'_workbook'的方法'saveas'失败**)
调试器指出:
(Note: The ** in the original text may be a footnote or special symbol, so I retained it in the translation.)
ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSV
我用谷歌搜索了一下,并尝试了一些解决方案:
- 指定目录为字符串
- 避免在文件名或文件夹中使用任何特殊字符(见这里)
- 在将表格另存为CSV之前将其复制粘贴为值(见这里)
- 使用.csv代码数字指定FileFormat(见这里)
- 禁用/重新启用某些警告
- 添加其他字段到ActiveWorkbook.SaveAs行,例如密码、创建备份等等
即使如此,它可能会连续运行50-60次,然后在某个时间点失败。
除了停止使用VBA/Excel进行此任务之外,还有哪些建议?很快就会发生,但现在我不能停止使用。
编辑:多亏了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