File System Object in VBA: Get Files Information in Excel

The File System Object lets Excel VBA read a folder and bring the details of every file into a worksheet: the file name, the file type, the size in KB and the date it was last modified. Type a folder path in one cell, click Get Information, and the list appears in seconds. It is a small macro, but it is the starting point for real file automation: renaming, moving or converting files only works once you can list them. In this tutorial you build the macro line by line, exactly as in PK’s video and download file.

⬇️  Download the Get File Information Workbook
Single-sheet workbook · .xlsb with macros (20 KB) · enable macros after opening

Watch the Video Tutorial

The video shows the finished list for a folder of Excel and Word files, then writes the macro in a new module, explains why the size is divided by 1024 and assigns it to a button.

What You Will Build

• A folder path cell (H1) that tells the macro which folder to read.
• A four-column file list: File Name, File Type, File Size (In KB) and Last Modified On.
• A For Each loop over the folder’s Files collection, using the File System Object.
• A Get Information button that runs the macro with one click.

How the Sheet Is Laid Out

• A1:D1 – the headers File Name, File Type, File Size (In KB) and Last Modified On.
• G1 – the label Folder Path; H1 – the folder to read, for example C:\Users\YourName\Desktop\Files.
• Rows 2 and below – filled by the macro, one row per file.
• A rounded-rectangle shape labelled Get Information sits under the folder path.

In the sample file the folder holds two Excel reports and eight Word documents, so the macro returns ten rows, for example Report-1.xlsx, Microsoft Excel Worksheet, 6 KB, and Test-1.docx, Microsoft Word Document, 17 KB.

Part 1 – Set Up the File System Object

Step 1: Open the VBA editor and insert a module

Go to Developer > Visual Basic or press Alt+F11, then choose Insert > Module. Start a new macro called Get_Files_Information.

Step 2: Add the Microsoft Scripting Runtime reference

The File System Object is not part of VBA itself; it comes from the Scripting Runtime library. In the editor go to Tools > References, scroll down to Microsoft Scripting Runtime, tick it and click OK. Without it, VBA does not recognise FileSystemObject, Folder or File.

Step 3: Declare the objects

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

• sh is Sheet1, where the folder path and the results live.
• fso is a new File System Object, the entry point to the file system.
• fo will hold the folder and f each file inside it as the loop runs.

Part 2 – Get Files Information into Excel

Step 4: Open the folder and loop through its files

Set fo = fso.GetFolder(sh.Range("H1").Value)

Dim last_raw As Integer

For Each f In fo.Files
     last_raw = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
     sh.Range("A" & last_raw).Value = f.Name
     sh.Range("B" & last_raw).Value = f.Type
     sh.Range("C" & last_raw).Value = f.Size / 1024
     sh.Range("D" & last_raw).Value = f.DateLastModified
Next

How the code works

• fso.GetFolder(sh.Range("H1").Value) returns the folder named in H1 as a Folder object. Change H1 and the macro reads a different folder, with no code change.
• For Each f In fo.Files visits every file in that folder, one at a time.
• sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 jumps to the very last row of column A, moves up to the last cell with data and adds 1. That is the next empty row, so each file gets its own line under the headers.
• f.Name is the file name with its extension, for example Report-1.xlsx.
• f.Type is the description Windows shows in File Explorer, such as Microsoft Word Document.
• f.Size returns the size in bytes. Dividing by 1024 converts it to kilobytes, which is why the header says In KB. The workbook shows column C without decimals, so 6.04 KB displays as 6.
• f.DateLastModified is the date and time the file was last saved.

Step 5: Confirm when it is done

After Next, add MsgBox "Done" so you know the loop has finished, then close the procedure with End Sub.

The Complete VBA Code

This is the macro from the download file (Module1), the same code shown in the original post:

Option Explicit

Sub Get_Files_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(sh.Range("H1").Value)

Dim last_raw As Integer

For Each f In fo.Files
     last_raw = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
     sh.Range("A" & last_raw).Value = f.Name
     sh.Range("B" & last_raw).Value = f.Type
     sh.Range("C" & last_raw).Value = f.Size / 1024
     sh.Range("D" & last_raw).Value = f.DateLastModified
Next

MsgBox "Done"

End Sub

Part 3 – Add the Button and Run It

Step 6: Assign the macro to a shape

Back on Sheet1, insert a rounded rectangle from Insert > Shapes, type Get Information on it, right-click it, choose Assign Macro and pick Get_Files_Information.

Step 7: Test it

Paste a folder path into H1 (copy it from the File Explorer address bar), click the button and wait for Done. Every file in the folder now has a row. Save the workbook as .xlsm or .xlsb to keep the macro.

Refresh the List Without Duplicates (Addition)

This version is an addition to the video, based on general VBA practice. The original macro always appends below the last row, so clicking the button twice lists every file twice. It also uses an Integer row counter, which stops at 32,767. This version checks the folder, clears the old results first and uses a Long:

Sub Get_Files_Information_Refresh()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
Dim last_row As Long

If Not fso.FolderExists(sh.Range("H1").Value) Then
    MsgBox "Folder not found: " & sh.Range("H1").Value
    Exit Sub
End If

sh.Range("A2:D" & sh.Rows.Count).ClearContents
last_row = 1

Set fo = fso.GetFolder(sh.Range("H1").Value)

For Each f In fo.Files
    last_row = last_row + 1
    sh.Range("A" & last_row).Value = f.Name
    sh.Range("B" & last_row).Value = f.Type
    sh.Range("C" & last_row).Value = f.Size / 1024
    sh.Range("D" & last_row).Value = f.DateLastModified
Next

MsgBox fo.Files.Count & " files listed"

End Sub

Want more details? Add lines like these inside the loop and put matching headers in E1:G1:

sh.Range("E" & last_row).Value = f.Path
sh.Range("F" & last_row).Value = f.DateCreated
sh.Range("G" & last_row).Value = fso.GetExtensionName(f.Name)

f.Path gives the full path including the file name, f.DateCreated the creation date, and fso.GetExtensionName the extension without the dot (xlsx, docx). Move the folder path to another column first if you use these, because the original layout keeps it in G1:H1.

Modern Excel Alternative: Power Query (Addition)

If you only need the list and do not want macros, use Data > Get Data > From File > From Folder, pick the folder and click Transform Data. Power Query returns the name, extension, date accessed, date modified, date created and folder path of every file, including files in subfolders. Expand the Attributes column to get the size in bytes. Click Refresh whenever the folder changes.

Common Errors and Fixes

Compile error: User-defined type not defined

The Microsoft Scripting Runtime reference is missing. Tick it in Tools > References.

Run-time error 76: Path not found

The path in H1 is wrong, has a typo or points to a drive that is not connected. Copy it again from File Explorer. The refreshed version above shows a clear message instead.

The file list is duplicated

The original code never clears old rows. Delete rows 2 and below before running it again, or use the refreshed version.

File sizes show 0

Files smaller than about 0.5 KB round to 0 when column C has no decimals. Format column C with two decimals to see the real value.

Tips

• Keep the folder path in a cell instead of the code, so anyone can use the tool without opening the VBA editor.
• Turn the results into an Excel Table and sort by size or date to find the largest or oldest files.
• Use the list as the input for the next step: rename, move or convert files, as PK does in the related tutorials below.
• Microsoft’s reference for the File object lists every property you can read, such as Attributes, DateCreated and ShortName.

Want It Ready-Made?

If you manage folders and files every day, the Folder Automation Tool V1.0 in Excel on NextGenTemplates.com handles folder tasks from one sheet, and the Bulk File Creator in Excel VBA creates many files in one click. Browse all Excel VBA tools for more automation.

Related Tutorials

• Word to PDF Converter in Excel VBA
• Automation: Excel to PDF Converter in VBA
• Rename Multiple Files in One Click with VBA
• Check Folder Existence Using VBA
• Create Multiple Folders from an Excel Range

Frequently Asked Questions

What is the File System Object in VBA?

It is an object from the Microsoft Scripting Runtime library that lets VBA work with drives, folders and files: list them, read their properties, and copy, move, rename or delete them. You enable it with Tools, References, Microsoft Scripting Runtime.

How do I get a list of file names from a folder into Excel?

Put the folder path in a cell, use GetFolder to open that folder and loop through its Files collection with For Each. Write f.Name, and any other property you need, to the next empty row of the sheet. The full macro is in the Complete VBA Code section.

Why do I divide the file size by 1024?

The Size property returns the size in bytes. One kilobyte is 1024 bytes, so dividing by 1024 gives the size in KB. Divide by 1024 twice, or by 1048576, for megabytes.

Does the macro include files in subfolders?

No. fo.Files only contains the files directly inside the folder. To include subfolders you loop through fo.SubFolders as well and repeat the same steps for each one, usually with a small recursive procedure.

Why does the list repeat when I run the macro twice?

The original macro always writes below the last used row and never clears the old list. Clear A2:D first, as in the refreshed version in this tutorial, or delete the old rows by hand before you click the button.

Can I get file details without VBA?

Yes. In Excel 2016 and later, Data, Get Data, From File, From Folder loads the file names, extensions and dates with Power Query, and the Attributes column holds the size. You refresh it instead of running a macro.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel. New to macros? Start with his free step-by-step VBA course.

Conclusion

With the File System Object, getting files information into Excel takes one short loop: GetFolder opens the folder named in H1, For Each walks through its files, and each file’s name, type, size in KB and last modified date land on the next empty row. Download the workbook, point H1 at any folder and click Get Information, then use the list as the base for your own file automation.