VBA: List of all files contained within a Directory

September 13th, 2008 | Categories: VBA | Tags: , ,

The following code is a subroutine that will get the names of all the files that are present in a directory path:

Sub List_All_The_Files_Within_Path()

Dim Row_No As Integer
Dim No_Of_Files As Integer
Dim kk25 As Integer
Dim File_Path As String

File_Path = "C:\My Documents"

Row_No = 36

'Lists all the files in the current directory

With Application.FileSearch
.NewSearch
.LookIn = File_Path
.Filename = "*.*"
.SearchSubFolders = False
.Execute

    No_Of_Files = .FoundFiles.Count

    For kk25 = 1 buy viagra online purchase To No_Of_Files
       Worksheets("Sheet1").Cells(kk25 + 5, 15).Value = .FoundFiles(kk25)

    Next kk25

End With

End Sub

It will write the filenames to Sheet1 in column O – starting at row 36.

Download the Excel file here


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. September 13th, 2008 at 18:32
    Reply | Quote | #1

    Keep in mind this will only work pre-Excel 2007. Check out the Scripting.FileSystemObject for a way you can do this in all Excel versions.

    -JP

  2. Randy Harmelink
    September 14th, 2008 at 00:37
    Reply | Quote | #2

    Just to note that Application.FileSearch no longer exists in EXCEL 2007.

  3. September 14th, 2008 at 00:39
    Reply | Quote | #3

    Keep in mind that the FileSearch object is no longer available in Office 2007. So this code works only with earlier versions.

    You can use the old Dir command or FileSystemObject.

  4. February 10th, 2011 at 19:09
    Reply | Quote | #4

    Thanks for pointing out Scripting.FileSystemObject guys.

  5. defcon
    June 30th, 2011 at 12:07
    Reply | Quote | #5

    Dim Row_No As Integer
    Dim No_Of_Files As Integer
    Dim kk25 As Integer
    Dim File_Path As String
    Dim sPingcmd As String
    Dim taskID

    File_Path = Sheets(“Sheet1″).Range(“A1″).Value

    Row_No = 36

    Range(“A3:A500″).Clear

    ‘Lists all the files in the current directory

    With Application.FileSearch
    .NewSearch
    .LookIn = File_Path
    .Filename = “*.*”
    .SearchSubFolders = True
    .Execute

    No_Of_Files = .FoundFiles.Count

    For kk25 = 1 To No_Of_Files
    If InStr(1, .FoundFiles(kk25), “[www.songs.pk]“) > 0 Then
    Worksheets(“Sheet1″).Cells(kk25 + 2, 1).Value = .FoundFiles(kk25)
    End If
    Next kk25

    End With

    For kk25 = 1 To No_Of_Files
    sPingcmd = Worksheets(“Sheet1″).Cells(kk25 + 2, 3).Value
    If sPingcmd “” Then
    Call Shell(“cmd /K ” & sPingcmd, vbHide)

    If kk25 > 9 And kk25 Mod 10 = 0 Then
    Call Shell(“cmd /K taskkill /IM cmd.exe “, vbHide)
    End If

    End If
    Next kk25

    Call Shell(“cmd /K taskkill /IM cmd.exe “, vbHide)

    =IF(ISERROR(FIND(“[www.songs.pk]“,A3,1)),”",SUBSTITUTE(A3,LEFT(A3,FIND(“[www.songs.pk]“,A3,1) + 13),”"))

    =IF(A3=”",”",CONCATENATE(“ren “,”"”" & A3 & “”"”, ” “, “”"” & B3 & “”"”))