Listing Directory Contents in Worksheet
 

Is there a way to list the names of files within a directory in a worksheet?
I have a directory that contains 800 files and I would like to list them
within a Excel worksheet. I have tried selecting all, copy and paste but
nothing happens.

Any help is greatly appreciated.

Thanks,
Paul

Paul,

One way

Option Explicit

'-------------------------------------------------------------------------
Public Sub Hyperlinked_Folder_List()
'-------------------------------------------------------------------------
Const HFL_FOLDER As String = "C:\myTest"
Dim oFSO As Object
Dim cList As Long
Dim aryfiles
Dim iLevel As Long
Dim i As Long
Dim sFolder As String
Dim oSheet As Worksheet

Set oFSO = CreateObject("Scripting.FileSystemObject")

aryfiles = Array()
cList = -1: iLevel = 1

ReDim aryfiles(1, 0)
SelectFiles aryfiles, oFSO, cList, iLevel, HFL_FOLDER
On Error Resume Next
Set oSheet = Worksheets("Files")
On Error GoTo 0
If Not oSheet Is Nothing Then
oSheet.Cells.ClearContents
Else
Worksheets.Add.Name = "Files"
End If

With ActiveSheet
For i = LBound(aryfiles, 2) To UBound(aryfiles, 2)
.Hyperlinks.Add Anchor:=.Cells(i + 1, aryfiles(1, i)), _
Address:=aryfiles(0, i), _
TextToDisplay:=aryfiles(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With

Set oFSO = Nothing

End Sub


'-----------------------------**-----------------------------*-*------------
Private Sub SelectFiles(ByRef aryfiles, _
ByVal FSO As Object, _
ByRef pzList As Long, _
ByRef pzLevel As Long, _
ByVal pzPath As String)
'-----------------------------**-----------------------------*-*------------
Dim oSubfolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object

Set oFolder = FSO.GetFolder(pzPath)

Set oFiles = oFolder.Files
For Each oFile In oFiles
pzList = pzList + 1
ReDim Preserve aryfiles(1, pzList)
aryfiles(0, pzList) = oFolder.Path & "\" & oFile.Name
aryfiles(1, pzList) = pzLevel
Next oFile

pzLevel = pzLevel + 1
For Each oSubfolder In oFolder.Subfolders
SelectFiles aryfiles, FSO, pzList, pzLevel, oSubfolder.Path
Next oSubfolder
pzLevel = pzLevel - 1

Set oFiles = Nothing
Set oFolder = Nothing

End Sub
'-------------------------------------------------------------------------





--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pablo" wrote in message
...
Is there a way to list the names of files within a directory in a
worksheet?
I have a directory that contains 800 files and I would like to list them
within a Excel worksheet. I have tried selecting all, copy and paste but
nothing happens.

Any help is greatly appreciated.

Thanks,
Paul

Paul,

You could run a macro, like the one below. This will prompt you to select a file within the folder
of interest, and then list the file, date, and file size for all files found in the folder.

HTH,
Bernie
MS Excel MVP

Sub ListFolderContents()
Dim i As Integer
Dim myName As String
With Application.FileSearch
.NewSearch
myName = Application.GetOpenFilename( _
Title:="Pick a file in your folder")
.LookIn = Left(myName, InStrRev(myName, "\"))
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Range("A:C").EntireColumn.AutoFit
Else
MsgBox "There were no files found."
End If
End With
End Sub



"Pablo" wrote in message
...
Is there a way to list the names of files within a directory in a worksheet?
I have a directory that contains 800 files and I would like to list them
within a Excel worksheet. I have tried selecting all, copy and paste but
nothing happens.

Any help is greatly appreciated.

Thanks,
Paul