Returning File Size from VBA in Excel

Topic

New Reply

I wish to return file size from VBA to a function call from the old Macro language. I hope to be able to do so for a nominated file and path or for a folder and all its constituents. I’ve made a poor attempt to do so below, and cannot get it right. The other function I use (ZFileJoinPath) is OK. I’d very much appreciate assistance in correcting my ZFileSize.

Regards

Function ZFileSize( _
strFile As String, _
Optional strDir As String) As Boolean
‘Returns size of File in Bytes OR
‘for Folders the size of all files and subfolders in the folder OR
‘FALSE
On Error GoTo ErrHandler

strPathFile = ZFileJoinPath(strFile, strDir)
If strPathFile = “” Then GoTo ErrHandler
ZFileSize = strPathFile.Size
Exit Function
ErrHandler:
ZFileExists = False
End Function

Function ZFileJoinPath( _
File As String, _
Directory As String) As String
’Joins File & Directory into One String
‘File or Directory may be absent

Dim strPath As String

If File = “” Then
ZFileJoinPath = Directory
Exit Function
End If

If Directory = “” Then
ZFileJoinPath = File
Exit Function
End If

strPath = Directory
If Not Right(strPath, 1) = “” Then
strPath = strPath & “”
End If

strPath = strPath & File
ZFileJoinPath = strPath

End Function

Reply | Quote

Replies

Not sure where you were expecting to get file size from.
You need in VBA to use the Microsoft Scripting Runtime library.
You can set this in Tools References

Then you can set a scripting filesystem object
and from that get the basic file information.

The function below returns the size in Bytes of the file whose folder and path are passed.

Code:

Function GetFileSize(strFile As String, Optional strFolder As String)

Dim lngFSize As Long, lngDSize As Long
Dim oFO As File
Dim oFD As Folder
Dim OFS As New FileSystemObject

lngFSize = 0

Set OFS = New Scripting.FileSystemObject
If Not IsMissing(strFolder) Then
   If strFolder = "" Then strFolder = ActiveWorkbook.Path
   If Right(strFolder, 1)  "" Then strFolder = strFolder & ""
   If strFile  "" Then
    If OFS.FolderExists(strFolder) Then
        If OFS.FileExists(strFolder & strFile) Then
            Set oFO = OFS.GetFile(strFolder & strFile)
            Set oFD = OFS.GetFolder(strFolder)
            lngFSize = oFO.Size
            lngDSize = oFD.Size
        End If
     End If
    End If
End If

GetFileSize = lngFSize
'You could use lngDSize to return instead the size of the Directory

End Function

Reply | Quote

Andrew,

Thank you for your help. I should have explained that I distribute my code among a number of users and cannot rely on their settings. Without changing my environment as you suggest your code I found would not work but the code below that I found in VBA Help did work. However it only leaves a message for folders and does not return a result. This was what I was attempting to change but my VBA skills are insufficient for the task. To my untutored way of thinking, if the value can be obtained to leave in a message, it should be available to return as a result, and for both folders and files. If you have the time, I’d be very grateful for your insights

Regards

Syntax
object.Size
The object is always a File or Folder object.
Remarks
The following code illustrates the use of the Size property with a Folder object:

Sub ShowFolderSize(filespec)
Dim fs, f, s
Set fs = CreateObject(“Scripting.FileSystemObject”)
Set f = fs.GetFolder(filespec)
s = UCase(f.Name) & ” uses ” & f.size & ” bytes.”
MsgBox s, 0, “Folder Size Info”
End Sub

Reply | Quote

Not a problem. I often have to use late binding to overcome incompatible library issues.
You should find that other than the declaration of the objects as Object rather than an implicit type, my example would still work.

Code:

Function GetFileSize(strFile As String, Optional strFolder As String)  
Dim lngFSize As Long, lngDSize As Long 
Dim oFO As Object 
Dim oFD As Object Dim OFS As Object  lngFSize = 0  
Set OFS = CreateObject("Scripting.FileSystemObject") 
If Not IsMissing(strFolder) Then    
  If strFolder = "" Then strFolder = ActiveWorkbook.Path
    If Right(strFolder, 1)  "" Then strFolder = strFolder & ""
    If strFile  "" Then
     If OFS.FolderExists(strFolder) Then
         If OFS.FileExists(strFolder & strFile) Then
             Set oFO = OFS.GetFile(strFolder & strFile)
             Set oFD = OFS.GetFolder(strFolder)
             lngFSize = oFO.Size
             lngDSize = oFD.Size
         End If
      End If
     End If
 End If  
GetFileSize = lngFSize 'You could use lngDSize to return instead the size of the Directory  
End Function

Reply | Quote

The function you published works fine unless the file size exceeds a positive integer value. (> 2gb) I’m looking for a way to determine the file size when it is 3Gb or bigger. Any suggestions?

Reply | Quote

Andrew,

I played around with your code a little to, hopefully, meet the OP’s requirements, at least as I read them. YMMV.
Also, I think you forgot, as I do all the time, that Optional arguments need to be declared Variant for IsMissing to work.

Code:

Function GetDirOrFileSize(strFolder As String, Optional strFile As Variant) As Long

‘Call Sequence: GetDirOrFileSize(“drivepath”[,”filename.ext”])
   
   Dim lngFSize As Long, lngDSize As Long
   Dim oFO As Object
   Dim oFD As Object
   Dim OFS As Object
   
   lngFSize = 0
   Set OFS = CreateObject(“Scripting.FileSystemObject”)

   If strFolder = “” Then strFolder = ActiveWorkbook.Path
   If Right(strFolder, 1)  “” Then strFolder = strFolder & “”
   
   If OFS.FolderExists(strFolder) Then
     If Not IsMissing(strFile) Then
       
       If OFS.FileExists(strFolder & strFile) Then
         Set oFO = OFS.GetFile(strFolder & strFile)
         GetDirOrFileSize = oFO.Size
       End If
       
       Else
        Set oFD = OFS.GetFolder(strFolder)
        GetDirOrFileSize = oFD.Size
       End If
   
   End If
   
End Function   ‘*** GetDirOrFileSize ***

:cheers:

Edit: As I re-read this I guess it needs to be pointed out that if you are looking for a file in the current directory you need to pass a null string to the function, maybe not the best choice but no code, at least mine, is perfect. Ex: GetDirOrFileSize(“”,”MyTestFile.dat”)

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Also, I think you forgot, as I do all the time, that Optional arguments need to be declared Variant for IsMissing to work.

YEP! :rolleyes:

Reply | Quote

My thanks to both of you

Reply | Quote

You could just use FileLen(filepath) which returns the size in bytes.

Reply | Quote

My thanks to all of you; I’m really bowled over with your excellent level of assistance. I intend to try to create an amalgam of the two concepts, combining the speed and simplicity of one with the generality of the other.

Reply | Quote

I’ve just noticed that when using the Folder measurement option with a very large folder I get the error response “Run-time error ‘6’: Overflow”. Would it be possible to remedy this or to provide an appropriate error message?

Reply | Quote

If using RG’s code you’ll need Double rather than Long data types.

Reply | Quote

Rory, Excellent. Worked like a charm. Thank you. Geoffrey

Reply | Quote

Indeed – the FileLen(filepath) function uses the Long format which is limited to a maximum value of 2,147,483,647

Instead you can use the construct described here:

https://msdn.microsoft.com/en-us/library/aa243182(v=vs.60).aspx

It worked for me on a single file over 4GB, by changing fs.GetFolder to fs.GetFile

Reply | Quote

This worked just fine. Thanks. My problem was that i had declared the size variable as ‘long’ which i believe is documented as being 64 bits. Apparently it is only 36 bits. Confusing specs. Thanks anyway.

Reply | Quote

46496-Numerical-types-limits

Reply | Quote

..where’s Double-Double?

..that would be a delight.

zeddy

Reply | Quote

..what I meant was LongLong

LongLong (LongLong integer) variables are stored as signed 64-bit (8-byte) numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. The type-declaration character for LongLong is the caret (^). LongLong is a valid declared type only on 64-bit platforms.

zeddy

Reply | Quote

Hey Y’all,

Just to clarify…

LongLong is a valid declared type only on 64-bit platforms.

Means 64 bit Software! 32 Bit Excel will not allow this even though running on a 64 bit machine with a 64 bit OS. Just thought I’d clarify.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..and just to be clear, Excel 64-bit versions have been available for all Office versions since Office 2010.
..LongLong looking forward to the 256-bit Excel version.

zeddy
(I was kidding about the looking-forward-to bit)
..but I am looking forward to my tropical trip, where I can put on my ShortShorts)

Reply | Quote

I wish I’d known about LongLong – would have come in handy for my tax returns :rolleyes:

Reply | Quote

Hi Martin

..in Zimbabwe dollars that LongLong max value would only get you US$263.53, when they scrapped their local currency in June 2015. You would need more than that for an hour of your accountant’s time.
You would also need lots of their one-hundred-trillion-dollar notes.
(You could get US$5 for just 175 quadrillion of theirs).

..just saying

zeddy

Reply | Quote

Actually off to spend my East Caribbean Dollars – charity work in Dominica – at 3 to the £. So Long will suffice for the accounts :rolleyes:

Reply | Quote

Hi Martin

Have you been to Dominica before? It’s a fabulous place!
It just so happens that Dominica is one of my most favourite islands. I worked there on many occasions.
..and they knew me very well in the Inland Revenue office.
I like to call it Waikikibulli (sounds Hawaiian) – which is the name the original Carib Indians call the island.
My favourite hotel was the Fort Young (I think I’ve stayed in every room there)- it was one of the few hotels that had a lift (elevator for our US friends). Check out the badge on the floor of the lift – It says “Schindlers” (always brought a smile to my face).
Now, if you land at Canefield airport, be aware that, rather than a gentle 15-degree approach glide path, the aircraft ‘attack’ the runway with a 30-degree approach, and, I believe, have to ‘stall’ the engines immediately on landing. (it’s a short runway – I still remember a lady passenger shouting ‘Don’t bounce the dog’ (the dog was taking a shortcut across the runway))
Dominica has a proper rain forest and is in the top ten dive locations of the world.
Happy days indeed.

zeddy

Reply | Quote

Hi Zed,
Staying with friends in Roseau for a week, doing charitable things, then a week in a cabin in the rain forezt.
However we’re flying into Melville Hall, which is reasonably flat if not necessarily straight !
I’ll let you know how we get on – everyone says how wonderful it is
Em

Reply | Quote

Hi Martin

..remember, it’s called a rain forest for a reason.. you’ll need an umbrella.
..and don’t let anyone trick you with ordering Mountain Chicken on the dinner menu..(like what happened to me)
..it is not chicken as we know it.
(I now know exactly what it is. Still, it tasted nice though)

zeddy

Reply | Quote