Hi Tom,

I would like excel to list the files and folder is C Drive, then list
the files and folders within the subfolders found right through until
every file and folder on the drive is on excel.

Can this be done?

Many thanks for your previous advice.

Stuart


Tom Ogilvy wrote:
You mean like this
Dim FSO As Object

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
this.Worksheets.Add.Name = "FileList"
sFolder = "C:\Data1" '<=== change to suit
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Worksheets("FileList").Cells(cnt, "A").Value = file.Path
cnt = cnt + 1
End If
Next file
For Each fldr In Folder.SubFolders
Worksheets("FileList").Cells(cnt, "A").Value = fldr.Path
cnt = cnt + 1
Next
End If ' sFolder < ""

End Sub

or are you looking to recurse throught the whole subdirectory structure
listing files?

http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic

--
Regards,
Tom Ogilvy


"Stuart" wrote:

Got that sorted Bob,

Thanks

anything I can do to get that code to bring in the directories too?

Thanks

Stuart

Stuart wrote:
Bob,

Thank you for your repsonse.....greatly appreciated.

It keeps debugging at the part of the code

this.Worksheets.Add.Name = "FileList"

Any ideas?

I attempted the folder C:\temp and it didnt work.

Thanks Bob,

Stuart

Bob Phillips wrote:
You might be able to do something with this code

Dim FSO As Object

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
this.Worksheets.Add.Name = "FileList"
sFolder = "C:\MyTest" '<=== change to suit
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Worksheets("FileList").Cells(cnt, "A").Value = file.Path
cnt = cnt + 1
End If
Next file

End If ' sFolder < ""

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stuart" wrote in message
oups.com...
Hi there,

Since my previous tests failed - the file paths werent entered into the
spreadsheet correctly!! I wondered if it were possible to have excel
extract a list of all the files in a drive and put each file found's
full path location in a row on the spreadsheet.

for example - I have my C Drive. I would expect excel to find all
Windows files and then list their full path down column A,

Hope this explains what I'm looking for,

Thanks in advance,

Kind Regards,

Stuart