Graham182

New Member
Joined
Jan 31, 2010
Messages
31
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Graham182

New Member
Joined
Jan 31, 2010
Messages
31
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 :rolleyes:
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,285
Change the inner loop to:
Code:
            For I = 1 To .FoundFiles.Count
                Cells(I, 1).Value = Right(.FoundFiles(I), InStr(StrReverse(.FoundFiles(I)), "\") - 1)        
            Next I
Bye
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,225
Messages
5,768,912
Members
425,502
Latest member
sunstream

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top