Morning Cecil,

Try this

Option Explicit

Dim oFSO As Object
Dim a As Long

Sub FileListing()
Dim i As Long
Dim oFolder As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

a = 2

SelectFiles "C:\MyTest" 'Documents"

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(ByVal sPath)
'-----------------------------------------------------------------------
Dim oFolder As Object
Dim oFldr As Object
Dim oFile As Object
Dim oFiles As Object
Dim sOld As String

Set oFolder = oFSO.GetFolder(sPath)

For Each oFldr In oFolder.subFolders
SelectFiles oFldr.Path
Set oFiles = oFolder.Files
For Each oFile In oFiles
sOld = oFile.Name
Range("A" & a) = sOld
a = a + 1
Next oFile
Next oFldr

End Sub





--

HTH

RP

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
Hi to All,
This macro gets the name of the files in a folder "My Documents" to a
excel
sheet,
can it be improved/altered to get the name of the sub folders in it?
Regards,
Cecil

Sub FileListing()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim a As Long
Dim sOld As String

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:\My Documents")

a = 2
For Each oFile In oFolder.Files
sOld = oFile.Name
Range("A" & a) = sOld
a = a + 1
Next oFile

End Sub