Welcome to VBA Tips & Tricks.
All VBA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too
Happy reading
Pages
▼
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"
'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
'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:
Is there any way to get the file size when a file is greater than 4GB ?
ReplyDeleteIf your file is over 4GB I'd suggest that VBA and Excel is the wrong medium...
ReplyDeleteBad suggestion..
ReplyDeleteThey 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
agreed, very bad suggestion.
ReplyDeleteIf FileLen(FilePathFileName) > 4000000000 Then
ReplyDelete4*1024*1024*1024:)
ReplyDeleteuhhm.... bytes arend x1000 for a bigger step from bytes to kilobyes...
ReplyDeleteyou must do X1024
so 1 kb = 1024 bytes
what is would say is....
4000000000 isnt the right value of 4gb
Sub Get_File_Size()
ReplyDelete'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
Thanks DA for posting back the solution
DeleteThe 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.
ReplyDeleteAnd also 1 MB is not the same as 1,000,000 bytes (as someone already wrote above)
ReplyDeleteMsgBox "File size: " & Round(FileLen(File1) / 1048576, 1) & "MB"
ReplyDeleteThis will give you what you want.