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)


3 thoughts on “Files in a Folder (VBA)”

  1. 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

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.