Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Size & Modifed Date (Excel XP)

    Can anyone help - I need to show file size and modified date for a group of files in a directory in my spreadsheet. The spreadsheet is used to control a financial consolidation.

    I guess I need to use an API call but I'm just a humble accountant who dabbles with Excel VBA.

    Many Thanks

    QB

  2. #2
    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

    Re: File Size & Modifed Date (Excel XP)

    Hi,
    Something like this should do it:
    <pre>Function GetFileInfo(strDirectory As String) As String()
    Dim arrFiles() As String, i As Integer, strFileName As String
    With Application.FileSearch
    .NewSearch
    .LookIn = strDirectory
    .SearchSubFolders = True 'Up to you if you need this
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
    ReDim arrFiles(.FoundFiles.Count - 1, 2)
    For i = 1 To .FoundFiles.Count
    strFileName = .FoundFiles(i)
    arrFiles(i - 1, 0) = strFileName
    arrFiles(i - 1, 1) = FileDateTime(strFileName)
    arrFiles(i - 1, 2) = FileLen(strFileName) 'length in Bytes
    Next i
    End If
    End With
    GetFileInfo = arrFiles
    End Function
    </pre>

    Note: it's an array function and therefore needs to be array-entered.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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