CODEWITHSUNDEEP
excelvba
luligoy
luligoy

Reputation: 11

Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed

Users have an Excel spreadsheet. To save it, they press a button which runs the below VBA code.

The code attempts to save the Excel spreadsheet to a network location amending the file name with today's date.

Intermittently the code will fail with

"Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed".

The script is:

Public Sub Copy_Save_R2()
    Dim wbNew As Workbook
    Dim fDate As Date

    fDate = Worksheets("Update").Range("D3").Value

    Set wbNew = ActiveWorkbook

    With wbNew
        ActiveWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\" & "R2 Portfolio - CEC A " & Format(fDate, "mm-dd-yyyy")
    End With

    Sheets("Update").Activate
End Sub

Upvotes: 1

Views: 61414

Answers (6)

Tam Le
Tam Le

Reputation: 378

Appearantly, my issue with SaveAs is how you supply the parameter

resultWb.SaveAs storageFolder & filePrefix & curYear & ".xlsx"
'This create error 1004

fullFilePath = storageFolder & filePrefix & curYear & ".xlsx"
resultWb.SaveAs fullFilePath
'No error

Upvotes: 0

LindaC
LindaC

Reputation: 11

Had the same error message when saving a workbook with a dynamic file name to a network location. Saving stage suddenly stopped working. Error 1004 Workbook SaveAs method failed.

Realised after a while that the location where the macro saved the output had changed and the macro's script had been tweaked by the team to reflect the new network address.

Whilst the new network address was accurate, it was simply too long... I shortened the folder names, as well as the file name and bingo... Workbooks are being saved again, with no error messages.

Upvotes: 1

Luiz Fernando Lemes de Oliveir
Luiz Fernando Lemes de Oliveir

Reputation: 1

When it happened to me, I added a command before the save.

On Error Resume Next
    Kill TargetFullname
On Error GoTo 0

wb.SaveCopyAs TargetFullname

(I also use application.display=false)

Upvotes: 0

SGJ
SGJ

Reputation: 11

I was also looking for the cause of this error, and then remembered I was working on a version of my spreadsheet that had been recovered. Once I manually saved the recovered file and reopened it there was no problem with the vba code to save the workbook.

Upvotes: 1

DEG
DEG

Reputation: 1

This worked for me. Ensure your workbook is not shared. I guess "Shared" workbooks have limitations.

Saw this here: https://www.ozgrid.com/forum/forum/help-forums/excel-general/27843-save-xls-as-txt

In the "Review" tab click "Share Workbook" and ensure "Allow changes by more than one user at the same time. This allows workbook merging" is unchecked.

Upvotes: -1

Eric Harlan
Eric Harlan

Reputation: 384

As Hugo stated, it could be an issue with the mapped drive. I prefer to use the full UNC path (\\Thismachine\...), in case the workbook gets used on a machine that doesn't have the mapped drive set up.

I thought the missing extension could be the problem, but I just tested it in Excel 2013 and it automatically added .xlsx to the filename.

The issue is probably due to the wbNew reference. It's completely unnecessary and should not be combined with ActiveWorkbook. Basically, you should have either a reference to a workbook, or use the predefined ActiveWorkbook reference. I'd also recommend using ThisWorkbook instead, since the user might click on another book while code is running.

Public Sub Copy_Save_R2()
    Dim wbNew As Workbook
    Dim fDate As Date

    fDate = Worksheets("Update").Range("D3").Value

    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:="Q:\R2 Portfolio Prints\#Archive - R2 Portfolio\R2 Portfolio - CEC A " & Format(fDate, "mm-dd-yyyy") & ".xlsx"
    Application.DisplayAlerts = True

    ThisWorkbook.Sheets("Update").Activate
End Sub

Edit: Added Application.DisplayAlerts commands to prevent any Save popups, such as using .xlsx instead of .xlsm, and overwriting an existing copy.

Edit 2018-08-11: Added escape backslashes to fix UNC path display. Added strike-through to inaccurate statement about the With statement (see comments below). Basically, since nothing between With and End With begins with a ., the statement isn't doing anything at all.

Upvotes: 3

Related Questions