Friday, May 04, 2007

Get the File Size

File Size - VBA Function

Sub Get_File_Size()

File1 = "c:\temp\Sample.txt"

MsgBox "The Size of the File is " & FileLen(File1) & " bytes"

End Sub

12 comments:

  1. Anonymous5:09 AM

    Is there any way to get the file size when a file is greater than 4GB ?

    ReplyDelete
  2. Anonymous7:01 AM

    If your file is over 4GB I'd suggest that VBA and Excel is the wrong medium...

    ReplyDelete
  3. Anonymous9:29 AM

    Bad suggestion..

    They didn't say that they had an Excel File bigger than 4GB, just how do I check to see if A (any) file is over 4GB..

    smh

    ReplyDelete
  4. Anonymous2:07 PM

    agreed, very bad suggestion.

    ReplyDelete
  5. Anonymous10:19 AM

    If FileLen(FilePathFileName) > 4000000000 Then

    ReplyDelete
  6. Anonymous6:46 AM

    4*1024*1024*1024:)

    ReplyDelete
  7. Anonymous8:55 AM

    uhhm.... bytes arend x1000 for a bigger step from bytes to kilobyes...

    you must do X1024

    so 1 kb = 1024 bytes

    what is would say is....

    4000000000 isnt the right value of 4gb

    ReplyDelete
  8. Sub Get_File_Size()

    'Written by VBADUD
    'Respectfully modified/commented by yourexcelguy@gmail.com

    'adapted from http://vbadud.blogspot.ca/2007/05/get-file-size.html
    'Read more at http://vbadud.blogspot.com/2007/05/get-file-size.html#FE4XB444awHluIM6.99

    'I hate that it takes five mouse clicks to get the file size going through Start button, etc.
    'Had to find a better way to do it, found this code.

    'VBADUD's original code looks like this:

    'File1 = "c:\temp\Sample.txt"
    'MsgBox "The Size of the File is " & FileLen(File1) & " bytes"

    'Below are my thoughts and modifications

    'VBADUD's code starts with this line
    'File1 = "c:\temp\Sample.txt"

    'but it require that the user must know, and must change for each file, the file path and name. Not cool.
    ' found the below line at http://www.techrepublic.com/blog/10things/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/967
    'GetActiveWB = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

    'merged the two ideas to get this below line, which finds the active filepath and filename

    File1 = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

    'Then VBADUD does the following
    'MsgBox "The Size of the File is " & FileLen(File1) & " bytes"
    ' but it's not easy to read (to me anyway), so I modified it to be more readable to me, per below:

    MsgBox "File size: " & Round(FileLen(File1) / 1000000, 1) & "MB"

    End Sub

    ReplyDelete
    Replies
    1. Anonymous2:39 AM

      Thanks DA for posting back the solution

      Delete
  9. Anonymous6:40 AM

    The later code will not work because the FileLen function returns Long value which in VBA can only have maximum value of 2,147,483,647.

    ReplyDelete
  10. Anonymous6:44 AM

    And also 1 MB is not the same as 1,000,000 bytes (as someone already wrote above)

    ReplyDelete
  11. Anonymous11:40 AM

    MsgBox "File size: " & Round(FileLen(File1) / 1048576, 1) & "MB"

    This will give you what you want.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.