How to Retrieve File Names from a Folder and Export Them into Excel Using VBA

Contents

How to Retrieve File Names from a Folder and Export Them into Excel Using VBA

Extracting file names from a folder and listing them neatly in Excel is a common task in business automation. Whether you are managing documents, tracking reports, filtering images, or preparing files for RPA pipelines, having an automated way to retrieve file names saves significant time and eliminates manual errors. Excel VBA provides powerful methods to scan folders, retrieve file names in order, and write them directly into Excel cells.

This article will guide you through the complete process of:

Retrieving file names from a folder
Sorting file names or extracting in the natural order
Filtering by extension
Handling subfolders
Writing results directly into Excel
Using folder-picker dialogs for user-friendly input
Creating automation suitable for UiPath and other RPA tools

Mastering this technique will help you build clean, flexible, and professional automation solutions.

✅ Understanding How to Retrieve File Names and Export Them into Excel

・Why retrieve file names automatically?

Automating file name retrieval is essential in scenarios like:

Creating an index of files in a folder
Monitoring new files received daily
Preparing lists for RPA robots
Validating whether expected reports exist
Processing multiple files in batch operations
Producing audit logs for documentation
Extracting file metadata for analysis

Instead of manually listing filenames, VBA can do it instantly and accurately.

・Common types of files processed

Excel files
CSV and text files
PDFs
Images
Log files
Automation outputs
ERP/CRM exports

VBA can retrieve any file type, making it suitable for general-use automation.

✅ Retrieving File Names Using the Dir Function

・How the Dir function works

Dir is the simplest and most efficient method for scanning files:

Returns the first file that matches a pattern
Uses wildcard filters
Supports loops to retrieve all files
Works with all file types
Lightweight and fast

・Basic syntax (Syntax: Dir)

fileName = Dir("C:\Folder\*.*")

Returns the first file name.

・Get the next file

fileName = Dir()

This continues until no more files are found.

✅ Exporting File Names to Excel (Basic Example)

・Write file names starting at cell A1

Sub ListFiles()
Dim folder As String
Dim file As String
Dim r As Long
folder = "C:\Data\"
r = 1
file = Dir(folder & "*.*")
Do While file <> ""
Cells(r, 1).Value = file
r = r + 1
file = Dir
Loop
End Sub

This script lists all file names in order, one per row.

✅ Using a Folder Picker Dialog to Let Users Choose the Folder

・Folder Picker (user-friendly approach)

Sub ListFilesWithDialog()
Dim fd As FileDialog
Dim folder As String
Dim file As String
Dim r As Long
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show <> -1 Then Exit Sub
folder = fd.SelectedItems(1) & "\"
r = 1
file = Dir(folder & "*.*")
Do While file <> ""
Cells(r, 1).Value = file
r = r + 1
file = Dir
Loop
End Sub

・Benefits of using a dialog

Beginners can easily select folders
Eliminates hard-coded paths
More flexible for shared environments
Helps when users store files in different locations

Dialogs improve the usability of your macro tool significantly.

How to Open Files from a Selected Folder in Excel VBA: Dialog Selection, Full-Scan Loading, and Partial Filename Matching

✅ Retrieving File Names in Alphabetical or Natural Order

The Dir function retrieves file names in the order provided by the file system—usually alphabetically, but not guaranteed.

To guarantee sorting, you must store file names in a collection or array and sort them.

・Step-by-step: Retrieve → Store → Sort → Write

Sub ListSortedFiles()
Dim folder As String
Dim file As String
Dim list As Object
Dim i As Long
Set list = CreateObject("System.Collections.ArrayList")
folder = "C:\Data\"
file = Dir(folder & "*.*")
Do While file <> ""
list.Add file
file = Dir
Loop
list.Sort
For i = 0 To list.Count - 1
Cells(i + 1, 1).Value = list(i)
Next i
End Sub

・Sorting is useful when

Files have timestamps
Naming includes serial numbers
Preparing audit logs
Ensuring predictable output for RPA robots

Sorting ensures clean and professional results.

✅ Filtering File Names by Extension or Keyword

・Only Excel files

file = Dir(folder & "*.xlsx")

・Only CSV files

file = Dir(folder & "*.csv")

・Files containing keywords

file = Dir(folder & "*Sales*.xlsx")

・Multiple filters (manual conditions)

If InStr(file, "2024") > 0 Then

Filtering makes your script more accurate and reduces noise in large folders.

✅ Retrieving Full File Paths Instead of Just File Names

To export full paths:

Cells(r, 1).Value = folder & file

Full paths are essential when:

Used as input for UiPath robots
You need to open the files later
You want traceability for audits
You’re building a file-processing pipeline

How to Open Text Files in Excel VBA: Complete Guide to Reading, Loading, and Processing Text Data

✅ Writing File Names into Multiple Columns or Sheets

・Write names and file sizes

Cells(r, 1).Value = file
Cells(r, 2).Value = FileLen(folder & file)

・Write file name, type, and modification date

Cells(r, 1).Value = file
Cells(r, 2).Value = FileDateTime(folder & file)
Cells(r, 3).Value = FileLen(folder & file)

・Write into a specific sheet

Worksheets("FileList").Cells(r, 1).Value = file

Multiple-column exports help build rich file inventory reports.

✅ Step-by-Step Workflow: Retrieving and Exporting File Names

・Step 1 — Choose the folder

Using dialog or hard-coded.

・Step 2 — Clear old data

Cells.Clear

・Step 3 — Retrieve file names

Using Dir.

・Step 4 — Optional sorting

Store and sort using ArrayList.

・Step 5 — Export to Excel

Write into rows, columns, or tables.

・Step 6 — Optional save or processing

Perfect for UiPath or data validation workflows.

✅ Advanced Techniques for Folder File Extraction

・Loop through subfolders (recursive search)

If GetAttr(path) And vbDirectory Then

・Only include non-temporary files

If InStr(file, "~") = 0 Then

・Track the newest file

If FileDateTime(folder & file) > latestDate Then

・Track largest file

If FileLen(folder & file) > maxSize Then

・Export into a structured Excel Table

✅ Practical Real-World Examples

・Example 1: List all files for a monthly report

Useful for accounting or finance teams.

・Example 2: List RPA output files

UiPath often drops output files, and you may need to validate them.

・Example 3: Validate if required files exist

Compare expected file list vs folder contents.

・Example 4: Monitor a shared network folder

Extract newest uploads from multiple departments.

・Example 5: Build a file index for archiving

Create a structured list before moving or cleaning files.

✅ Error Handling During File Name Extraction

・Folder does not exist

If Dir(folder, vbDirectory) = "" Then

・No matching files

Display a user-friendly message.

・Permission denied

Occurs in restricted corporate folders.

・Names too long for cells

Use Left(file, 255) if needed.

・Network delays

DoEvents helps keep UI stable.

✅ Best Practices for File Listing Automation

・Always clear existing data

Avoid mixing old and new lists.

・Use full paths when needed

Essential for downstream processing.

・Provide folder selection dialogs

Improves usability.

・Use sorting for consistency

Better for automation and auditing.

・Filter unnecessary files

Keeps output clean and focused.

・Add timestamps when storing file lists

Useful for audit trails.

・Use Arrays or Collections for large folders

Enhances performance.

✅ Business Use Cases for Listing File Names with VBA

Creating file inventories
Preparing input lists for Power Automate or UiPath
Checking completeness of reports
Managing folder-based workflows
Extracting metadata for analysis
Monitoring shared network folders
Batch-processing multiple document types
Logging file changes over time

This technique is widely used across departments including finance, manufacturing, HR, logistics, and IT.

✅ Summary：Retrieving File Names and Exporting Them to Excel Is One of the Most Valuable VBA Automation Skills

Use Dir to retrieve file names quickly and efficiently.
Use folder picker dialogs for user-friendly folder selection.
Export file names into Excel rows, multiple columns, or structured tables.
Filter by extension or keywords for precision.
Sort file names for predictable results.
Include full paths when preparing data for RPA tools.
Loop through subfolders for advanced automation.
Apply error handling to keep workflows stable.

By mastering folder scanning and file export techniques, your VBA solutions will become more powerful, flexible, and valuable for business operations and automation environments.

VBA File & Print Operations: The Complete Guide to Handling Files, Folders, Saving, Exporting, and Printing in Excel VBA