Run time error 1004 method 'add' of object 'workbooks' failed

Question

I am writting a code for a stock register for input and output records. I just finished after few days, and everytime I tested it it was working perfectly. But, today after I finished the complete code, I had a problem just trying to open a file .xlsm to do register a modification.

Error message:

run time error 1004 Method 'add' of object 'Workbooks' failed

Here you have the subroutine where excel is showing me the error message. In essence the highlighted line to debug is Set book = app.Workbooks.Add(FileOUT)

    Sub Register_Deleted_Location(nData As Integer, DATA_ARRAY As Variant, User As String, PATH_HISTORY As String, HISTORY_FILE As String, TAB_HISTORY As String, RegError As Boolean)
'
    'Getting the number of columns for the data in the file
    Call Definition_Columns
    '
    RegError = False
    '
    'Open HISTROY_FILE
    Dim app As New Excel.Application
    Dim book As Excel.Workbook
    Dim FileOUT As String, PathOUT As String, TAB_OUT As String
    '
    app.Visible = False 'Visible is False by default, so this isn't necessary
    '
    FileOUT = PATH_HISTORY & "\" & HISTORY_FILE
    '
    If (Not FileExists(FileOUT)) Then
        MsgBox ("File does not exist in the Path" & vbNewLine & vbNewLine & FileOUT & vbNewLine & vbNewLine & "Please contact your BSO.")
        RegError = True
        GoTo End_Reg_Del
    End If
    If (FileLocked(FileOUT)) Then
        MsgBox ("File is locked, it is probably that another user is updating it, please try again in few seconds." & vbNewLine & vbNewLine & FileOUT & vbNewLine & vbNewLine & "If the problem persists, please contact your BSO")
        RegError = True
        GoTo End_Reg_Del
    End If

    Set book = app.Workbooks.Add(FileOUT)
    '
    TAB_OUT = TAB_HISTORY
    '
    'Unprotect sheet in HISTROY_FILE
    book.Sheets(TAB_OUT).Unprotect
    '
    'Looking for the first empty row in order to add the new register
    FER = First_Empty_Row_InColumn(book, TAB_OUT, "A", First_Row_For_Registers, 10000)
    '
    'Update information in HISTROY_FILE
    For i = 1 To nData
        book.Sheets(TAB_OUT).Cells(FER, i).Value = DATA_ARRAY(i)
    Next i
    '
    'Register the Date/Time/User Deleted
    book.Sheets(TAB_OUT).Cells(FER, Col_DTD).Value = Format(Now(), "DD/MM/YYYY")
    book.Sheets(TAB_OUT).Cells(FER, Col_TID).Value = Format(Now(), "HH:MM:SS")
    book.Sheets(TAB_OUT).Cells(FER, Col_USD).Value = User
    '
    'Fixing the problem with the date after copy the date from another file
    book.Sheets(TAB_OUT).Cells(FER, Col_DTC).NumberFormat = "DD/MM/YYYY"
    'book.Sheets(TAB_OUT).Cells(FER, Col_TIC).NumberFormat = "HH:MM:SS"
    book.Sheets(TAB_OUT).Cells(FER, Col_DTM).NumberFormat = "DD/MM/YYYY"
    'book.Sheets(TAB_OUT).Cells(FER, Col_TIM).NumberFormat = "HH:MM:SS"
    '
    'Update Date/Time of the last modification in HISTORY_FILE
    book.Sheets(TAB_OUT).Cells(Row_DT_File, Col_DT_File).Value = Format(Now(), "DD/MM/YYYY") & "  " & Format(Now(), "HH:MM:SS")
    '
    Call Sorting_File(book, TAB_OUT, "AX", "AY")
    '
    'Protect sheet in STOCK OUT file
    book.Sheets(TAB_OUT).Protect
    '
    'Save and close STOCK OUT File
    app.DisplayAlerts = False
    book.SaveAs Filename:=FileOUT, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    app.DisplayAlerts = True
    book.Close SaveChanges:=True
    app.Quit
    '
End_Reg_Del:
End Sub

Could somebody help me to understand this error and how to fix it?. I spent the wholde day trying to find an answer by google it, but nothing helps at this moment.

I would appreciate any tip.

Best regards,

Rodrigo Sassaki Rodrigo Sassaki · Accepted Answer · 2018-02-28T21:12:12

You probably have a problem with your fileOut string. The ".Add" receives only one parameter, which is a template file. If it's failing, probably the parameter is incorrect (the file address could either be invalid or the file itself is not a template).

Stop your code after assigning value to the fileOut string and check its value.

Run time error 1004 method 'add' of object 'workbooks' failed

1 Answers