Outputting filenames in excel from a particular

[Bhupinder Rayat]

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]

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.

[Bhupinder Rayat]

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.

[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