Search for files in root folder and subfolders

B

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39
The following code only returns the files in the subfolders and does not include those in the root folder. Why is that so? Thanks.

Code: 
    With Application.FileSearch
        .SearchSubFolders = True
        .NewSearch
        .Filename = ".txt"
        .LookIn = userrootfolder
        .FileType = msoFileTypeAllFiles
        .Execute

         '<.FoundFiles(1 to .FoundFiles.Count) processing>

    End With

Upon further analysis I see that all the (many) files are there but are not returned in folder (and then alphabetical) order. Is it possible to make that happen? Thanks.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Here is "one I prepared earlier" using the FileSystemObject.
Things are a little complicated because we need to deal with folders within folders - so we use VBA ability for a subroutine to call itself. You will need to adapt the 'ShowFileList' routine to show only .txt files. eg
If Right(f1.Name,4) =".txt" Then .........
Code: 
'=====================================================================================
'- USE FileSystemObject TO LIST FOLDERS & FILES
'- ... to worksheet called "List-Folders-Files"
'- Brian Baulsom October 2004
'=====================================================================================
'- Need to set 'BaseFolder' variable
Const BaseFolder As String = "F:\XL_MACROS\"
'==============================================
Dim MySheet As Worksheet
Dim ToRow As Long
'----------------------------------------------
Dim FSO As Object           ' FileSystemObject
Dim FolderName As String
Dim FolderPath As String
Dim FolderSpec As String
Dim FileSpec As String

'=====================================================================================
'- MAIN ROUTINE : SET START FOLDER & GET ITS FILES - THEN GET SUB FOLDERS
'=====================================================================================
Sub LIST_FOLDERS_FILES()
    '- initialise variables
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set MySheet = Worksheets("List-Folders-Files")
    Application.Calculation = xlCalculationManual
    ChDrive BaseFolder
    ChDir BaseFolder
    '- set up worksheet
    MySheet.Columns("A:D").ClearContents
    MySheet.Range("A1:D1").Value = Array("FOLDER", "FILE NAME", "CREATED", "SIZE")
    '--------------------------------------------------------------------------------
    '- CALL FILE SUBROUTINE FOR BASE FOLDER
    MySheet.Cells(2, 1).Value = BaseFolder
    Application.StatusBar = BaseFolder
    ToRow = 2
    ShowFileList (BaseFolder)
    '-------------------------------------------------------------------------------
    '- CALL FOLDER SUBROUTINE (WHICH CALLS THE FILE ROUTINE)
    ShowFolderList (BaseFolder)
    '-------------------------------------------------------------------------------
    '- FINISH
    MsgBox ("Done")
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
End Sub
'========  END OF MAIN ROUTINE =======================================================

'=====================================================================================
'- SUBROUTINE : GET SUBFOLDERS OF SPECIFIED FOLDER
'=====================================================================================
Private Sub ShowFolderList(FolderSpec)
    Dim f, f1, fc, s
    Set f = FSO.GetFolder(FolderSpec)
    Set fc = f.subfolders
    '---------------------------------------------------------------------------------
    '- CHECK SUBFOLDER COUNT
    If fc.Count = 0 Then
        Exit Sub
    Else
        '- LOOP FOLDERS
        For Each f1 In fc
            FolderName = f1.Path
            Application.StatusBar = FolderName
            MySheet.Cells(ToRow, 1).Value = FolderName
            ShowFileList (FolderName)
            '-----------------------------------------------------------------------
            '- CALL SELF TO GET ANY SUBFOLDERS IN THIS SUBFOLDER
            ShowFolderList (FolderName)
            '------------------------------------------------------------------------
        Next
    End If
    '--------------------------------------------------------------------------------
End Sub
'-
'=====================================================================================
'- SUBROUTINE : TO LIST FILES IN FOLDER
'=====================================================================================
Private Sub ShowFileList(FileSpec)
    Dim f, f1, fc, Spec
    Set f = FSO.GetFolder(FileSpec)
    Set fc = f.Files
    '--------------------------------------------------------------------------------
    '- CHECK FILE COUNT
    If fc.Count = 0 Then
        ToRow = ToRow + 1
        Exit Sub
    Else
        '- LOOP FILES
        For Each f1 In fc
            Set Spec = FSO.GetFile(f1)  ' individual file info
            MySheet.Cells(ToRow, 2).Value = f1.Name
            MySheet.Cells(ToRow, 3).Value = Spec.datecreated
            MySheet.Cells(ToRow, 4).Value = Spec.Size   'bytes
            ToRow = ToRow + 1
        Next
    End If
    '---------------------------------------------------------------------------------
End Sub
'=== END OF PROJECT ==================================================================
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
3K
Candyman8019
C
D
  • Question Question
With Application.FileSearch how do I replace
Replies
8
Views
828
ddavis5891
D
E
  • Question Question
Excel vba: create folder, move Word files, Search Replace
Replies
0
Views
945
enigmaes
E
I
  • Solved
Loop through a folder with multiple csv files, delete empty column and save as text files
Replies
7
Views
1K
smozgur
smozgur
B
  • Question Question
Use partial name to Search and locate newest file within multi layered subfolders
Replies
1
Views
1K
Buffalo13
B

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top