adjusting code search files into folder and sub folder

[maghari]

hi, everyone
actually i search too much to get code doing search thorough specific cell to get the file throughout loop folder or sub folder but i failed every code it shows all of file s in the sheets so i would code when i write the file name in cell e1 it show in a1 the name file and the b1 is the type and c1 the size and d1 is directory and when i search a new file name replace the old file name in a1,b1,c1,d1 with if is possible show massege by vbline gives the data a1,b,c1d1
my directory in device "C:\Users\leopar\Desktop\search" and the folder search contains many folder (jan,feb,mar...etc)

[BIFanatic]

Try this

Option Explicit


Dim FSO         As Object 'Scripting.FileSystemObject
Dim RowNum      As Long
Dim ws          As Worksheet


Sub GetFileNames()
    
    Dim FD                  As FileDialog
    Dim FolderSelected      As Boolean
    Dim TryAgain            As VbMsgBoxResult
        
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)


SelectFolderAgain:
    FD.Title = "Select the source Folder"
    FolderSelected = FD.Show
    
    If Not FolderSelected Then
        TryAgain = MsgBox("You didn't select a folder, do you want to try again?", vbYesNo)
        If TryAgain = vbYes Then
            GoTo SelectFolderAgain
        Else
            MsgBox "You do not wish to select a folder, the macro will now end!", vbInformation
            Exit Sub
        End If
    End If


    Set FSO = CreateObject("Scripting.FileSystemObject")
    RowNum = 2
    Set ws = Sheet1 'change this to the codename of your sheet


    Call LoopOverFolders(FD.SelectedItems(1))
    
    Set FSO = Nothing
    
End Sub


Private Sub LoopOverFolders(TargetFolderPath As String)


    Dim Fol                 As Object 'Scripting.Folder
    Dim Fil                 As Object 'Scripting.File
    Dim SubFol              As Object 'Scripting.Folder


    Set Fol = FSO.GetFolder(TargetFolderPath)


    For Each Fil In Fol.Files
        
        ws.Cells(RowNum, 1).Value = Fil.Name
        ws.Cells(RowNum, 2).Value = Fil.Path
        
        RowNum = RowNum + 1
        
    Next Fil
    
    For Each SubFol In Fol.SubFolders
        Call LoopOverFolders(SubFol.Path)
    Next SubFol
    
End Sub

[maghari]

thanks but it just open the folder from desktop without show any files xls,pdf,jpeg...etc and is possible bring file without open browser by use cells in e1 as i explained

[BIFanatic]

No offense, but it is difficult to understand your original post, so I thought you wanted to loop over files and folders and list the file details, can you please be more specific and clear. This a very easy task but I need to understand clearly what you want to do.

[maghari]

i'm surprised my explanation is not clear i do my best to anybody want to help me i make sure to understand me i have ever told all of codes in the internet show the all files in folder and sub folder this is not what i want what i would when i write the file name in e1 so get me the file name in a1,b1,c1,d1 jut what i write not all of the files and when i write a new file name in e1 should clear the old file name it replace it if you see my file you understand me

[BIFanatic]

Try this.

[maghari]

thanks for adjusting but the code still missing something when it brings only file in the main folder doesn't bring file in sub folder for instance the main folder is search and contains sub folder jan , feb theses contain files it doesn't brings any file in theses sub folder except one case i have to write directory sub folder every time i search specific file like this "C:\Users\leopar\Desktop\search\jan"
or
"C:\Users\leopar\Desktop\search\ feb" and so on it supposes brings any files whether in main file or sub folder

[BIFanatic]

Change Call LoopOverFolders(MyFolderPath) to Call LoopOverFolders(MyFolderPath, True)

[maghari]

yes it works i noted about the size file it does not show kb or mb it just number like this 147167 but the real size is 144 kb so if i read 147167 it contains gb is it normal ? or there is way to do that

[BIFanatic]

Updated it to return the file size, used logic from this post(Click me) on Stack Overflow, thanks to "Creator"!

[maghari]

well done i would final request i'm sorry about it i want if i write file name is not existed in my directory it gives me warning massage " the file name is not existed"

[BIFanatic]

after this:

ws.Cells(RowNum, 3).Value = FileSize
ws.Cells(RowNum, 4).Value = Fil.ParentFolder
RowNum = RowNum + 1

add this:

Else
     MsgBox "The File name doesn't exists in the specified directory"
     Exit Sub
End If

[maghari]

awesome i noted this message when does it show?

 If Err.Number = 76 Then
        MsgBox "The folder path is incorrect, subroutine will now end", vbInformation

[BIFanatic]

in case if the folder path specified is incorrect:

Const MyFolderPath      As String = "C:\Users\leopar\Desktop\search"

[maghari]

it occurred something about adjusting the code warning message when i search name file in sub folder it doesn't show it then show the message when i search in main folder it shows it and show the message

[maghari]

hi, BIFanatic
i write my notices i don't if you noted in post#15

[BIFanatic]

Not sure what you mean in post 15.

[maghari]

you told me about adjusting the warning message in post#12 the code it doesn't work as it was before adjusting the warning message when i search the file name is existed i n sub folder it doesn't show the data in a,b,c,d it shows the warning message and when i search file name is existed in main folder it shows the data in a,b,c,d but at the same time it shows the warning message as you know i would the warning message shows only in one case if the file name is not existed whether in sub folder or main folder
i hope this help to understand me

[snb]

Faster:

Dim c00, fs As Object, y

Sub M_snb()
   Set fs = CreateObject("scripting.filesystemobject")
   c01 = Sheet1.Cells(1, 5)
   If Dir(c01, 16) <> "" Then F_snb c01
   
   ReDim sp(y, 3)
   sn = Filter(Split(c00, vbLf), c01)
   
   For j = 0 To UBound(sn)
     For Each fl In fs.GetFolder(sn(j)).Files
        sp(n, 0) = fl.Name
        sp(n, 1) = fs.getextensionname(fl)
        sp(n, 2) = Round(FileLen(fl) / 1024)
        sp(n, 3) = fl.ParentFolder
        n = n + 1
     Next
   Next
   
   Cells(2, 1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

Sub F_snb(c01)
    For Each fl In fs.GetFolder(c01).subfolders
       y = y + fl.Files.Count
       c00 = c00 & vbLf & fl.Path
       F_snb fl.Path
    Next
End Sub

[maghari]

hi, snb
i tested your code but it gives me error run time error13 and highlight this line

   If Dir(c01, 16) <> "" Then F_snb c01