Excel VBA Tool to Get File Properties


3

Here is one more interesting VBA tool from ExcelSirJi team. File Properties Tool is an Excel VBA tool which 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

Excel Tricks

'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 this tool:

  • Save the following attachment in your system File Properties Tool
  • Unzip the file and open
  • Browse the folder which has your files

Excel Tricks

  • Click on ‘Get File Properties’ button
  • Excel Tricks
  • Done, you will get all the files available in the folder along with file properties
  • To clear the data, click on ‘Clear Data’
  • Excel TricksWe 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.

3
Leave a Reply

avatar
1 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
excelsirjiMichela Recent comment authors
newest oldest most voted
Michela
Guest
Michela

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

EXCEL VBA TRICKS
VBA Code to Sort Data

Here is a one line code for developer’s reference which can be used to sort data ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”), Order1:=xlAscending, Header:=xlYes, DataOption1:=xlSortNormal Before Sort: After Sort: If you are looking for a VBA code that can sort the data …

EXCEL VBA TRICKS
VBA Code to Filter Data in Excel

Here is an easy reference code which filters data in the sheet. In the code, we have filtered the data in three steps. Step 1: Remove existing filter from the sheet (if any). This code ensures if there is already a filter applied then the same is removed first Step …

EXCEL VBA TRICKS
VBA Tool to Consolidate Excel Files from a Folder

From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free VBA tool from ExcelSirJi team. With this Excel Consolidator tool, you will be able to consolidate data from …