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 Read Excel Data using Connection String
EXCEL VBA TRICKS
VBA to Read Excel Data using Connection String

Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open the file using VBA and read the data. Click Here Stablish ADODB connection with Excel file and read data using SQL queries Here …

VBA To Rename Files
EXCEL VBA TRICKS
Excel VBA Tool to Rename Files

Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in a selected folder. Just browse the folder and hit ‘Rename Files’ button ‘This function loops through all the files listed in the sheet and ‘rename them as …

EXCEL VBA TRICKS
VBA Code to Change Sheet Tab Color

Here is another help code for programmers to change the color of Excel sheet tab. I have shown two ways to achieve this: Using standard colors like Green, Blue, Red, White etc. Using RGB (Red, Green Blue) function to set a specific color ‘This function changes the tab color of …