ActiveWorkbook SaveAs excel 2013 1004 error

0 votes

I am getting a

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

The code works in excel 2010. I only get this error message in excel 2013. The error message appears after trying to run the follow line.

    ActiveWorkbook.SaveAs FolderPath & SaveName & NewSaveExt, 52

Background:
The spreadsheet is an .xls
When using the Saveas I am changing it to .xlsm
I have tried it with a .xls extension and fileformat 56 and it still falls over.
I am using code from the resources listed in the code.
I am saving the file to the same folder the workbook is in.
The orignal file name is: Financial Report as at month N.xls
The new filename is : Financial Report 1516 as at month 8.xlsm

    Sub SaveNewVersion_Excel()
    'PURPOSE: Save file, if already exists add a new version indicator to                 filename
    'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

    Dim FolderPath As String
    Dim myPath As String
    Dim SaveName As String
    Dim SaveExt As String
    Dim NewSaveExt As String
    Dim VersionExt As String
    Dim Saved As Boolean
    Dim x As Long

    TestStr = ""
    Saved = False
    x = 0
    NewSaveExt = ".xlsm"
    'Version Indicator (change to liking)
      VersionExt = "_v"

    'Pull info about file
      On Error GoTo NotSavedYet
        myPath = ActiveWorkbook.FullName
        myFileName = "Financial Report " & FileFinancialYear & " as at month         " & MonthNumber
        FolderPath = Left(myPath, InStrRev(myPath, "\"))
        SaveExt = "." & Right(myPath, Len(myPath) - InStrRev(myPath, "."))
      On Error GoTo 0



    'Determine Base File Name
      If InStr(1, myFileName, VersionExt) > 1 Then
        myArray = Split(myFileName, VersionExt)
        SaveName = myArray(0)
      Else
        SaveName = myFileName
      End If


    'Test to see if file name already exists
      If FileExist(FolderPath & SaveName & SaveExt) = False Then

        ActiveWorkbook.SaveAs FolderPath & SaveName & NewSaveExt, 52
        Exit Sub
      End If

    'Need a new version made
      Do While Saved = False
        If FileExist(FolderPath & SaveName & VersionExt & x & SaveExt) =         False Then
          ActiveWorkbook.SaveAs FolderPath & SaveName & VersionExt & x & NewSaveExt, 52
          Saved = True
        Else
          x = x + 1
        End If
      Loop

    'New version saved
      MsgBox "New file version saved (version " & x & ")"

    Exit Sub

    'Error Handler
    NotSavedYet:
      MsgBox "This file has not been initially saved. " & _
        "Cannot save a new version!", vbCritical, "Not Saved To Computer"

    End Sub


    Function FileExist(FilePath As String) As Boolean
    'PURPOSE: Test to see if a file exists or not
    'RESOURCE: http://www.rondebruin.nl/win/s9/win003.htm

    Dim TestStr As String

    'Test File Path (ie "S:\Reports\Financial Report as at...")
      On Error Resume Next
        TestStr = Dir(FilePath)
      On Error GoTo 0

    'Determine if File exists
      If TestStr = "" Then
        FileExist = False
      Else
        FileExist = True
      End If

    End Function
Sep 24, 2022 in Others by Kithuzzz
• 38,010 points
1,295 views

1 answer to this question.

0 votes

Error reproduction: I was able to reproduce the error when trying to save a workbook with a FileName that already exist. This could happen because the code checks the existence of a file named with extension SaveExt (using Function FileExist) but then try to save it as a file named with extension NewSaveExt. If these extensions are not the same then it’s possible that the file named with extension NewSaveExt already exist raising the

Run-time error ‘1004’: Method ‘SaveAs’ of object ‘_Workbook’ failed.

However this alert:

A file ‘Financial Report as month .xlsm’ already exist in this location. Do you want to replace it?.

Should have been displayed before the error 1004

Unfortunately I cannot test the code posted in Excel 2010, but I personally think this behavior is not exclusive of Excel 2013.

Solution: If the objective is to always save the file as xlsm (value of NewSaveExt) then the code should validate the existence of a filename with that extension.

Additional comments about the code posted:

  1. It’s a best practice to declare all variables. These variables are not declared: TestStr, FileFinancialYear, MonthNumber, myFileName, myArray
  2. These lines are redundant as no need to initialize variables that have not been used as yet, so they are already holding their initialized value. TestStr = ""; Saved = False; x = 0
  3. Suggest to use constant instead of variables for these (see Variables & Constants) NewSaveExt = ".xlsm"; VersionExt = "_v"
  4. New workbooks are not detected as the error handler NotSavedYet which is supposed to be triggered when the ActiveWorkbook has not been saved before (i.e. a new workbook) never gets fired as none of the commands between the On Error statements generate an error when dealing with new workbooks (see On Error Statement). If the intention is not to save New Workbooks, as implied by the error handler NotSavedYet, then validate the Path of the ActiveWorkbook, it will be empty if the workbook has not has been saved before.
  5. The FileFinancialYear and MonthNumber variables never get populated.
  6. Suggest to use specific workbook properties for Path and Name instead of FullName (see Workbook Object (Excel))
  7. About the piece referred as Determine Base File Name

    a. Programming: There is no need for IF statement, just use the Split function and take the item 0. The Split function returns ”a single-element array containing the entireexpression” when the delimiter is not present in the expression” (i.e. VersionExt and myFileName respectively).

    b. Practicality: This piece seems to be redundant, as it’s meant to extract from variable myFileName the filename excluding the version and extension, however there is no such information in the variable as it has been populate just few lines above as:

    myFileName = "Financial Report " & FileFinancialYear & " as at month " & MonthNumber

    Therefore SaveName is always equal to myFileName

  8. The first version of the file is indexed as 0 instead of 1.

  9. The new indexed version will not always be the last index number + 1. If any of the previous versions is deleted or moved out to another folder as this version is missing the code will assign the missing version index to the latest file saved (see Fig. 1, note that time of version 3 is newer than versions 4 & 5)Correction of this point requires a more complex approach as such it is not included in the revised code below.

Requirements: Based on the above a revised code is written that complies with the following requirements:

  • The procedure resides in a standalone workbook.
  • Files are always saved as xlOpenXMLWorkbookMacroEnabled (Extension xlsm)
  • New workbooks will not be saved as new versions.
  • Variables FileFinancialYear and MonthNumber are hard coded as there is no indication of how they get populated (change as required).
  • The first time a file is saved and it does not exist in the source folder the file will be saved without the version number.
  • The index of the first version should be 1 (change to 0 if required).

    Option Explicit
    
    
    Sub Wbk_SaveNewVersion_Xlsm()
    Const kExt As String = ".xlsm"
    Const kVrs As String = "_v"
    
    Dim WbkAct As Workbook
    Dim iYear As Integer, bMnth As Byte, sWbkStd As String
    Dim sWbkPthNme As String, bVrs As Byte
    
        Rem Set Standard Workbook Name
        iYear = 2015    'Update Financial Year as required
        bMnth = 9       'Update Month as required
        sWbkStd = "Financial Report " & iYear & " as at month " & Format(bMnth, "00")
    
        Rem Validate Active Workbook
        Set WbkAct = ActiveWorkbook
        If WbkAct.Name = ThisWorkbook.Name Then GoTo HdeThs
        If WbkAct.Path = Empty Then GoTo NewWbk
    
        Rem Get Workbook Properties
        sWbkPthNme = WbkAct.Path & "\" & sWbkStd
    
        Rem Validate Base File Existance
        If Not (Fil_FileExist(sWbkPthNme & kExt)) Then
            WbkAct.SaveAs sWbkPthNme & kExt, xlOpenXMLWorkbookMacroEnabled
            MsgBox "A new workbook has been created: " & _
                vbLf & vbLf & Chr(34) & sWbkStd & kExt & Chr(34), _
                vbApplicationModal + vbInformation, "Workbook - Save a New Version - Xlsm"
            Exit Sub
        End If
    
        Rem Save a New Version
        bVrs = 1
        sWbkPthNme = sWbkPthNme & kVrs
        Do
            If Fil_FileExist(sWbkPthNme & bVrs & kExt) Then
                bVrs = 1 + bVrs
            Else
                WbkAct.SaveAs sWbkPthNme & bVrs & kExt, xlOpenXMLWorkbookMacroEnabled
                Exit Do
            End If
        Loop
    
        MsgBox "Version """ & bVrs & """ of workbook: " & _
            vbLf & vbLf & Chr(34) & sWbkStd & Chr(34) & " has been created.", _
            vbApplicationModal + vbInformation, "Workbook - Save a New Version - Xlsm"
    
    HdeThs:
        Call Wbk_Hide(ThisWorkbook)
    
    Exit Sub
    NewWbk:
        MsgBox "Active Workbook """ & WbkAct.Name & """ has not been saved as yet." & vbLf & _
            "A new version cannot be saved!", _
            vbApplicationModal + vbCritical, "Workbook - Save New Version - Xlsm"
    
    End Sub
    
    
    Private Function Fil_FileExist(sFullName As String) As Boolean
    Dim sDir As String
        Fil_FileExist = (Dir(sFullName) <> Empty)
    End Function
    
    
    Private Sub Wbk_Hide(Wbk As Workbook)
    Dim Wnd As Window
        For Each Wnd In Wbk.Windows
            Wnd.Visible = False
        Next
    End Sub
answered Sep 25, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,378 views
0 votes
1 answer

JavaScript API does not work for Excel 2013?

Each method in the Office.js APIs is ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,700 points
449 views
0 votes
1 answer

An error for excel download in Firefox for JMeter script recording

You shouldn't just copy and paste configuration ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,700 points
654 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
512 views
0 votes
1 answer

Spell number in excel 2013 using 2 rows

Hello, there are a few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
399 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,055 views
0 votes
1 answer

How do you calculate the Quintile for groups of rows in Excel?

Use this formula: =MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0)) To divide into whichever many ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,700 points
934 views
0 votes
1 answer

Camel case converter in an excel

Try this: =LOWER(LEFT(A1)) & MID(SUBSTITUTE(PROPER(A1),"_","") ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,700 points
2,979 views
0 votes
1 answer

=CONCATENATE() function giving a #VALUE! error (EXCEL 2013)

You can use the following: =A1&","&B1 TEXTJOIN would also ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,700 points
381 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
731 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP