Finding File Meta Data Using FileSystemObject

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:

User Form

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

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.

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.

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.