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