Outputting filenames in excel from a particular

[Guest]

Hi all,

I have a folder that contains 200 csv files (i.e. c:\folder). Is there any
way i can get vba to output the filenames within c:\folder in excel

i.e. in cell A1 - file.csv
A2 - file2.csv
A3 - file3.csv
etc.................

Kind Regards,


Bhupinder.

 

[Guest]

Bhupinder Rayat:

Try:

With Application.FileSearch
rpath = "C:\folder"
.NewSearch
.Filename = "*.csv"
.LookIn = rpath
.SearchSubFolders = True
.Execute msoSortByFileName, msoSortOrderAscending
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = Dir(.FoundFiles.Item(i))
Next i
End If
End With

 

[Guest]

chijanzen,

works like a dream! i can enhance that now to give me exactly what i want.

Thanks ever so much, have a good day.


Bhupinder

 

[icdoo]

Wow this works great, is there away to make it return the file name
without the file extension.
Example; "filename.gif" would just be "filename"
I am putting the data right into an array so even a method to get rid
of ".GIF" after would help.

If you look at the modified sub below I would also like to find away to
DIM Scanmaps to the amount of files in the directory, I currently put in
36 to make it work. However if you put in a variable it doesnt work.

Sub filenames()
With Application.FileSearch
rpath = "C:\Program Files\Code2004\MD1"
..NewSearch
..Filename = "*.GIF"
..LookIn = rpath
..SearchSubFolders = True
..Execute msoSortByFileName, msoSortOrderAscending
If .Execute > 0 Then

Dim Scanmaps(1 To 36) As String

For i = 1 To .FoundFiles.Count
Scanmaps(i) = Dir(.FoundFiles.Item(i))
Next i
End If
End With
End Sub