Method saveas of object workbook failed 1004 ошибка vba

Hello,

     I have code designed to create an archive of my main sheet (as a .xlsx) by copying the sheet > saving the copied sheet in a new workbook > doing things to the sheet within the new workbook > saving and closing new workbook then continuing the rest of my code. 

     Everything works as coded except when the user selects (or keeps selected) the same file location, in the SaveAs dialog, that the original file (with the running VBA) is in. It returns a «Method ‘SaveAs’ of object ‘_Workbook’ failed» error.

     I created an «If» check to see if the selected file location from the SaveAs dialog is the same as the file location of the original and was able to create an error handler (avoid the error), but not an error solution. I want to default to the same file location as the original, and regardless I want the user to be able to save into the same file location, especially since that is a very typical thing to do.

     Line (59) with error 1004:

ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook

     ShiftYear (what code is in) and PleaseWait are userforms, and «Troop to Task — Tracker» is the sheet I’m copying.

Code with error:

'<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'''Declare variables:
'General:
Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer
NewGenYear = 0: LastGenYear = 0: year_create_counter = 0
'Personnel:
Dim cell_person As Range, cell_num As Range
Dim cell_num_default As Range
'Archive:
Dim Sheet_Archive As Worksheet, ShVal As Integer
Dim ObFD As FileDialog
Dim File_Name As String
Dim PathAndFile_Name As String
Dim Shape_Clr As Shape
Dim cell_color_convert As Range

'<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'Set then launch SaveAs dialog:
If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked:
    For Each Sheet_Archive In ThisWorkbook.Sheets
    Select Case Sheet_Archive.CodeName
    Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7"
    ShVal = Sheet_Archive.Name
    If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then
    File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Archive Year(s) - Personnel Tracker"
        .ButtonName = "A&rchive"
        .InitialFileName = ThisWorkbook.Path & "" & File_Name 'Default file location and File Name
        .FilterIndex = 1 'File Type (.xlsx)
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then
        MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _
        , vbExclamation, "Year Shift & Creation - Personnel Tracker"
        '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT>
        Exit Sub
        Else
        PathAndFile_Name = .SelectedItems(1)
        End If
    End With
    Application.DisplayAlerts = False
    
'Load year to be archived:
    Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name
    Worksheets("Formula & Code Data").Range("I13").Value = "No"
    Call Load_Year.Load_Year

'Copy Troop to Task - Tracker sheet into new workbook and format:
    PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..."
    DoEvents
    File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "")) 'Update File Name to user's input
    ThisWorkbook.Sheets("Troop to Task - Tracker").Copy

    ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate

    '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK>

    Excel.Workbooks(File_Name).Activate
    Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close
    Application.DisplayAlerts = True
    End If
    End Select
    If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _
    Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _
    And ShVal <> ShiftYear.Shift_3.Value Then
    PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete"
    DoEvents
    Else: PleaseWait.Label2.Caption = "Initailizing archive ..."
    DoEvents: End If
    Next Sheet_Archive
ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked:
    'Do Nothing
End If 'Archive <=5 year(s) data externally - END

'<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

Code with error handler:

'<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'''Declare variables:
'General:
Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer
NewGenYear = 0: LastGenYear = 0: year_create_counter = 0
'Personnel:
Dim cell_person As Range, cell_num As Range
Dim cell_num_default As Range
'Archive:
Dim Sheet_Archive As Worksheet, ShVal As Integer
Dim ObFD As FileDialog
Dim File_Name As String
Dim PathAndFile_Name As String
Dim Shape_Clr As Shape
Dim cell_color_convert As Range

'<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'Set then launch SaveAs dialog:
If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked:
    For Each Sheet_Archive In ThisWorkbook.Sheets
    Select Case Sheet_Archive.CodeName
    Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7"
Archive_Error:
    ShVal = Sheet_Archive.Name
    If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then
    File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Archive Year(s) - Personnel Tracker"
        .ButtonName = "A&rchive"
        .InitialFileName = ThisWorkbook.Path & "" & File_Name 'Default file location and File Name
        .FilterIndex = 1 'File Type (.xlsx)
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then
        MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _
        , vbExclamation, "Year Shift & Creation - Personnel Tracker"
        '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT>
        Exit Sub
        Else
        PathAndFile_Name = .SelectedItems(1)
        End If
    End With
    Application.DisplayAlerts = False
    
'Load year to be archived:
    Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name
    Worksheets("Formula & Code Data").Range("I13").Value = "No"
    Call Load_Year.Load_Year

'Copy Troop to Task - Tracker sheet into new workbook and format:
    PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..."
    DoEvents
    File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "")) 'Update File Name to user's input
    ThisWorkbook.Sheets("Troop to Task - Tracker").Copy
    
    If PathAndFile_Name = ThisWorkbook.Path & "" & File_Name Then 'Error handler
Archive_Error_Actual:
    MsgBox "You cannot save into the same location as this Tracker, in this version. Please select a different file location." _
    , vbExclamation, "Year Shift & Creation - Personnel Tracker"
    'UPDATE MESSAGE AND FIGURE OUT WAY TO FIX RUNTIME ERROR WHEN SAVING TO SAME LOCATION AS THE TRACKER!!!
    ActiveWorkbook.Close savechanges:=False
    GoTo Archive_Error
    End If
    On Error GoTo Archive_Error_Actual
    ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate

    '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK>

    Excel.Workbooks(File_Name).Activate
    Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close
    Application.DisplayAlerts = True
    End If
    End Select
    If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _
    Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _
    And ShVal <> ShiftYear.Shift_3.Value Then
    PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete"
    DoEvents
    Else: PleaseWait.Label2.Caption = "Initailizing archive ..."
    DoEvents: End If
    Next Sheet_Archive
ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked:
    'Do Nothing
End If 'Archive <=5 year(s) data externally - END

'<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

Any solution to this is much appreciated!

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

Hello,

     I have code designed to create an archive of my main sheet (as a .xlsx) by copying the sheet > saving the copied sheet in a new workbook > doing things to the sheet within the new workbook > saving and closing new workbook then continuing the rest of my code. 

     Everything works as coded except when the user selects (or keeps selected) the same file location, in the SaveAs dialog, that the original file (with the running VBA) is in. It returns a «Method ‘SaveAs’ of object ‘_Workbook’ failed» error.

     I created an «If» check to see if the selected file location from the SaveAs dialog is the same as the file location of the original and was able to create an error handler (avoid the error), but not an error solution. I want to default to the same file location as the original, and regardless I want the user to be able to save into the same file location, especially since that is a very typical thing to do.

     Line (59) with error 1004:

ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook

     ShiftYear (what code is in) and PleaseWait are userforms, and «Troop to Task — Tracker» is the sheet I’m copying.

Code with error:

'<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'''Declare variables:
'General:
Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer
NewGenYear = 0: LastGenYear = 0: year_create_counter = 0
'Personnel:
Dim cell_person As Range, cell_num As Range
Dim cell_num_default As Range
'Archive:
Dim Sheet_Archive As Worksheet, ShVal As Integer
Dim ObFD As FileDialog
Dim File_Name As String
Dim PathAndFile_Name As String
Dim Shape_Clr As Shape
Dim cell_color_convert As Range

'<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'Set then launch SaveAs dialog:
If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked:
    For Each Sheet_Archive In ThisWorkbook.Sheets
    Select Case Sheet_Archive.CodeName
    Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7"
    ShVal = Sheet_Archive.Name
    If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then
    File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Archive Year(s) - Personnel Tracker"
        .ButtonName = "A&rchive"
        .InitialFileName = ThisWorkbook.Path & "" & File_Name 'Default file location and File Name
        .FilterIndex = 1 'File Type (.xlsx)
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then
        MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _
        , vbExclamation, "Year Shift & Creation - Personnel Tracker"
        '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT>
        Exit Sub
        Else
        PathAndFile_Name = .SelectedItems(1)
        End If
    End With
    Application.DisplayAlerts = False
    
'Load year to be archived:
    Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name
    Worksheets("Formula & Code Data").Range("I13").Value = "No"
    Call Load_Year.Load_Year

'Copy Troop to Task - Tracker sheet into new workbook and format:
    PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..."
    DoEvents
    File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "")) 'Update File Name to user's input
    ThisWorkbook.Sheets("Troop to Task - Tracker").Copy

    ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate

    '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK>

    Excel.Workbooks(File_Name).Activate
    Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close
    Application.DisplayAlerts = True
    End If
    End Select
    If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _
    Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _
    And ShVal <> ShiftYear.Shift_3.Value Then
    PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete"
    DoEvents
    Else: PleaseWait.Label2.Caption = "Initailizing archive ..."
    DoEvents: End If
    Next Sheet_Archive
ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked:
    'Do Nothing
End If 'Archive <=5 year(s) data externally - END

'<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

Code with error handler:

'<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'''Declare variables:
'General:
Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer
NewGenYear = 0: LastGenYear = 0: year_create_counter = 0
'Personnel:
Dim cell_person As Range, cell_num As Range
Dim cell_num_default As Range
'Archive:
Dim Sheet_Archive As Worksheet, ShVal As Integer
Dim ObFD As FileDialog
Dim File_Name As String
Dim PathAndFile_Name As String
Dim Shape_Clr As Shape
Dim cell_color_convert As Range

'<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

'Set then launch SaveAs dialog:
If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked:
    For Each Sheet_Archive In ThisWorkbook.Sheets
    Select Case Sheet_Archive.CodeName
    Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7"
Archive_Error:
    ShVal = Sheet_Archive.Name
    If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then
    File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Archive Year(s) - Personnel Tracker"
        .ButtonName = "A&rchive"
        .InitialFileName = ThisWorkbook.Path & "" & File_Name 'Default file location and File Name
        .FilterIndex = 1 'File Type (.xlsx)
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then
        MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _
        , vbExclamation, "Year Shift & Creation - Personnel Tracker"
        '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT>
        Exit Sub
        Else
        PathAndFile_Name = .SelectedItems(1)
        End If
    End With
    Application.DisplayAlerts = False
    
'Load year to be archived:
    Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name
    Worksheets("Formula & Code Data").Range("I13").Value = "No"
    Call Load_Year.Load_Year

'Copy Troop to Task - Tracker sheet into new workbook and format:
    PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..."
    DoEvents
    File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "")) 'Update File Name to user's input
    ThisWorkbook.Sheets("Troop to Task - Tracker").Copy
    
    If PathAndFile_Name = ThisWorkbook.Path & "" & File_Name Then 'Error handler
Archive_Error_Actual:
    MsgBox "You cannot save into the same location as this Tracker, in this version. Please select a different file location." _
    , vbExclamation, "Year Shift & Creation - Personnel Tracker"
    'UPDATE MESSAGE AND FIGURE OUT WAY TO FIX RUNTIME ERROR WHEN SAVING TO SAME LOCATION AS THE TRACKER!!!
    ActiveWorkbook.Close savechanges:=False
    GoTo Archive_Error
    End If
    On Error GoTo Archive_Error_Actual
    ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate

    '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK>

    Excel.Workbooks(File_Name).Activate
    Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close
    Application.DisplayAlerts = True
    End If
    End Select
    If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _
    Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _
    And ShVal <> ShiftYear.Shift_3.Value Then
    PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete"
    DoEvents
    Else: PleaseWait.Label2.Caption = "Initailizing archive ..."
    DoEvents: End If
    Next Sheet_Archive
ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked:
    'Do Nothing
End If 'Archive <=5 year(s) data externally - END

'<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE>

Any solution to this is much appreciated!