Inshakhan
New Member
- Joined
- May 25, 2021
- Messages
- 1
- Office Version
- 2019
- 2016
- 2013
- 2011
- 2010
- Platform
- Windows
- MacOS
- Mobile
- Web
Hi.
I have written the below code and I need help please someone can assist in how I can list of all files in a given folder which are more than 50KB in size .
I have written the below code and I need help please someone can assist in how I can list of all files in a given folder which are more than 50KB in size .
VBA Code:
Option Explicit
Sub Run_Tool()
'
' Run_Tool Macro
'
'
Sheets("File_n_Size").Select
Columns("A:AQ").Select
Selection.Delete Shift:=xlToLeft
Range("B2:B20033").Select
Selection.NumberFormat = "@"
Range("C2:C20033").Select
Selection.NumberFormat = "#,##0"
Range("A2").Select
Dim main_path As String
Dim temp_row As Long
Dim folder_row As Long
main_path = Worksheets("Master").Range("D1").Value
Range("A1").Value = "Parent_Folder"
Range("B1").Value = "File_or_Folder_Name"
Range("C1").Value = "File_or_Folder_Size"
temp_row = 2
folder_row = 2
Call iterate(main_path, temp_row)
Do While (Range("D" & folder_row).Value <> "")
If Range("D" & folder_row).Value = "Folder" Then
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
temp_row = .Rows.Count + 1
End With
Call iterate(Range("A" & folder_row).Value & "\" & _
Range("B" & folder_row).Value, temp_row)
End If
folder_row = folder_row + 1
Loop
End Sub
Sub iterate(my_path As String, row_num As Long)
Dim fso As New FileSystemObject
Dim oFolder, temp_folder As Folder
Dim oFile As File
Dim strFolderName As String
strFolderName = my_path
Set oFolder = fso.GetFolder(strFolderName)
For Each oFile In oFolder.Files
Range("A" & row_num).Value = strFolderName
Range("B" & row_num).Value = oFile.Name
Range("C" & row_num).Value = oFile.Size
row_num = row_num + 1
Next oFile
For Each temp_folder In oFolder.SubFolders
Range("A" & row_num).Value = strFolderName
Range("B" & row_num).Value = temp_folder.Name
Range("C" & row_num).Value = temp_folder.Size
row_num = row_num + 1
Next temp_folder
Set oFile = Nothing
Set oFolder = Nothing
Set temp_folder = Nothing
Set fso = Nothing
End Sub