• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Finding File Meta Data Using FileSystemObject

You are here: Home / Excel VBA / Finding File Meta Data Using FileSystemObject
Finding File Metadata Using FileSystemObject
November 6, 2015 by Philip Treacy

In a previous post I looked at using the FileSystemObject to search through folders on your computer's disk and create a hyperlinked list of files. Clicking on the hyperlink would open the file.

FileSystemObject (FSO) can also be used to retrieve meta data from a file, like the creation date, last modified date or file size.

In this post we'll look at modifying the code from my fist post on this topic, and creating a hyperlinked list of files with some file meta data.

Please read my first post as it covers some important steps like creating a reference to the Microsoft Scripting Library, without which this code won't work. It also has an explanation of recursion which is very useful programming concept to understand, and which this code uses.

Plus, I'm going to create a userform to ask what meta data we want listed

File Meta Data

Using FileSystemObject.GetFile we can access various file meta data, some of which I am not interested in for this example. I'm going to use DateCreated, DateLastModified, Size and Type.

To see the full list of meta data you can access, in the VBA editor, use the auto-complete list to see what is available:

FileSystemObject Properties

User Form

The userform is pretty simple, just 4 check boxes and a text box surrounded by a frame, and two buttons.

Userform to gather meta data

Each checkbox corresponds to some meta data that I want to retrieve. The text box allows the user to type in their search term e.g. *.xl* if they are looking for Excel workbooks. The function of the buttons is self explanatory.

You could enhance the form by adding checkboxes for common file types e.g. Excel Workbooks, Excel Macro Enabled Workbooks, or PDF's. This will reduce what has to be typed, and perhaps even remove the need for a text box altogether.

How Does the Form Work?

When a check box is checked, its value is True. If it is unchecked its value is False.

So to see what meta data we need to look for we just test to see what checkbox values are true, e.g.

If FileSize Then

Because the checkbox is a Boolean object, it can only be true or false, I don't have to explicitly test for the values True or False like so

If FileSize.Value = True Then

The textbox is used to specify the filename pattern to match e.g. *.xls? which matches all files with extensions .xlsm or .xlsx. The wildcards * (0 or more characters) and ? (a single character) can be used.

Picking the Folder to Search

Once we know what meta data and files to look for we need to pick a folder to search. To do this we use the Application.FileDialog property

This allows us to choose a folder and then use the Dir function to search that folder, and its subfolders. This is done recursively, check my first article on this to see an explanation of a recursive routine.

Choose a folder to search

The List of Files

As the code searches our selected folder (and its subfolders), we get a list on our worksheet of any files found. The name of the file is hyperlinked to the file, so clicking the link will open that file.

Right beside the filename/hyperlink is the meta data we said we wanted.

Search results with meta data

Acknowledgement

Thanks to our Excel guru Catalin for some of the FSO code that went into this routine.

Example Workbook

In the example workbook, click on the blue button at the top of Sheet1 to start the macro.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download the workbook from here

Finding File Metadata Using FileSystemObject

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Calculate end of period dates in Excel

Excel End of Period Dates

Calculating Excel End of Period Dates can be tricky if you don't use standard month ends. Here is a formula and UDF that solves the problem.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: Excel VBA
Previous Post:PivotTable MedianCalculating MEDIAN in PivotTables
Next Post:Excel User VoiceExcel User Voice

Reader Interactions

Comments

  1. André Turgeon

    March 4, 2020 at 8:46 am

    Hello, I’m using your Code of List of Files with great interest; but I have a line failure: Current Filename = Dir(TargetFiles, 7) can I be helped to solve this problem. Thank you for your support, it is really appreciated.

    Reply
    • Philip Treacy

      March 4, 2020 at 9:09 am

      Hi André

      Difficult to say what’s causing that without seeing your workbook and knowing what inputs you are providing to the code.

      TargetFiles is constructed from the line TargetFiles = StartingFolder.Path & “\” & FileType so what are the values for StartingFolder.Path and FileType?

      It could be the path separator is causing you issues. Try changing

      TargetFiles = StartingFolder.Path & “\” & FileType

      to this

      TargetFiles = StartingFolder.Path & Application.PathSeparator & FileType

      If that doesn’t work you can try debugging the code.

      Or post a question on our forum and tell me what folder you are searching in and what file type you are looking for when this error occurs.

      Regards

      Phil

      Reply
  2. Esther Joy

    November 13, 2015 at 10:31 pm

    Thanks for giving such a nice article . The information you shared will help me to get a good knowledge.

    Reply
    • Philip Treacy

      November 14, 2015 at 8:44 am

      You’re welcome Esther.

      Reply
  3. Jon Acampora

    November 8, 2015 at 5:38 am

    Very cool Phil! Thanks for sharing. I like the format of the output. It would be great to also have an output in a tabular format in an Excel Table.

    Reply
    • Philip Treacy

      November 8, 2015 at 10:27 am

      Thanks John. Good suggestion. Shouldn’t take to much work to recode with a table as output.

      Cheers

      Phil

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.