Category: Excel

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

Preview:

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

Download:

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
Else

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

Next
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

Next

---

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)