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.

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Bhupinder Rayat" wrote:

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.

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Bhupinder Rayat" wrote:

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.

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


--
icdoo
------------------------------------------------------------------------
icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342
View this thread: http://www.excelforum.com/showthread...hreadid=485144