VBA - Application-defined error with Dir()

Question

I'm having issues with the Dir() function in Excel VBA. When I run the below code, the code repeats as expected until there are no more files that match my condition. Once the Dir() call should run out of files to return I get a Run-time error '1004': Application-defined or object-defined error. I've tried all the other answers I could find that even remotely seemed like they applied to my situation.

Dim dataIter As Range
Dim outputIter As Range
Dim fileName As String
Dim wkbk As Workbook

fileName = Dir(path & "\" & "*.xlsm")
Do While Len(fileName) > 0
    Set wkbk = Workbooks.Open(path & "\" & fileName)
    Set dataIter = wkbk.Sheets(1).Range("A1")
    Do While dataIter.Address <> wkbk.Sheets(1).Range("TITLE").Address
        If dataIter.Value <> "" Then
            'Extract the data I need
            Set outputIter = outputIter.Offset(1)
        End If
        Set dataIter = dataIter.Offset(1)
    Loop
    wkbk.Close False
    fileName = Dir() 'Fails here
Loop

Valid assumptions:

All needed sheets are the first in each workbook
All sheets have a properly placed "TITLE" named range

What could be causing this and what can I do to fix this?

K.Dᴀᴠɪs · Accepted Answer · 2018-01-31 14:36:43Z

1

Here's a quick fix using some error handling, since your code runs as expected until the end.

Sub test()

    Dim dataIter As Range
    Dim outputIter As Range
    Dim fileName As String
    Dim wkbk As Workbook

    fileName = Dir(path & "\" & "*.xlsm")
    Do While Len(fileName) > 0
        Set wkbk = Workbooks.Open(path & "\" & fileName)
        Set dataIter = wkbk.Sheets(1).Range("A1")
        Do While dataIter.Address <> wkbk.Sheets(1).Range("TITLE").Address
            If dataIter.Value <> "" Then
                'Extract the data I need
                Set outputIter = outputIter.Offset(1)
            End If
            Set dataIter = dataIter.Offset(1)
        Loop
        wkbk.Close False
        On Error GoTo ErrHandler
        fileName = Dir() 'Fails here
        On Error GoTo 0
    Loop

    ' Any Remaining code

    Exit Sub
ErrHandler:
    If Err.Number = 1004 Then
        fileName = vbNullString
        Err.Clear
        Resume Next
    End If

End Sub

edited Jan 31, 2018 at 14:36

answered Oct 31, 2017 at 23:22

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Taelsin Over a year ago

This worked! Thank you. Any idea why Dir is throwing this error?

K.Dᴀᴠɪs Over a year ago

Because at the end there is no Dir(). You already went through all of them therefore Dir() doesn’t exist.

Collectives™ on Stack Overflow

VBA - Application-defined error with Dir()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related