locked
VBA to get File Size RRS feed

  • Question

  • Hello,

    I have a 2016 Access database on a Win10 machine. I currently use the code below on my form to give me the date modified for a specific file. Can this be modified or does someone have a vba that will give me the file size?

    Cheers, Kevin

    'The following steps will return the Date Modified for the specified file.
    '1. Copy/paste the code below to a empty module.
    '2. On your form, create a unbound text box and add the following in the
    '   Control Source property: =GetFileDateTime("C:\PATH TO FILE\FILE NAME.EXT")
    
    
    Public Function GetFileDateTime(sFilePathAndName As String) As Variant
    On Error GoTo Error_Handler
    
        'Validation/Check
        'Probably should ensure you have been passed a valid file path and file name
    
        GetFileDateTime = FileDateTime(sFilePathAndName)
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
                "Error Number: " & Err.Number & vbCrLf & _
                "Error Source: " & sModName & "/GetFileDateTime" & vbCrLf & _
                "Error Description: " & Err.Description, _
                vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function

    Wednesday, July 29, 2020 5:24 PM

Answers

  • Kevin -

    "No luck" is too vague to decipher, especially in a std Module.

    For example, I would write the function like this

    Public Function TestFileSize() as Long
    Dim LResult As Long
    
    LResult = FileLen("C:\FILE_PATH\FILE_NAME.EXT")
    TestFileSize = LResult
    
    End Function


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by KevinATF Thursday, July 30, 2020 12:13 PM
    Thursday, July 30, 2020 2:22 AM

All replies

  • You can simply use FileLen() to get the size of a file.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, July 29, 2020 6:53 PM
  • Hi Daniel,

    Thanks for your reply. How do I use FileLen()?

    I tried putting it in a query as Expr1:FileLen("C:\FILE_PATH\FILE_NAME.EXT"). No luck.

    I tried putting it in a standard module (see below) with no luck.

    Public Function TestFileSize()
    Dim LResult As Long
    
    LResult = FileLen("C:\FILE_PATH\FILE_NAME.EXT")
    
    End Function
    

    Wednesday, July 29, 2020 7:05 PM
  • I would not define it as Long. See https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/filelen-function

    Dim MySize
    MySize = FileLen("FILE PATH AND FILE NAME WITH EXTENSION")
    msgbox MySize

    Wednesday, July 29, 2020 8:06 PM
  • I would not define it as Long.

    Hi Lawrence,

    Why not?

    Imb.

    Wednesday, July 29, 2020 9:06 PM
  • It's strange that you would not define as long, in light of this text on the help page you referenced:
    Returns a Long specifying the length of a file in bytes.

    -Tom. Microsoft Access MVP

    Thursday, July 30, 2020 1:33 AM
  • Kevin -

    "No luck" is too vague to decipher, especially in a std Module.

    For example, I would write the function like this

    Public Function TestFileSize() as Long
    Dim LResult As Long
    
    LResult = FileLen("C:\FILE_PATH\FILE_NAME.EXT")
    TestFileSize = LResult
    
    End Function


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by KevinATF Thursday, July 30, 2020 12:13 PM
    Thursday, July 30, 2020 2:22 AM
  • What did LResult return?  It will return the number of bytes and then you can convert that to whatever format (by dividing by multiples of 1024) to get the format you want (Kb, Mb, ....).

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, July 30, 2020 2:43 AM
  • Hi KevinATF,

    Have you tried "Currency" datatype?

    I think it might be due to Win10 64bit.

    But I think Lawrence suggestion will work. Works fine in Win7 & Access 2000. (works fine with "String" as well)

    I did some research, they suggest "Currency" but that was different question.

    Thursday, July 30, 2020 4:41 AM
  • Peter,

    You suggestion did the trick. Thank you. And thanks to all for your suggestions.

    Cheers, Kevin

    Thursday, July 30, 2020 12:14 PM