Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Code throwing 1004 Error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code throwing 1004 Error

    This code has worked for years until recently (this month). Now all of a sudden it is throwing the dreaded Runtime Error 1004 "Application-defined or Object-defined error"

    I have tried everything I can think of but the error keeps showing up every time I run the code. Can anyone shed some light on this error from the code below, please.

    I'm using Office 365

    Code:
    Sub DataExtract()
    
    With Application
     .DisplayAlerts = False
     .ScreenUpdating = False
    End With
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile  As Object
    Dim wb As Workbook
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\Statements\")
    
    For Each objFile In objFolder.Files
        If InStr(objFile, ".xls") Then
            Workbooks.Open (objFile)
        End If
        Set wb = ActiveWorkbook
        i = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row + 1
        j = wb.Sheets("Statement").Cells(Rows.Count, "C").End(xlUp).Row
        If wb.Sheets("Statement").Range("C13") <> vbNullString Then
            wb.Sheets("Statement").Range("A13:F" & j).Copy
            Sheet1.Range("B" & i).PasteSpecial xlPasteValuesAndNumberFormats
            wb.Sheets("Statement").Range("I13:I" & j).Copy
            Sheet1.Range("H" & i).PasteSpecial xlPasteValuesAndNumberFormats
            k = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
            wb.Sheets("Statement").Range("F6").Copy
            Sheet1.Range("A" & i & ":A" & k).PasteSpecial
            Application.CutCopyMode = False
        End If
        wb.Close
    Next
    
    Sheet1.Range("A1") = "All Invoices: " & Format(Date, "mmmm d, yyyy") & ", Week " & Format(Date, "ww")
    'Align & Format Date text cell
    Range("A1").RowHeight = 30
    Range("A1").Font.Name = "Arial"
    Range("A1").Font.Size = 16
    Range("A1").IndentLevel = 1
    Range("A1").VerticalAlignment = xlCenter
    Range("A1").HorizontalAlignment = xlGeneral
    
    MsgBox "Task Complete!"
    
    With Application
     .EnableEvents = True
     .ScreenUpdating = True
     .Calculation = xlCalculationAutomatic
    End With
    
    End Sub
    Thanks in advance...
    Terry E

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,117
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    Which line gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Quote Originally Posted by Fluff View Post
    Which line gives the error?
    I'm not sure. I tried stepping through the code and it does everything until the "Msg Box" line. It paste all the lines pulled form all the sheets. It fails, I guess, right before the date part. I say that because I commented out all the date stuff and the 1004 error was still thrown. Is there some testing that I can do that will narrow down the problem code? I'm not a VBA programmer but I am a geek and can follow code blocks.

    It runs through all the statements and pulls all the invoices (what it's supposed to do) but at the very end of the run the error is thrown. The date doesn't change and the "Msg Box" does now display "Task Complete" but I can't figure out what line is causing the problem. This all worked until 2 weeks ago. I run this every Monday and it worked June 3rd an 10th but failed on the 17th.

    Terry E

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,117
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    When you get the error click debug. What line of code is highlighted?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Not sure how to do that. I'm running the code from the VBA screen but when the error is displayed I have "ok or help" as the only options. I can't click on debug with the popup error and when I click on ok to get rid of the error box clicking debug does nothing. Is there something I'm missing?

    Stepping through the code does not highlight a line after clicking the "OK" button. So I don't know how to get it to highlight the problem line of code.

    I've even tried reducing the number of workbooks in the folder, though I've had over 1500 in the folder before and it ran without problems, currently there is only 613 workbooks in the folder.

    Terry E

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,117
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    If you get an Error 1004 the pop-up window should have buttons marked End, Debug & Help (along with Continue which should be greyed out) & the title of the window should be "Microsoft Visual Basic"
    Do you not get that?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    Quote Originally Posted by Fluff View Post
    If you get an Error 1004 the pop-up window should have buttons marked End, Debug & Help (along with Continue which should be greyed out) & the title of the window should be "Microsoft Visual Basic"
    Do you not get that?
    It says

    Microsoft Visual Basic for Applications
    Run-time error '1004'
    Application-defined or Object-defined error

    And only "ok" and "help"

    Terry E

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,117
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Code throwing 1004 Error

    What platform are you using?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    I'm using Office 365 Business subscription. All Office updates have been done.

    Terry E

  10. #10
    New Member
    Join Date
    Jul 2015
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code throwing 1004 Error

    I think I found something but don't know how to fix it. I have some other code (below) that pulls a list of all the files in the folder.

    This keeps showing in the list EVEN when the file is removed from the folder:

    ~$SCM ENTERPRISES.xlsm
    The path: C:\Users\accou\Documents\Customer Spreadsheets\Testing\Statements\~$SCM ENTERPRISES.xlsm

    I've tried removing the file from the folder, it still shows up. I've tried recreating the spreadsheet from one I know works but that did not help either. The file does not exist in the folder so I don't know why it keeps listing it.

    Code:
    Sub GetListOfFileNames()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder("C:\Users\accou\Documents\Customer Spreadsheets\Testing\Statements")
    i = 1
    'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
        'print file name
        Cells(i + 1, 1) = objFile.Name
        'print file path
        Cells(i + 1, 2) = objFile.Path
        i = i + 1
    Next objFile
    End Sub
    I'm pretty sure this is what is causing all code issues but for the life of me I can't figure out why, even when the files is removed from the folder, it keeps showing in the list.

    Terry E

    UPDATED:

    I just tried recreating the macro file but it still pulls that ~$SCM ENTERPRISES.xlsm file even though that file is not in the folder. Any idea what could cause this? I've never seen anything like this before.

    Terry E
    Last edited by Terry Echols; Jun 20th, 2019 at 12:51 PM. Reason: New information added

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •