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 …),
DirListfunction returns a string array of files or folders from a starting point, a disk root or a specific folder.
If nothing matches,
DirListreturns an empty array …
DirList("*.xlsx", "D:\Data\")returns all workbooks from the folder D:\Data\ like D:\Data\Accounts2019.xlsx.
DirList("D:\Data\*.xlsx")returns only the workbooks names without the path like for example Accounts2019.xlsx …
DirListdoes not scan sub folders as
DirVBA function can't …
So if you need to scan a folder and its sub folders, you must use a particular procedure like
DirScanin my demonstration
that must be called from your main procedure,
Demoon my side below …
DirScanstarts from the starting point called by the main procedure and,
once it writes the
DirListstring array to the worksheet (only with a matching result),
it scans its sub folders and for each sub folder found,
DirScancalls itself for this sub folder, that's Recursivity ‼
Demoscans 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
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 Else 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$ Loop 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:\" Me.UsedRange.Clear Cells(COL).Value = "Scanning …" Application.ScreenUpdating = False DirScan FIC, SRC Cells(COL).Value = "Scan from " & SRC & FIC & " :" Columns(COL).AutoFit Application.ScreenUpdating = True End Sub
Do you like it ? So thanks to click on bottom right Like !