Excel Fox
10-24-2011, 10:26 PM
This macro creates a list of all files within a folder.
Excel 2007+ version
Sub ListAllFile()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim wks As Worksheet
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wks = Worksheets.Add
'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder("C:\")
wks.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
'Loop through the Files collection
For Each objFile In objFolder.Files
wks.Cells(wks.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
Next
'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub
Excel 2003- version
Sub ListAllFiles()
'Not for Excel 2007
Dim objFileSearch As FileSearch, wks As Worksheet, lngLoop As Long
Set objFileSearch = Application.FileSearch
With objFileSearch
.SearchSubFolders = False ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = "C:\" 'modify this to where you want to serach
If .Execute > 0 Then
Set wks = Worksheets.Add
For lngLoop = 1 To .FoundFiles.Count
wks.Cells(lngLoop, 1) = Mid$(.FoundFiles(lngLoop), InStrRev(.FoundFiles(lngLoop), "\") + 1)
Next
Else
MsgBox "No files found"
End If
End With
Set objFileSearch = Nothing
Set wks = Nothing
lngLoop = Empty
End Sub
Excel 2007+ version
Sub ListAllFile()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim wks As Worksheet
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wks = Worksheets.Add
'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder("C:\")
wks.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
'Loop through the Files collection
For Each objFile In objFolder.Files
wks.Cells(wks.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
Next
'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub
Excel 2003- version
Sub ListAllFiles()
'Not for Excel 2007
Dim objFileSearch As FileSearch, wks As Worksheet, lngLoop As Long
Set objFileSearch = Application.FileSearch
With objFileSearch
.SearchSubFolders = False ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = "C:\" 'modify this to where you want to serach
If .Execute > 0 Then
Set wks = Worksheets.Add
For lngLoop = 1 To .FoundFiles.Count
wks.Cells(lngLoop, 1) = Mid$(.FoundFiles(lngLoop), InStrRev(.FoundFiles(lngLoop), "\") + 1)
Next
Else
MsgBox "No files found"
End If
End With
Set objFileSearch = Nothing
Set wks = Nothing
lngLoop = Empty
End Sub