Microsoft Office Tutorials and References
In Depth Information
Creating a List of Files That Reside in a Directory
Option Explicit¶
' * * * * *¶
Sub CreateFileList()¶
'Variable declarations¶
Dim i As Long¶
'Folder in which to search¶
Dim Folder As String¶
'Search in subfolders¶
Dim LookInSubFolders As Boolean¶
Dim File As Object¶
Dim FSO As Object¶
'Store old calculation¶
Dim OldCalculation As Long¶
'Change the following variables¶
'Hard code Folder value¶
'Folder = "C:\"¶
'Folder = "C:\My Documents\My Music"¶
'User inputs Folder value¶
Folder = Range("B1").Value¶
'Hard code LookInSubFolders value¶
'LookInSubFolders = False¶
'User inputs LookInSubFolders value¶
LookInSubFolders = Range("B2").Value¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'Store current calculation method in variable¶
OldCalculation = Application.Calculation¶
'Set calculation to Manual¶
Application.Calculation = xlCalculationManual¶
'New FileSystemObject¶
Set FSO = CreateObject("Scripting.FileSystemObject")¶
With Application.FileSearch¶
.LookIn = Folder¶
.SearchSubFolders = LookInSubFolders¶
.FileType = msoFileTypeAllFiles¶
'Does enough space exist for all the files?¶
If i - 1 > Rows.Count Then¶
MsgBox Prompt:="The search returned more results than can " & _¶
"be displayed on a worksheet", _¶
'Exit nicely¶
GoTo exiting¶
End If¶
Search JabSto ::

Custom Search