Paste the file names in a excel sheet

[sethu29]

Hi

I have plenty of files in a folder path E:\SETHU-OFFICIAL. The files are around 500. So now i want to paste the file names in a excel sheet, which are in E:\SETHU-OFFICIAL folder. i also saved a excel sheet in E:\ called files.xls.

Please help me.

[mohanvijay]

Try this

[vba]

Dim i As Long
Dim Fi_Name As String
Fi_Name = Dir("E:\SETHU-OFFICIAL\*.xls")
i = 1
Do Until Fi_Name = ""
Cells(i, 1).Value = Fi_Name
i = i + 1
Fi_Name = Dir
Loop
[/vba]

[sethu29]

Where should i paste this and how to run this.

[Tinbendr]

Try our Knowledge base entry.

[sethu29]

Thanks

[mperrah]

how do you make this list files from sub folders too?
and how do you limit to a specific filer type?
I have swf and pdf, but only want pdf listed
[VBA]

Sub GetFileNames()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = "C:\Users\Mark\Documents\Right To Know_edit\msds" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub
[/VBA]

[Tinbendr]

Search is your friend.

Search directory and sub folders for Excel files

[mperrah]

Thank you David:
I ran this to find all .pdf files 2 folders levels down
and even tried 1 level down, it errors on
[VBA]With Application.FileSearch[/VBA]
microsoft Visual Basic message:
run time error '445'
object does not support this action

not sure if its because im looking for .pdf not an office file?
or more than 1 folder deep?

[Aussiebear]

What version of Excel are you running?

[Tinbendr]

it errors on

My apologies, I keep forgeting M$ took FileSearch out starting in 2007.

Search for 'directory list excel -filesearch'

[mperrah]

running 2010 on win 7 x64

[Aussiebear]

Have a look here
http://support.microsoft.com/kb/185601

[sethu29]

Yes attached file is working. But i want file names of the sub folder name and file name also.


In E drive ive one folder named SETHU-OFFICIAL, There are 15 files and one more folder with named ramu. Inside the ramu folder Ive 60 files

if im extracting SETHU-OFFICIAL folder.it is showing 15 files itself.
It has extract the ramu folder placed inside SETHU-OFFICIAl also, The ramu folder file names also required. Any body can help me?

[Aussiebear]

Have a look here http://www.mrexcel.com/forum/showthr...?p=64850#66157 and in particular at posts 6, 8 & 9.