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 :think:

[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:doh:

[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) (https://stackoverflow.com/questions/27367190/how-to-return-kb-mb-and-gb-from-bytes-using-a-public-function) 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 :think:

[maghari]

hi, BIFanatic (http://www.vbaexpress.com/forum/member.php?78735-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

[BIFanatic]

Good catch, made some changes, try this and let me know.

[maghari]

great work thanks BIFanatic i appreciate your effort thanks for every thing the code works completely :clap::clap::clap:

[BIFanatic]

You're welcome! Happy to help. :)