Code throwing 1004 Error

[Terry Echols]

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

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

 

[Fluff]

Which line gives the error?

 

[Terry Echols]

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]

When you get the error click debug. What line of code is highlighted?

 

[Terry Echols]

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]

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]

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]

What platform are you using?

 

[Terry Echols]

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

Terry E

 

[Terry Echols]

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.

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