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