CODEWITHSUNDEEP
excelvbafilefilesystemsdirectory
user525966
user525966

Reputation: 499

Iterate though all files of a certain path in Excel VBA?

I am trying to iterate over a bunch of .xlsx files in a particular drive such that any file fits the following format:

H:\[*FOLDER NAME*]\SpecificFolder\Finalized Plans\2019 Prefix[*].xlsx

In other words inside folders on the H drive with subfolders \SpecificFolder\Finalized Plans\ containing .xlsx files starting with the literal phrase "2019 Prefix". For example 

H:\Widget\SpecificFolder\Finalized Plans\2019 Prefix Howdyado Neighbor.xlsx

or

H:\Rofl672\SpecificFolder\Finalized Plans\2019 Prefix Bob.xlsx

Is this something I can somehow iterate over using Dir()? Some other way?

Is it something like Dir("H:\*\SpecificFolder\Finalized Plans\2019 Prefix*.xlsx")? (this is what I tried and it gave me the error "Bad file name or number" so it may not be the right syntax)

Upvotes: 0

Views: 249

Answers (2)

ASH
ASH

Reputation: 20302

There are MANY ways to do this. I think this will give you what you want.

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

See the link below for all details.

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

Click the link titled 'Download Now' to download a samaple file.

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

Here is a very fast approach using WScript, which does support wildcards. 

'Adapted from --> https://stackoverflow.com/a/31132876/4839827
Public Sub GetAllFilesMatchingPattern(StartingFolder As String, FolderPattern As String)
    If Right$(StartingFolder, 1) <> "\" Then StartingFolder = StartingFolder & "\"
    Dim StandardOutput As String
    Dim ws             As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim Files          As Variant
    StandardOutput = CreateObject("WScript.Shell").Exec("CMD /C DIR """ & StartingFolder & FolderPattern & """ /S /B /A:-D").StdOut.ReadAll

    If Not StandardOutput = vbNullString Then
        Files = Split(StandardOutput, vbCrLf)
        ws.Range("A1").Resize(UBound(Files), 1).Value2 = Application.Transpose(Files)
    End If
End Sub


Sub Example()
    GetAllFilesMatchingPattern "H:\", "*\SpecificFolder\Finalized Plans\2019 Prefix*.xlsx"
End Sub

Upvotes: 1

Related Questions