Files in a Folder (VBA)

This Excel sheet can be used to get a list of files in a folder into a column of the Excel sheet.


You just need to give the path of the folder in the text box and press enter. This works as simple as a browser.


Code for getting the file and folder names into excel cells is below:

Dim fs, f, f1, ffiles, ffolds
Set fs = CreateObject(“Scripting.FileSystemObject“)

If fs.folderexists(TextBox1.Text) Then

a = MsgBox(“Folder Does not Exist“, , “Give Valid Path“)
GoTo ending

End If

Set f = fs.GetFolder(TextBox1.Text)
Set ffiles = f.Files
Set ffolds = f.subfolders

‘ Adding files to the column
For Each f1 In ffiles

Sheet1.Cells(i, 4).Value = f1.Name
Sheet1.Cells(i, 5).Value = “Go

With Sheet1

.Hyperlinks.Add .Range(Sheet1.Cells(i, 5).Address()), TextBox1.Text + “\” + f1.Name

End With
i = i + 1

j = 6

‘ Adding folders to the column
For Each f1 In ffolds

Sheet1.Cells(j, 7).Value = f1.Name
Sheet1.Cells(j, 8).Value = “Go
With Sheet1

.Hyperlinks.Add .Range(Sheet1.Cells(j, 8).Address()), TextBox1.Text + “\” + f1.Name
End With
j = j + 1


Creating a File System object:

Dim fs

Set fs = CreateObject(“Scripting.FileSystemObject“)

Operations with the file system object:

Opening an existing file:

Dim a, ForAppending=8

a = fs.OpenTextFile(“c:\\testfile.txt“, ForAppending, false);

This will open the file c:\\testfile.txt (Which is already existing) into the object “a” for Appending.

The second parameter to OpenTextFile method can be 1(ForReading), 2(ForWriting) or 8(ForAppending)

blog comments powered by Disqus

There's 3 Comments So Far

  •   Selvakumar
    January 25th, 2012 at 8:31 am

    Another way to do it (with a small basic macro)

    Dim temparray(5000, 1)
    Path = “C:\WINDOWS\”

    MyFile = Dir$(Path, vbDirectory)

    inx = 1
    Do While MyFile <> “”
    temparray(inx, 0) = MyFile
    MyFile = Dir$
    inx = inx + 1

    For i = 1 To inx
    tp = “A” & i
    Range(tp).Value = temparray(i, 0)
    Next i

  •   Selvakumar
    January 25th, 2012 at 8:37 am

    There is also a DOS way of doing it.

    navigate to the directory using command prompt
    ____> cd “c:\windows”

    and issue the below command
    ____> DIR > filelist.txt

  •   Karthik
    January 26th, 2012 at 11:52 am

    Thanks Selva. The DOS way of doing it is the best one.

Share your thoughts, leave a comment!