Excel VBA Tool To Get File Properties

Complete Excel VBA Course

Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file.

  • File Name
  • Date Created
  • Date Last Accessed
  • Date Last Modified
  • Size (MB)
  • Type 

You just need to browse the folder where your files are and click on ‘Get File Properties’ button

File Properties Tool Excel
'This function loops through all the files in the folder and
'gets the properties of each file and displays them on the sheet
Public Sub GetFileProperties()
    'Variable Declaration
    Dim objFS As Object
    Dim objFile As Object
    Dim strPath As String
    Dim vFile As Variant
    Dim iCurRow As Integer
    'Clear old data from the sheet
    Sheet1.Range("C7:H" & Sheet1.Rows.Count).ClearContents
    'Set the path of the folder
    strPath = Sheet1.Range("C3").Value
    'Add slash at the end of the path
    If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    'Set Directory to folder path
    ChDir strPath
    vFile = Dir(strPath & "*.*") 'Change or add formats to get specific file types
    'Set the variable to FileSystemObject
    Set objFS = CreateObject("Scripting.FileSystemObject")
    iCurRow = 7
    Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through
        Set objFile = objFS.getfile(vFile)
        'File name
        Sheet1.Cells(iCurRow, 3).Value = objFile.Name
        'Date Created
        Sheet1.Cells(iCurRow, 4).Value = objFile.DateCreated
        'Date Last Accessed
        Sheet1.Cells(iCurRow, 5).Value = objFile.DateLastAccessed
        'Date Last Modified
        Sheet1.Cells(iCurRow, 6).Value = objFile.DateLastModified
        'Size
        Sheet1.Cells(iCurRow, 7).Value = Round(objFile.Size / 1024 / 1024, 2)
        'Type
        Sheet1.Cells(iCurRow, 8).Value = objFile.Type
        vFile = Dir
        iCurRow = iCurRow + 1
    Loop
End Sub

How to use Excel VBA Tool to get file properties:-

  • Save the following attachment in your system File Properties Tool
  • Unzip the file and open
  • Browse the folder which has your files
Get File Properties from a folder Excel VBA
  • Click on ‘Get File Properties’ button
Get File Properties from a folder Excel VBA
  • Done, you will get all the files available in the folder along with file properties
  • To clear the data, click on ‘Clear Data’
  •  
Get File Properties from a folder Excel VBA

We hope that you loved this article and tool. Please share this with your friends and share your feedback, views in below comment box.Looking forward to see you again.

Complete Excel VBA Course

Note: ExcelSirJi team works hard to help it’s subscribers and visitors to make full use of the free templates. As part of this objective, you are free to use this template. To get unprotected copy, you may refer the below link.

Recommended Articles

Similar Posts

7 Comments

  1. It’s just what I need, but the download does not work.

      1. Now it works perfectly. Thank you very much for your prompt reply!

  2. Hi. Is there any chance to get unprotected version?

    1. Hello Jed,

      Please accept our apologies for delay in response.

      As you know that we at ExcelSirJi works hard to help our subscribers and visitors to make full use of the free codes and templates published by our team.

      As part of our objective, you are free to use these templates. However to access the code, you may refer the below link to get unprotected version.

      https://courses.excelsirji.com/cartflows_flow/file-properties-tool/file-properties-tool/

      Regards,
      Your Excel Mate

  3. This is a good bit of code. What I am struggling with is to amend the code to carry on the search in all subfolders. Ideally I’d love some code that would allow me to look for all Purchase Orders files containing “(SPO)” and to list them as you have here with a hyperlink on the file name. Again ideally this files contains field, would be a user typed variable, so we could look at all acknowledgements “(ACK)” or project references etc

Leave a Reply

Your email address will not be published. Required fields are marked *