VBA Excel Function for returning file size in byte

Question

I wish to return the file size of some files in the same folder or in a different one with VBA in Excel 2010.

Answer 1

There is a very nice and simple VBA function, which was not mentioned so far, FileLen:

FileLen("C:\Temp\test file.xls")

It returns the size of the file in bytes.

In combination with looping through files in a directory it's possible to achieve what you originally wanted (get sizes of files in a folder).

Answer 2

Here how to use it in Excel Cell:

 =GetDirOrFileSize("C:\Users\xxx\Playground\","filename.xxx")

If you have a german Windows than:

=GetDirOrFileSize("C:\Users\xxx\Playground\";"filename.xxx")

Here is the function for the VBA modul: (Just enable the Developer tools, and copy and paste this into a new modul)

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 & "\"
   'Thanks to Jean-Francois Corbett, you can use also OFS.BuildPath(strFolder, strFile)

   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 ***