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.

VBA Code to Sum Cells by Color
EXCEL VBA TRICKS
VBA Code to Sum Cells by Color

Recently some of our subscribers have requested us to share a VBA code that can sum the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to sum the cells with specific color and returns the total sum of the matching color cells

VBA Code to Delete File
EXCEL VBA TRICKS
VBA Code to Delete File

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

VBA Code to Add Border to Excel Range
EXCEL VBA TRICKS
VBA Code to Add Border to Excel Range

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

DO YOU WANT MORE UPDATE FROM US?

Enter your email address to follow this blog