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

Question

I am trying to save an Excel workbook with macros as a csv file, overwriting the old one (below I had to change the name of the folder and sheet, but this is not a problem).

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's not with

Runtime Error 1004: Method Method of the _workbook Object ** Fails)

The debugger indicates:

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

I googled, and some of the solutions I tried were:

• Indicating the directory is a string
• Avoid any special characters in the file or folder name (see here )
• Copy and paste the sheet as a value before saving it in .csv format (see here )
• Specifying FileFormat with .csv code (see here )
• Disabling / re-enabling some alerts
• Adding other fields to the ActiveWorkbook.SaveAs line regarding passwords, creating backups, etc.

However, it may work correctly up to 50-60 times in a row, and then at some point a failure will occur again.

Any suggestion other than stop using VBA / Excel for this task, which is about to happen, but I can't yet.

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

• ThisWorkbook.Sheets instead of CurrentWorkbook.Sheets
• FileFormat:=6 instead of FileFormat:=xlCSV (obviously, more resistant 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 

Answer 1

I usually find ActiveWorkbook be a problem in these cases. By this, I mean that somehow this book (or any other) is not selected from you, and Excel does not know what to do. Unfortunately, since copy does not return anything (it would be nice to copy the sheet), this is the standard way to solve this problem.

Thus, we can approach this as how we can copy this sheet into a new workbook and get a link to this workbook. What we can do is create a 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 this situation: WorkSheet supports the SaveAs method. No copy required.

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

I will warn you to subsequently restore the real name of the book if it remains open, but it is already in your code.

Answer 2

This is the year, but I will add something for future readers.

You will not find much documentation in the Excel help system for a 1004 runtime error, because Microsoft does not consider it an Excel error.

Answers above 100% are valid, but sometimes it helps to find out what causes the problem, so you can avoid it, fix it earlier or fix it easier.

The fact that this is an intermittent error is fixed by saving with the full path, and the file name tells me that either your macro may try to save the .xlsb file to the autocompletion directory after the files are automatically restored.

Alternatively, you can edit the file path or file name yourself.

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

You should see something similar in the message box.

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

If it is a solution (as indicated above by others) to save the file in the correct path and file name. This can be done using VBA or manually.

Now I’m used to manually saving the file with the correct path and file name, as a matter of course after any auto-discovery action, as it takes a few seconds and I find it faster (if this is not a daily occurrence). This way, macros will not encounter this error that you are running. Remember that although my habit of manually saving .xlxb files in .xlsm files right after recovery will not help the newcomer to whom you give the worksheet.

Hyperlink Note

After this error: if you have hyperlinks on the worksheet created using Ctrl + k , you will have something like "AppData \ Roaming \ Microsoft \", "\ AppData \ Roaming", .. /../AppData / Roaming / "or" .... \ My documents \ My documents \ "in several hyperlinks after the file is restored. You can avoid this by adding your hyperlinks to the text field or by creating them using the HYPERLINK function.

Identifying and recovering them is a bit more complicated.

First look at the hyperlinks and identify the error lines and the correct line for each error. Over time, I found several.

Excel does not provide tools in the Go To Special menu to search for hyperlinks created with Ctrl + k .

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

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

where Link2Text is UDF

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

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

My error correction VBA is as follows

Sub Replace_roaming ()

'Select the correct sheet 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 that you get used to regular backups and not rely solely on autodiscover. If this fails, you have not had anything since the last full backup.

Although the worksheet is often a fragile backup, like every hour or after a significant import of new data.

The following shortcuts will backup worksheet in seconds: Ctrl + O , [highlight file name], Ctrl + C , Ctrl + V , [X]. Regular backups allow you to immediately jump to the most recent backup without having to restore from the backup file last night, especially if you need to request another person to do this.

Answer 3

Try combining the path and name of the CSV file in a string variable and discard .csv; which is processed by FileFormat. The path must be absolute, starting with the drive letter or server name: Dim strFullFileName as String strFullFileName = "C:\My Folder\My_Sheet" If it looks like this on the server: strFullFileName = "\\ServerName\ShareName\My Folder\My_Sheet" Substiture ServerName with your server name and replace ShareName with your network. \\data101\Accounting\My Folder\My_Sheet ActiveWorkbook.SaveAs Filename:=strFullFileName,FileFormat:=xlCSVMSDOS, CreateBackup:=False