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

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 WorkbookSet Wkbk = Workbooks.AddCurrentWorkbook.Sheets("My_Sheet").Copy Before:=Wkbk.Sheets(1)Wkbk.SaveAs Filename:=SaveToDirectory & "My_Sheet" & ".csv", FileFormat:=xlCSVWkbk.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.

This is a year old, but I'll add something for future readers

You won’t find a lot of documentation in Excel help for Run-time error 1004 as Microsoft doesn't consider it to be an Excel error.

The answers above are 100% valid but sometimes it helps to know what is causing the problem so you can avoid it, fix it earlier or fix it more easily.

The fact that this is an intermittent fault, and it is fixed by saving with the full path and file name tells me that either your macro may be trying to save an .xlsb file to the autorecover directory after an auto file recovery.

Alternatively, you may have edited the file's path or filename yourself.

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

You should see something like this in the message box.

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

If so the solution is (as stated above by others) to save your file to its correct path and file name. This can be done with VBA or manually.

I am now in the habit of manually saving the file with its correct path and filename as a matter of course after any autorecover action as it takes seconds and I find it quicker (if this is not a daily occurrence). Thus, the macros will not encounter this fault you run it. Remember that while my habit of manually saving .xlxb files to .xlsm files immediately after a recovery won't help a novice that you give the worksheet to.

A note on Hyperlinks

After this error: If you have hyperlinks in your worksheet created with Ctrl+k in all likelihood, you will have something like "AppData\Roaming\Microsoft\", "\AppData\Roaming\", "../../AppData/Roaming/"or "....\My documents\My documents\" in multiple hyperlinks after file recovery. You can avoid these by attaching your hyperlinks to a text box or generating them with the HYPERLINK function.

Identifying and Repairing them is a little more complicated

First, examine the hyperlinks and determine the erroneous strings and the correct string for each error. Over time, I have found several.

Excel doesn't provide a facility in the 'Go To Special' menu to search for hyperlinks created with Ctrl+k.

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

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

where Link2Text is the UDF

Function Link2Text(rng As Range) As String ' DO NOT deactivate. ' Locates 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 VBA to correct the errors is as follows

Sub Replace_roaming()

' Select the correct sheet Sheets("DIARY").Select

Dim hl As HyperlinkFor 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\")NextApplication.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 you get in the habit of doing regular backups and not relying on autorecover alone. If it fails, you have nothing since your last full backup.

While the worksheet is being fragile backup often, like every hour or after any significant import of new data.

The following shortcuts will backup your worksheet in seconds: Ctrl+O, [highlight the filename], Ctrl+C, Ctrl+V, [ X ]. Regular backups allow you to go immediately to your most recent backup without having to restore from last night's backup file especially if you have to make a request of another person to do this.

Try combining the Path and the CSV file name into a string variable and drop the .csv; that is handled by the FileFormat. Path must be absolute starting with a drive letter or Server Name: Dim strFullFileName as StringstrFullFileName = "C:\My Folder\My_Sheet"If on a Server then it would look something like this: strFullFileName = "\\ServerName\ShareName\My Folder\My_Sheet"Substiture ServerName with your Server name and substitute ShareName with the your network Share name e.g. \\data101\Accounting\My Folder\My_SheetActiveWorkbook.SaveAs Filename:=strFullFileName,FileFormat:=xlCSVMSDOS, CreateBackup:=False