Hi,

I am creating macro that will select excel file from my
folder (directory), How can I display the contents of that
directory or put in a text file.

Appreciate your help. thanks

Eric,

Here is a bit of code goes through all folders ands files from a given
folder and
lists them on a worksheet as hyperlinks, you should be able to adapt to
what you want, both for the former and the latter requirement. There is no
control that I know of that does it.


Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long

Sub Folders()
Dim i As Long
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = -1
level = 1

sFolder = "C:\myTest"
ReDim arfiles(1, 0)
If sFolder < "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

If sPath = "" Then
Set FSO = CreateObject("SCripting.FileSystemObject")
sPath = "c:\myTest"
End If

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.Path & "\" & file.Name
arfiles(1, cnt) = level
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path
Next

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Eric" wrote in message
...
Hi,

I am creating macro that will select excel file from my
folder (directory), How can I display the contents of that
directory or put in a text file.

Appreciate your help. thanks