» List files in a folder with Office 97 or later using VBA in Microsoft Excel
VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
CATEGORY - Files, Workbook, and Worksheets in VBA
VERSION - All Microsoft Excel Versions
Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.FileName = FileFilter
.SearchSubFolders = IncludeSubFolder
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
FileList(FileCount) = .FoundFiles(FileCount)
Next FileCount
.FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
CreateFileList = FileList
Erase FileList
End Function
Sub TestCreateFileList()
Dim FileNamesList As Variant, i As Integer
'ChDir "C:\My Documents"
' activate the desired startfolder for the filesearch
FileNamesList = CreateFileList("*.*", False)
' performs the filesearch, includes any subfolders
' present the result
Range("A:A").ClearContents
For i = 1 To UBound(FileNamesList)
Cells(i + 1, 1).Formula = FileNamesList(i)
Next i
End Sub
Book Store:
Recommended Books:
- Getting to Yes: Negotiating Agreement Without Giving In
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- East of Eden (Oprah's Book Club)
- Business Analysis with Microsoft Excel (2nd Edition)
- Good to Great: Why Some Companies Make the Leap... and Others Don't
No comments have been submitted.

