Get Files Macro Need more

[Graham182]

Hi i've got this Marco/Function

Sub get_files()
Extension = "*.CSV"
Folder = "C:\Documents and Settings\User\My Documents
First = True
RowCount = 1
Do
If First = True Then
FName = Dir(Folder & "\" & Extension)
First = False
Else
FName = Dir()
End If
If FName <> "" Then
Range("A" & RowCount) = FName
RowCount = RowCount + 1
End If
Loop While FName <> ""
End Sub

It will list everything in the My documents files
But its not searching the Subfolders and listing the name in the Directory
I know a few things about VBA but don't know what to do to ajust it to search for Subfolders can anyone help

Thanks
Graham

 

[Anthony47]

If you use excel version 2003 or earlier, have a look to this thread: http://www.mrexcel.com/forum/showthread.php?t=24634
You will change .SearchSubFolders = True

Bye.

 

[Graham182]

This is only returning one of the many I have in the folder?
and its also showing the path as well.
I only want the file name without the path.
I only know basic macro and VBA
hope you can help

 

[Anthony47]

Change the inner loop to:

            For I = 1 To .FoundFiles.Count
                Cells(I, 1).Value = Right(.FoundFiles(I), InStr(StrReverse(.FoundFiles(I)), "\") - 1)        
            Next I

Bye

 

[Graham182]

Perfect
Thanks very much