External File Paths

[Stuart]

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

 

[Bob Phillips]

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]

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

 

[Stuart]

Got that sorted Bob,

Thanks

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

Thanks

Stuart

 

[Guest]

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

 

[Stuart]

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

 

[Bob Phillips]

Option Explicit

Private cnt As Long
Private FSO As Object

Sub Folders()
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean

Set FSO = CreateObject("Scripting.FileSystemObject")

cnt = 0
ActiveWorkbook.Worksheets.Add.Name = "FileList"

sFolder = "C:\MyTest" '<=== change to suit
ReDim arfiles(2, 0)
SelectFiles sFolder

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(sPath As String)
'-----------------------------------------------------------------------
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object

Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
cnt = cnt + 1
Worksheets("FileList").Cells(cnt, "A").Value = oFile.Path
Next oFile

For Each oSubFolder In oFolder.Subfolders
SelectFiles oSubFolder.Path
Next

End Sub


--
HTH

Bob Phillips

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