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
- on a mechanical hard drive, not a SSD -
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 drive,
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 demonstrationthat 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 …
Code:
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 !