Excel 2010: Function to acquire file size in bytes

For the purpose of planning play list play times, is there an Excel function to get a file's size in bytes?

For example column A, I list MP3 files by name, for example: (The file in A34)
     E:\Music\PlayList1\The Little River Band - Down On The Border.MP3

Is there an excel function such as "FileSizeOf(A34)"  and return  4022196
     (the actual size of the file in bytes)

TIA - Ed
LVL 3
Ed CovneyRetiredAsked:
Who is Participating?
 
dlmilleCommented:
Yes, you can do that.  Attached, I wrote your function which you can use like you asked.  I also create a macro to just give you the result on all files in column A (which may be faster/more efficient).

PS - the FileLen() function will provide size in bytes up to 4GB.

Function fileSizeOf(r As Range) As Variant
    On Error Resume Next
    fileSizeOf = FileLen(r.Value)
    On Error GoTo 0
End Function
Sub getFilesizes()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    For Each r In rng
        r.Offset(, 1).Value = fileSizeOf(r)
    Next r
    
End Sub

Open in new window


See attached demonstration workbook.  Just put your file names  fully pathed in Column A and copy down the formula, or hit the button to just give you the result.

Dave
fileSizeInfo-r1.xls
0
 
dlmilleCommented:
For files of any size you can use this workbook with a function & complimentary macro that returns the file size in GB's instead.  > 4GB would be a very large MP3, but just for completeness, here's that solution ;)

See attached.

Dave
fileSizeInfo-r2.xls
0
 
Ed CovneyRetiredAuthor Commented:
Dave - EXCELLENT !!

Now I just have to figure out how to import your macro into my spreadsheet.
I have lots to learn, thanks for a great start.

Ed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.