• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scanning a folder and sub folders, FSO ? Dir ‼

Marc L

Excel Ninja
One day a colleague told me « How can you dare cheating on this project ?! »​
after an interview with the boss as we worked on parts on the same project.​
« What are you talking about ?‼ » I asked.​
- Your files scanning is running too fast, far faster than mine ! And I get more files, I'm stuck on how to remove unwanted results !
- Unwanted results ?! But what do you use to list files ?
- So obvious : File System Object library !
Then I started to laugh, very loudly.​
- Why that makes you so laugh ?!​
- 'Cause you choosed an external library, FSO is slower and VBA has yet all the necessary …
- Well, I know VBA but it can't work alone for this project ! By the way, what do you use ?
And I started to laugh again and after a while I could answer « Dir, only the Dir VBA function ‼ »​
-- ★ --
For example to scan workbooks in normal folders of an entire partition,​
FSO needed 2 minutes and 10 seconds and the same with Dir ran in less than 20s …​
So that's the drawback of using FSO but it scans all files and all folders whatever if they are marked as hidden, system, …​
On a network, FSO is also safer than using Dir VBA function.​
By default the Dir VBA function scans only normal files & folders until you set some specific attributes.​
The reason why before going further you must first read the VBA inner help of Dir function in order to understand how it works.​
-- ★ --
The faster way to scan files is not FSO neither Dir VBA function but using other Windows API via kernel32 for example.​
Needs to pay attention to declare them under VBA if 32 or 64 bits Excel version …​
But if you are here, you are maybe a VBA beginner so the easy way is to stay only with VBA inner feature which is fast enough for common needs.​
And also as FSO exists only under Windows but not on MAC …​
-- ★ --
So if you need to scan files / folders from a normal folder even within its sub folders on a local hard disk,​
Dir VBA function is your friend, yes you can trust me ‼​
In next demonstration (an old one but run as it is since years, good enough in order you can catch the main idea and mod it to fit your need …),​
DirList function returns a string array of files or folders from a starting point, a disk root or a specific folder.​
If nothing matches, DirList returns an empty array …​
For example DirList("*.xlsx", "D:\Data\") returns all workbooks from the folder D:\Data\ like D:\Data\Accounts2019.xlsx.​
But DirList("D:\Data\*.xlsx") returns only the workbooks names without the path like for example Accounts2019.xlsx …​
DirList does not scan sub folders as Dir VBA function can't …​
So if you need to scan a folder and its sub folders, you must use a particular procedure like DirScan in my demonstration​
that must be called from your main procedure, Demo on my side below …​
DirScan starts from the starting point called by the main procedure and,​
once it writes the DirList string array to the worksheet (only with a matching result),​
it scans its sub folders and for each sub folder found, DirScan calls itself for this sub folder, that's Recursivity ‼​
Demo scans as it is .xlsx files from D:\ but you can mod FIC & SRC constants in order to fit your test …​

-- ★ --
Paste this demonstration to the top of a worksheet module then run Demo procedure …​
Const COL = 3

Function DirList(SCAN$, Optional FOLD$, Optional ATTR As VbFileAttribute = vbNormal) As String()
         Dim B%, D$, F$, T$(), U&
    With Application
        If FOLD > "" Then
            If Right(FOLD, 1) <> .PathSeparator And Left(SCAN, 1) <> .PathSeparator Then FOLD = FOLD & .PathSeparator
            D = FOLD
            D = Left$(SCAN, InStrRev(SCAN, .PathSeparator))
        End If
    End With
        If SCAN = "." Then SCAN = "*."
        On Error Resume Next
        F = Dir$(FOLD & SCAN, ATTR)
    Do Until F = ""
        If ATTR And vbDirectory Then B = Right(F, 1) = "." Or (GetAttr(D & F) And vbDirectory) = 0
        If B = 0 Then U = U + 1: ReDim Preserve T(1 To U): T(U) = FOLD & F
        F = Dir$
         DirList = IIf(U, T, Split(""))
End Function

Sub DirScan(WHAT$, ByVal FROM$)
    V = DirList(WHAT, FROM)
    If UBound(V) > 0 Then Cells(Rows.Count, COL).End(xlUp)(2).Resize(UBound(V)).Value = Application.Transpose(V)
    For Each V In DirList("*", FROM, vbDirectory):  DirScan WHAT, V:  Next
End Sub

Sub Demo()
    Const FIC = "*.xlsx", SRC = "D:\"
    Cells(COL).Value = "Scanning …"
    Application.ScreenUpdating = False
    DirScan FIC, SRC
    Cells(COL).Value = "Scan from " & SRC & FIC & " :"
    Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​