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

Question

I'm using the following code to save an updated workbook.

Private Sub cmdSaveUpdatedWB_Click()

On Error GoTo Err_cmdSaveUpdatedWB_Click

    gwbTarget.Activate   <<<<<<<<<<<<<<<<<<<<<<<

    Application.DisplayAlerts = False

    gwbTarget.SaveAs txtUpdWorkbookName.Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Application.DisplayAlerts = False

    frmLoanWBMain.Show
    gwbTarget.Close
    Set gwbTarget = Nothing

    gWBPath = ""
    gWBName = ""

    lblWorkbookSaved.Enabled = True
    cmdUpdateAnotherWorkbook.Visible = True

Exit_cmdSaveUpdatedWB_Click:

    Exit Sub

Err_cmdSaveUpdatedWB_Click:

    MsgBox "The following error occurred inthe [cmdSaveUpdateWB_Click] event handler." & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & "Error descriptioin: " & Err.Description

    Resume Exit_cmdSaveUpdatedWB_Click

End Sub

As noted in the title, the SaveAs operation fails. I've determined that the failure is a result of having the workbook to be saved losing the focus. I can step through the code and get the error. Once the error is generated, selecting Debug in the error message box and then pressing F5 to run the code will result in the workbook saving correctly. Placing Debug.Print statements before and after the Activate method of the worbook to be saved indicates that the active wokbook is the workbook containing the code and the form used to update the workbook. Placing a print statement in the Immediate wondow that prints the ActiveWorkbook.Name will result in printing the name of the workbook to be saved - gwbTarget.Name. Pressing F5 then runs the code correctly. I have been unable to figure out why the workbook to be saved loses the focus. I placed delays, multiple activation statements, local variables to use for the workbookto be saved, and for the name of the workbook to be saved. Any help or ideas as to why this is happening and how to fix it will be greatly appreciated.

I did make some changes. The code is listed below...

Private Sub cmdSaveUpdatedWB_Click()
On Error GoTo Err_cmdSaveUpdatedWB_Click

Dim wbSave As Workbook

    Set wbSave = gwbTarget

    gwbTarget.Activate

    Application.DisplayAlerts = False

'''''''    gwbTarget.SaveAs txtUpdWorkbookName.Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    wbSave.SaveAs fileName:=txtUpdWorkbookName.Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Application.DisplayAlerts = False

    frmLoanWBMain.Show
    gwbTarget.Close
    Set gwbTarget = Nothing

    gWBPath = ""
    gWBName = ""

    lblWorkbookSaved.Enabled = True
    cmdUpdateAnotherWorkbook.Visible = True


Exit_cmdSaveUpdatedWB_Click:

    Set wbSave = Nothing
    Exit Sub

Err_cmdSaveUpdatedWB_Click:

    MsgBox "The following error occurred inthe [cmdSaveUpdateWB_Click] event handler." & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & "Error descriptioin: " & Err.Description

    Resume Exit_cmdSaveUpdatedWB_Click

End Sub

I've changed the code to more closely resemble the suggestion below. The listing is below, along with the variable definitions as they were upon entry into the program. The Excel code is running in a Citrix environment which may effect timing but shouldn't have any other effect on code execution.

I deleted the other code versions for brevity. The following code is what has worked. The key issue is that the workbook to be saved must be the active workbook when the SaveAs method is invoked.

Private Sub cmdSaveUpdatedWB_Click() On Error GoTo Err_cmdSaveUpdatedWB_Click

Dim wbSave As Workbook Dim wsActive As Worksheet Dim sNWBName As String

Application.DisplayAlerts = False

sNWBName = txtUpdWorkbookName.Value

Set wbSave = gwbTarget
wbSave.Activate
Set wsActive = wbSave.ActiveSheet

wbSave.SaveAs fileName:=sNWBName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True

frmLoanWBMain.Show
gwbTarget.Close
Set gwbTarget = Nothing

gWBPath = ""
gWBName = ""

lblWorkbookSaved.Enabled = True
cmdUpdateAnotherWorkbook.Visible = True

Exit_cmdSaveUpdatedWB_Click:

Set wbSave = Nothing
Exit Sub

Err_cmdSaveUpdatedWB_Click: Dim strErrMsg As String

strErrMsg = "Error Number: " & Err.Number & " Desc: " & Err.Description & vbCrLf & _
        "Source:" & Err.Source & vbCrLf & _
        "Updating Workbook: " & vbCrLf & "      " & gwbTarget.Name & vbCrLf & _
        "Selected Worksheet: " & gwsTrgSheet.Name & vbCrLf & _
        "Active Workbook: " & vbCrLf & "      " & ActiveWorkbook.Name & vbCrLf & _
        "Worksheet: " & ActiveSheet.Name & vbCrLf & _
        "Code Segment: cmdSaveUpdatedWB_Click event handler"

RecordErrorInfo strErrMsg

Resume Exit_cmdSaveUpdatedWB_Click

End Sub

where do you declare gwbTarget? Is it the same workbook the code is stored in? — CallumDA
It's a global variable that is declared in a code module that is contained in the workbook that contains the code. — Thope
Firstly you can remove your activate statement - you don't need that. Check if your gwbTarget is defined by putting a break point on the SaveAs line and checking your locals when the code stops there. My guess is that gwbTarget is Nothing — CallumDA
@CallumDA that would be a runtime error 91 on the .Activate call. But yeah, no need to .Activate it at all. — Mathieu Guindon
@Mat'sMug...one day I'll know all the different error codes off by heart! — CallumDA

CallumDA CallumDA · Accepted Answer · 2017-02-07T21:25:15

Why don't you start with something like this

Private Sub cmdSaveUpdatedWB_Click()
    Dim gwbTarget As Workbook
    Set gwbTarget = Workbooks("workbook_name.xlsm") 'correct extension needed, workbook must be open

    wb.SaveAs Filename:=gwbTarget.Path, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    MsgBox "Last saved: " & gwbTarget.BuiltinDocumentProperties("Last Save Time")
End Sub

Change one thing at a time to make it more like yours and hopefully it'll all work fine!

Update

As per the comments. If you are trying to open, update and close hundreds of workbooks. You can use this as a guide:

Sub ChangeWorkbooks()
    Application.ScreenUpdating = False

    Dim wbPaths As Range, wbSaveFilenames As Range
    With Sheet1 'you will need to update this and the ranges below
        Set wbPaths = .Range("A1:A650") 'including file extensions
        Set wbSaveFilenames = .Range("B1:B650") 'including file extensions
    End With

    Dim i As Integer, totalBooks As Integer
    Dim wbTemp As Workbook

    totalBooks = wbPaths.Rows.Count
    For i = 1 To totalBooks
        Application.StatusBar = "Updating workbook " & i & " of " & totalBooks 'display statusbar message to user
        Set wbTemp = Workbooks.Open(wbPaths.Cells(i, 1), False)

        'make changes to wbTemp here

        wbTemp.SaveAs wbSaveFilenames.Cells(i, 1)
        wbTemp.Close
    Next i
    Set wbTemp = Nothing

    Application.ScreenUpdating = True
    Applicaton.StatusBar = False
End Sub

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

1 Answers