Using VBA Folder Picker and For Each statement

Question

I'm currently trying to work through a solution that will allow the user to determine a folder on their computer where certain files are stored (using folder picker tool).

Once that folder is set, I want to look there for a file that has a name like "File 1". Please note, I don't have the full file name, it will change rom person to person. For example the full name may be "123456 File1 abcde.xlsx"

Current attempt is as follows (this produced an error)

Sub SelectFolder()

    Dim wb As Workbook
    Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then    ' if OK is pressed

        End If
    End With

    If sFolder <> "" Then    ' if a file was chosen
        For Each wb In sFolder
            If wb.Name Like ("Extract 1 Dividends") Then
                wb.Open
            End If
        Next wb

    End Sub

This is producing an error as once the user selects and set sFolder string, I cant seem to look in that string for the file I wan't. I get the following error

For each may only iterate over a collection object or an array

Is there an alternative strategy I can try?

Use `dir$()` to loop through files: https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba with the wildcard `"*book 1*.xlsx"` — Alex K., Jun 01 '18 at 15:16
After indenting your code, it is visible it does not have `End If`. — Vityata, Jun 01 '18 at 15:16

Vityata · Answer 1 · 2018-06-01T15:32:39.787

The error is quite explanatory - you are asking VBA to loop each workbook of a String. The only thing that is "loopable" in a string are its chars. Thus, VBA has no idea that the String is actually a folder in your PC, containing workbooks.

This is a way to loop through the Excel files in a given folder:

Option Explicit

Sub SelectFolder()

    Dim wb As Workbook
    Dim fileInFolder As String        

    fileInFolder = Dir("C:\Users\user\Desktop\Neuer Ordner\")

    Do While Len(fileInFolder) > 0
        If Right(fileInFolder, 4) = "xlsx" Then
            Debug.Print fileInFolder
        End If
        fileInFolder = Dir
    Loop

End Sub

It uses the fact, that your Excel files are probably with extension xlsx, thus it checks the last 4 letters of the filename.

As far as you are trying to open the workbooks and do something with them, this is a good guide how to do it:

Option Explicit

Sub SelectFolder()

    Dim wb As Workbook
    Dim fileInFolder As String
    Dim initialDir As String

    initialDir = "C:\Users\user\Desktop\Neuer Ordner\"
    fileInFolder = Dir(initialDir)

    Do While Len(fileInFolder) > 0
        If Right(fileInFolder, 4) = "xlsx" Then
            Set wb = Workbooks.Open(initialDir & fileInFolder)
            Debug.Print wb.Name
            Debug.Print wb.Path
        End If
        fileInFolder = Dir
    Loop

    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            Debug.Print wb.Name & " is closed!"
            wb.Close savechanges:=True
        End If
    Next wb

End Sub

At the end of the code, it loops through all the workbooks, open in the same instance and closes every one of them, except for the workbook in which the code is residing (If wb.Name <> ThisWorkbook.Name Then).

Using VBA Folder Picker and For Each statement

1 Answers1