PDA

View Full Version : Solved: Excel file that lists the file names in the folder



jignesh142
10-31-2012, 03:00 AM
is it possible to make excel file that lists the names of the files in the particular folder??
Also it should get updated when the new file is added or removed...

Kenneth Hobs
10-31-2012, 08:50 AM
Put this in a Module and call in your Open event or just put the code in the Open event. Obviously, you need to change the path.

Sub DirStdOut()
Dim s As String, a() As String
' /b = bare file listing, /s = search subfolders, /c = open command shell, run command and then close shell.
' /a:-d means list files only and not subfolders
s = CreateObject("Wscript.Shell").Exec("cmd /c dir x:\test\*.* /a:-d /b").StdOut.ReadAll
a() = Split(s, vbCrLf)
With Range("A1")
.EntireColumn.Clear
.Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
End With
End Sub

jignesh142
11-01-2012, 04:59 AM
First of all, i want to express many many thanks....
You have made the wonder....
I slightly modified the code with the two string variables to create the excel file path so that you need not to change for each folder, just put the excel file in that folder and get the list....))))

Sub DirStdOut()
Dim s As String, a() As String
Dim filename As String
Dim finalstring As String
filename = ThisWorkbook.Path
finalstring = "cmd /c dir " & filename & "/b"

' /b = bare file listing, /s = search subfolders, /c = open command shell, run command and then close shell.
' /a:-d means list files only and not subfolders
s = CreateObject("Wscript.Shell").Exec(finalstring).StdOut.ReadAll
a() = Split(s, vbCrLf)
With Range("A1")
.EntireColumn.Clear
.Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
End With
End Sub

dantzu
11-01-2012, 05:31 AM
Very useful

jignesh142
02-27-2013, 10:27 PM
Is it possible to create Hyperlink to the listed files in excel, to open it instantly ??

shrivallabha
02-28-2013, 08:48 AM
You might like the discussion and files shared in this link:
http://www.vbaexpress.com/forum/showthread.php?t=42482

jignesh142
03-01-2013, 02:21 AM
Excellent !!!

That was the thing i was looking for.....

Many Many Thanks