File size

Inshakhan

New Member
Joined
May 25, 2021
Messages
1
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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 .
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Welcome!

At a quick glance, it looks like you have code to iterate over the files you want. Take a look at the FileLen function to determine the file size of a specific file (in bytes).
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

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