Display Contents of Folder

[Guest]

I'm in need of VBA code that will list in an Excel sheet the filenames of all
documents of a specific folder. Any help on this is much appreciated.

Thanks,
Chad

 

[Crowbar via OfficeKB.com]

This will do that, it stores them in an array then writes it to sheet1

Function GetFileNames()

Dim MyName, MyPath
Dim FNames()

'Find how many file exist

ReDim FNames(0)

MyPath = "C:\" 'You need to change this to your requirement
MyName = Dir(MyPath, vbDirectory)
Do While MyName <> ""

If MyName <> "." And MyName <> ".." Then

On Error Resume Next
If (GetAttr(MyPath & MyName) And vbDirectory) <> vbDirectory Then
ReDim Preserve FNames(UBound(FNames) + 1)
FNames(UBound(FNames)) = MyPath & MyName
End If

End If

MyName = Dir

Loop

For x = 1 To UBound(FNames)
Sheet1.Cells(x, 1) = FNames(x)
Next x

End Function

 

[Zone]

This will put a list of files in column G. Change MYPATH to the path for
the folder you want to look at, ending with a backslash \ Hope this
helps, James

Const MYPATH = "c:\MyFiles\"

Sub ListFiles()
Dim PutRow As Long, fName As String
PutRow = 1
Columns("g").Clear
fName = Dir(MYPATH & "*.*")
Cells(PutRow, "g") = fName
PutRow = PutRow + 1
Do
fName = Dir
Cells(PutRow, "g") = fName
PutRow = PutRow + 1
Loop Until fName = ""
End Sub

 

[Guest]

I use this routine

Sub Searchfolder()

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\User\My Documents" 'Your path here
.Filename = "*"
.Searchsubfolders = False
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."

For I = 1 To .FoundFiles.Count
'Truncate to File Name
flstring = .FoundFiles(I)
namelen = InStrRev(flstring, "\")
flstring = Right(flstring, Len(flstring) - namelen)
'Output to sheet
ActiveCell = flstring
ActiveCell.Offset(1, 0).Select
Next I
Else
MsgBox "There were no files found."
End If
End With

End Sub

 

[Guest]

I think this will work perfectly. Thank you so much!

-Chad

 

[Chip Pearson]

You can use my DirTree add-in that will list the subfolders and files of a
specified folder, with many display options. See
www.cpearson.com/Excel/FolderTree.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[cubbybear3]

Is it possible to access the indvidual file attributes that I can view
in different columns within Windows 'Exploder' (ie: Attributes, Owner,
Author, Title, Artist, etc)? And if so, are there any VBA examples
out there I could look at? Thanks. -pb

 

[Gord Dibben]

I suggest downloading Tushar's Directory Listing add-in from

http://www.tushar-mehta.com/ scroll down to Add-ins>Directory
Listing.

Download the ZIP file and un-zip to your Office\Library folder.


Gord Dibben MS Excel MVP

 

[Guest]

Is it possible to get a list of the entire folder contents, including other
folders that may exist? This code works perfectly to list all files outside
of folders within the desired destination.

Thanks!

 

[Zone]

Change it like this to get files plus subdirectories.

fName = Dir(MYPATH, vbDirectory)

This doesn't show the files within the subdirectories, but you can do add
the subdirectories to the path one at a time, then do Dir to get those
files. HTH, James