Code throwing 1004 Error

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,105
Office Version
365
Platform
Windows
Which line gives the error?
 

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,105
Office Version
365
Platform
Windows
When you get the error click debug. What line of code is highlighted?
 

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,105
Office Version
365
Platform
Windows
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?
 

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,105
Office Version
365
Platform
Windows
What platform are you using?
 

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,823
Messages
5,470,988
Members
406,738
Latest member
maximusben

This Week's Hot Topics

Top