CODEWITHSUNDEEP
vbaexcel
Coding Novice
Coding Novice

Reputation: 447

Find and List File Names Augment to Include Subfolders

I have two codes. One will search and name every folder within a directory. The other will list the files and file names within a single folder. I am not proficient enough with VBA to figure this out, so I need StackOverflow!

Here is the File Name Listing program:

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
    Set objFolder = objFSO.GetFolder("\\fc8fsp01\litho_recipe_amat_data")
    i = 1
'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
    'print file name
        Cells(i + 1, 1) = objFile.Name
    'print file path
        Cells(i + 1, 2) = objFile.Path
        i = i + 1
Next objFile
End Sub

Here is the second code that will navigate sub-folders to write folder names: 

Option Explicit

Dim i As Long, j As Long
Dim searchfolders As Variant
Dim FileSystemObject

    Sub ListOfFolders()
        Dim LookInTheFolder As String

        i = 1
        LookInTheFolder = "\D: ' As you know; you should modificate this row.
        Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
        For Each searchfolders In FileSystemObject.GetFolder(LookInTheFolder).SubFolders
            Cells(i, 1) = searchfolders
            i = i + 1
            SearchWithin searchfolders
        Next searchfolders

    End Sub

Sub SearchWithin(searchfolders)
        On Error GoTo exits
    For Each searchfolders In FileSystemObject.GetFolder(searchfolders).SubFolders
        j = UBound(Split(searchfolders, "\"))
        Cells(i, j) = searchfolders
        i = i + 1
        SearchWithin searchfolders
        Next searchfolders
exits:
End Sub

I need a code that will search all sub folders and list all files contained. Please help D:

Upvotes: 0

Views: 257

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Because of speed issues when some of the folders I was accessing were present on a network drive, I wrote a little VBA program that uses the Windows Shell dir command. With the proper arguments, this will return all the files in the base directory; as well as all the subfolders and files and so forth. I have it write the results to a text file, which I then read into Excel for further processing.

Compared with using VBA's DIR or the FSO, this ran about five times faster when the files were on a network drive -- not so noticeable when on the local computer -- but I present it as another approach.

You must set a reference to Windows Script Host Object Model. sDrive and sBasePath are used to set the starting folder name. sFileList is where the results will be written into a text file.

The /S argument Displays files in specified directory and all subdirectories. The /B argument results in omitting heading information and summary

If you run CMD.EXE and look for help on the dir command, you will see an explanation of the other arguments.

Public sDrive As String
Public sBasePath As String
Public Const sFileList As String = "C:\Users\Ron\FileList.txt"
Option Explicit
Sub GetDirTree()
    Dim WSH As WshShell
    Dim lErrCode As Long

Set WSH = New WshShell
lErrCode = WSH.Run("cmd.exe /c dir """ & sDrive & sBasePath & """/B /S >" & sFileList, 0, True)
If lErrCode <> 0 Then
    MsgBox ("Error in GetDirTree: Error Number: " & CStr(lErrCode))
    Stop
End If

End Sub

Upvotes: 1

Constuntine
Constuntine

Reputation: 508

This is the function I use to find all files in a directory.

Public Function RecursiveDir(colFiles As Collection, _
                      ByVal strFolder As String, _
                      strFileSpec As String, _
                      bIncludeSubfolders As Boolean)

 Dim strTemp As String
 Dim colFolders As New Collection
 Dim vFolderName As Variant

'Add files in strFolder matching strFileSpec to colFiles
 strFolder = TrailingSlash(strFolder)
 strTemp = Dir(strFolder & strFileSpec)
 Do While strTemp <> vbNullString
 colFiles.Add strFolder & strTemp
 strTemp = Dir
 Loop

'Fill colFolders with list of subdirectories of strFolder
 If bIncludeSubfolders Then
     strTemp = Dir(strFolder, vbDirectory)
     Do While strTemp <> vbNullString
       If (strTemp <> ".") And (strTemp <> "..") Then
         If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
             colFolders.Add strTemp
         End If
     End If
     strTemp = Dir
 Loop

'Call RecursiveDir for each subfolder in colFolders
 For Each vFolderName In colFolders
     Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
 Next vFolderName
 End If

'Garbage collection
 Set colFolders = Nothing

End Function

This function will populate a collection of every file name in a given directory. And if you want you can set the bIncludeSubfolders to True, and it will recursively search all subfolders within this directory. To use this function, you need the following:

Dim colFiles As New Collection ' The collection of files
Dim Path As String ' The parent Directory you want to search
Dim subFold As Boolean ' Search sub folders, yes or no?
Dim FileExt As String ' File extension type to search for

Then just set FileExt = "*.*" Which will find every file with every file extension. Hopefully this helps a little more.

Upvotes: 1

Related Questions