VBA: Cycle through files in a folder, and take information from all that contain a certain string

Question

I've been trying to write a code for VBA that will cycle through a folder, given a user inputted folder path, and paste some information, like a range of cells, and the file name in my work book based on whether or not a filename contains a specific string.

Currently I have a code that will take a folder path inputted in cell 1,1 in excel, but it will return all filenames, not just filenames containing "abc" for example. It's been a while since I used VBA, and this is my way of coming back to it.

Sub getFile()

Dim MyFolder As String
Dim file As String

MyFolder = Cells(1, 1)

file = Dir(MyFolder & ".xl??")

Dim col As Integer

col = 2

Do While file <> ""

   Cells(3, col) = file
   col = col + 1
   file = Dir()

Loop

End Sub

My problem is that I haven't been able to find a way to incorporate an "If" to say only use files that contain the string "abc"

Thanks everybody!

Answer 1

Here is a great sample that will do everything you want and a lot more, if you just modify the code a bit....

http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

Download the sample file from the link named 'Download Now'.

Sub GetFilesInFolder(SourceFolderName As String)

'--- For Example:Folder Name= "D:\Folder Name\"

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub



Sub GetFilesInFolder(SourceFolderName As String, Subfolders As Boolean)

'--- For Example:Folder Name= "D:\Folder Name\" and Flag as Yes or No

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File
'Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    '--- This is the Function to go each and Every Folder and get the Files. This is a Nested-Function Calling.

    If Subfolders = True Then
        For Each SubFolder In SourceFolder.Subfolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

Answer 2

I do not think you have made yourself clear, but I believe you want to loop through file that contain "abc" in their name, is it correct?!

If so, use asterisks in Dir function when declaring variable "file", like this:

file = Dir(MyFolder & "*abc*.xl*")

Answer 3

Try using

file = Dir(MyFolder & "*abc*.xl??")

instead of

file = Dir(MyFolder & ".xl??")

_{Just to close this post and in order not to have another open question (with the answer being in a comment) I re-post my comment here as a solution.}