Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Returning File Size from VBA in Excel

    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

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Last edited by AndrewKKWalker; 2011-11-06 at 06:37. Reason: Added Code
    Andrew

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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("drive\path"[,"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 ***


    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")
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-11-06 at 07:41.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Also, I think you forgot, as I do all the time, that Optional arguments need to be declared Variant for IsMissing to work.
    YEP!
    Andrew

  7. #7
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    My thanks to both of you

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could just use FileLen(filepath) which returns the size in bytes.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  10. #10
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If using RG's code you'll need Double rather than Long data types.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Rory, Excellent. Worked like a charm. Thank you. Geoffrey

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •