CODEWITHSUNDEEP
vbaexcel
Jay
Jay

Reputation: 646

Excel VBA DIR only looping once

I created a VBA that loops through excel files in a stored folder. It loops only through the ones that has the word "BAD" in it's file name. My Problem is DIR seem to only work once? StrFile = "" after the line StrFile = Dir. I already tried StrFile = Dir() and StrFile = Dir(StrFile)

    If Application.FileDialog(msoFileDialogFolderPicker).Show <> 0 Then
        TheFolder = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

        Dim StrFile As String
        StrFile = Dir(TheFolder & "\BAD *")
        Do While Len(StrFile) > 0
          'Do Something
          StrFile = Dir 'Check next file.
          'This is where it fails, it doesn't check the 2nd file
        Loop
    Else
        End
    End If

UPDATE: I noticed something that could be the cause of the problem, and I wasn't that familiar with DIR().

Inside the Do While scrip where it says 'Do Somethingis actually a call to another function and that function has a Example = Dir(ThePath). When I try to comment-out or disable the call to that function the script works. So I guess DIR can only be used once? If you use DIR() to loop through files in a folder you can't use another/different DIR()?

Upvotes: 2

Views: 2811

Answers (1)

Trigger
Trigger

Reputation: 145

This goes through every file in a folder and checks if it's interested (in this case if it's a csv - use If Lcase(Left(thing.path, 4)) = "bad " then for your requirements.).

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("c:\windows")
For Each thing in f.files
    If LCase(Right(thing.path, 3)) = "csv" Then
        msgbox thing.path
        Set doc = GetObject(thing.path)
        msgbox Doc.name
        Doc.close
        Set Doc = Nothing
    End If
Next

If you read help it says use the FSO and not the native functions.

A new feature for Visual Basic is the File System Object (FSO) object model, which provides an object-based tool for working with folders and files. This allows you to use the familiar object.method syntax with a rich set of properties, methods, and events to process folders and files, in addition to using the traditional Visual Basic statements and commands.

Upvotes: 3

Related Questions