Search code examples
excelvbacsvruntime-errorsave-as

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

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).

 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**)

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

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

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

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 different 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
Solution

  • 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 afterwards, if it is staying open, but you already have that in your code.