Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursive directory listing
Is it possible to perform a recursive directory listing using VBA ? I can
manage to go down one level but am struggling to code for (more than one) recursion, i.e. all files in all sub directories of a directory. Regards, Jim. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursive directory listing
Hi Jim,
The following was posted by Roger Hammond to this NG earlier this year:: <<<----------------------------------------------------- START 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 <<<----------------------------------------------------- END --- Regards, Norman "Jim" wrote in message ... Is it possible to perform a recursive directory listing using VBA ? I can manage to go down one level but am struggling to code for (more than one) recursion, i.e. all files in all sub directories of a directory. Regards, Jim. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recursive directory listing
Here's some similar code that Bill Manville posted (several) years ago. It
uses standard arrays rather than collections. The former is faster, I believe. The first Sub, ListAllFilesInDirectoryStructure() is a demo/test. Option Base 1 Dim aFiles() As String, iFile As Integer Sub ListAllFilesInDirectoryStructure() Dim Counter As Integer iFile = 0 ListFilesInDirectory "c:\test\" ' change the top level as you wish For Counter = 1 To iFile Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter) Next End Sub Sub ListFilesInDirectory(Directory As String) Dim aDirs() As String, iDir As Integer, stFile As String ' use Dir function to find files and directories in Directory ' look for directories and build a separate array of them ' note that Dir returns files as well as directories ' when vbDirectory specified iDir = 0 stFile = Directory & Dir(Directory & "*.*", vbDirectory) Do While stFile < Directory If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then ' do nothing - GetAttr doesn't like these directories ElseIf GetAttr(stFile) = vbDirectory Then ' add to local array of directories iDir = iDir + 1 ReDim Preserve aDirs(iDir) aDirs(iDir) = stFile Else ' add to global array of files iFile = iFile + 1 ReDim Preserve aFiles(iFile) aFiles(iFile) = stFile End If stFile = Directory & Dir() Loop ' now, for any directories in aDirs call self recursively If iDir 0 Then For iDir = 1 To UBound(aDirs) ListFilesInDirectory aDirs(iDir) & Application.PathSeparator Next iDir End If End Sub On Thu, 16 Sep 2004 11:14:09 +0100, "Norman Jones" wrote: Hi Jim, The following was posted by Roger Hammond to this NG earlier this year:: <<<----------------------------------------------------- START 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 <<<----------------------------------------------------- END --- Regards, Norman "Jim" wrote in message ... Is it possible to perform a recursive directory listing using VBA ? I can manage to go down one level but am struggling to code for (more than one) recursion, i.e. all files in all sub directories of a directory. Regards, Jim. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Directory listing | Excel Discussion (Misc queries) | |||
Listing Directory Contents in Worksheet | Excel Discussion (Misc queries) | |||
Directory listing | Excel Discussion (Misc queries) | |||
Importing a Directory Listing from CD to Excel | Excel Programming | |||
Unix Directory/File Listing | Excel Programming |