Directory Contents
 

Has anyone got any code to return a list of the contents of a directory and its sub folders

TIA

Directory Contents
 

Jo,

The actual code will depend on what specifically you want to do
with the list. Try the following code to create a tree-like list
in an Excel worksheet.

Sub Start()

Const START_FOLDER_NAME = "H:\ExcelProjects" '<<< CHANGE
Dim FSO As Object
Dim Rng As Range
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Rng = Range("A1")
Rng.Value = START_FOLDER_NAME
Set Rng = Rng(2, 2)
ProcessOneFolder FSO.GetFolder(START_FOLDER_NAME), Rng

End Sub


Sub ProcessOneFolder(Fldr As Object, Rng As Range)

Dim OneFile As Object
Dim OneFolder As Object
For Each OneFile In Fldr.Files
Rng.Value = OneFile.Path
Set Rng = Rng(2, 1)
Next OneFile
For Each OneFolder In Fldr.SubFolders
Rng.Value = OneFolder.Path
Set Rng = Rng(2, 2)
ProcessOneFolder OneFolder, Rng
Set Rng = Rng(1, 0)
Next OneFolder

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jo" wrote in message
...
Has anyone got any code to return a list of the contents of a
directory and its sub folders?

TIA

Directory Contents
 

Jo

Chip has provided code.

In addition, Tushar Mehta has a downloadable add-in for this function.

This allows filtering and sorting once you have the data in Excel.

http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing.

Download the ZIP file and un-zip to your Office\Library folder.

Gord Dibben Excel MVP

On Tue, 20 Apr 2004 08:16:05 -0700, "Jo"
wrote:

Has anyone got any code to return a list of the contents of a directory and its sub folders?

TIA

Directory Contents
 

Jo,

this will spit out the contents of a folder and sub folders for either a
given file type or all types to a new sheet.

Option Explicit

Sub FullDir()
ActiveWorkbook.Sheets.Add
GetFiles "c:\my documents\my excel files\xspandxl\", ".xls"
End Sub

Sub GetFiles(strRootDir As String, Optional strType As String)
Dim strDirName As String
Dim bTypeMatch As Boolean
Dim colDirs As Collection
Dim lDirCounter As Long
Dim lIndex As Long

Set colDirs = New Collection
colDirs.Add strRootDir
lDirCounter = 1
lIndex = 1

'check for sub directories and make a recursive call to the lowest level
dirs first

Do While lDirCounter <= colDirs.Count
strRootDir = colDirs(lDirCounter)
strDirName = Dir(strRootDir, vbDirectory + vbNormal)
Do While strDirName < ""
If strDirName < "." And strDirName < ".." Then
If (GetAttr(strRootDir & strDirName) And vbDirectory) =
vbDirectory Then
'add to the directories collection so that this will be done
later
colDirs.Add strRootDir & strDirName & "\"
Else
'we found a normal file
bTypeMatch = False
If strType = "*.*" Then
bTypeMatch = True
ElseIf UCase(Right(strDirName, Len(strType))) =
UCase(strType) Then
bTypeMatch = True
End If
If bTypeMatch = True Then
'we found a valid file
Cells(lIndex, 1) = strRootDir & strDirName
lIndex = lIndex + 1
End If
End If
End If
strDirName = Dir
Loop
lDirCounter = lDirCounter + 1
Loop
End Sub

Robin Hammond
www.enhanceddatasystems.com

"Jo" wrote in message
...
Has anyone got any code to return a list of the contents of a directory
and its sub folders?

TIA