Get size of file

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

I have the path and file name and want to retrieve the
file size. The file can be of any type - not necesarrily
an .mdb file.

How is that done?
 
1. Open a standard module in the vba editor.
2. In the vba editor, open the TOOLS menu and select References.
3. Put a checkmark against "Microsoft Scripting Runtime" and close the
References dialog.
4. The following code demonstrates how to get a file's size:

Sub WrapperGetFileSize()

Dim vntSize As Variant

' Get size of "C:\Autoexec.bat":
vntSize = GetFileSize("C:\", "Autoexec.bat")

' Show result:
MsgBox "File Size: " & vntSize & " Bytes"
MsgBox "File Size: " & Round(vntSize / 1024, 3) & " Kilobytes"

End Sub

Function GetFileSize(strPath As String, strFileName As String) As Variant

Dim objFSO As Scripting.FileSystemObject
Dim objFLDR As Scripting.Folder
Dim objFILE As Scripting.File

On Error GoTo HandleErrors

' Initialise:
Set objFSO = New Scripting.FileSystemObject
Set objFLDR = objFSO.GetFolder(strPath)

' Fix path:
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

' Exit if file doesn't exist:
If Not objFSO.FileExists(strPath & strFileName) Then GoTo
FileDoesNotExist

' Point to file:
Set objFILE = objFLDR.Files(strFileName)

' Return file size to calling procedure:
GetFileSize = objFILE.Size

Bye:

' Destroy variables:
Set objFILE = Nothing
Set objFLDR = Nothing
Set objFSO = Nothing
Exit Function

FileDoesNotExist:

MsgBox "File doesn't exist", vbExclamation + vbOKOnly, "Error"
GoTo Bye

HandleErrors:

MsgBox Err.Description
Resume Bye

End Function
 
Why bother with the headaches associated with adding an unnecessary
Reference when VBA has the capability to do it as is?
 
Back
Top