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

Contents

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

Opening files from a folder is one of the most essential skills in Excel VBA automation. In real business operations, file names often change, locations vary, and users may not know exactly where the file is stored. To handle these scenarios safely and efficiently, VBA offers several powerful methods to specify a folder, search through its contents, and open files automatically—even when only part of the file name is known.

This guide explains how to:

Use a folder selection dialog
Open all files in a folder
Open files that match part of a filename
Combine folder selection + wildcard filtering
Build advanced automation suitable for UiPath/RPA pipelines

By mastering these techniques, you will create VBA tools that are flexible, user-friendly, and capable of handling dynamic business workflows.

✅ Understanding How to Select a Folder and Open Files in VBA

・Why folder-based file opening matters

Real business environments often include:

Daily or monthly reports inside the same folder
CSV files exported automatically to a single location
Financial files stored by department
Shared-drive directories with unpredictable file names
RPA systems that drop output files into a fixed folder

In these cases, relying on a single hard-coded file path is not practical. Instead, VBA needs to:

Accept a folder selected by the user
Scan all files inside
Optionally filter by extension or partial filename
Open the correct file automatically
Handle errors if the file is missing or locked

Folder-based automation is essential for scalable, maintainable VBA systems.

✅ Selecting a Folder Using a Dialog (Folder Picker)

・Why use a folder picker dialog?

A dialog is the safest method when:

Each user stores files in different locations
Folder paths change frequently
You want beginner-friendly automation
Security policies prevent hard-coded paths

・Basic folder picker using FileDialog (Syntax: Folder Picker)

Dim fd As FileDialog
Dim folderPath As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show = -1 Then
folderPath = fd.SelectedItems(1) & "\"
Else
MsgBox "Operation cancelled."
Exit Sub
End If

The selected folder path always ends with a backslash for convenience.

✅ Open All Files Inside a Selected Folder

・Loop through every file using Dir

Sub OpenAllFilesInFolder()
Dim fd As FileDialog
Dim folderPath As String
Dim f As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show <> -1 Then Exit Sub
folderPath = fd.SelectedItems(1) & "\"
f = Dir(folderPath & "*.*")
Do While f <> ""
Workbooks.Open folderPath & f
ActiveWorkbook.Close False
f = Dir
Loop
End Sub

・When to use this

Consolidation workflows
Log file processing
Folder cleanup tools
Importing multiple exports from accounting systems
RPA pipelines that require batch-processing

This is one of the most powerful folder-based automation patterns.

✅ Open Files That Match Part of a Filename

・Use wildcards inside the selected folder

Sub OpenFilesMatchingKeyword()
Dim fd As FileDialog
Dim folderPath As String
Dim f As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show <> -1 Then Exit Sub
folderPath = fd.SelectedItems(1) & "\"
f = Dir(folderPath & "*Sales*.xlsx")
Do While f <> ""
Workbooks.Open folderPath & f
ActiveWorkbook.Close False
f = Dir
Loop
End Sub

・Keywords commonly used

“Sales”
“Report”
“2024”
“Final”
“Data”
“Export”

Works perfectly when file names change but contain consistent patterns.

✅ Open the Newest File in a Folder Matching a Pattern

・Example: open the latest `Report_*.xlsx`

Sub OpenLatestMatchingFile()
Dim fd As FileDialog
Dim folderPath As String
Dim f As String
Dim latest As String
Dim latestDate As Date
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show <> -1 Then Exit Sub
folderPath = fd.SelectedItems(1) & "\"
f = Dir(folderPath & "Report_*.xlsx")
Do While f <> ""
If FileDateTime(folderPath & f) > latestDate Then
latest = f
latestDate = FileDateTime(folderPath & f)
End If
f = Dir
Loop
If latest <> "" Then
Workbooks.Open folderPath & latest
Else
MsgBox "No matching file found."
End If
End Sub

・Why this matters

Automated systems (ERP, CRM, POS, RPA) often generate timestamped files:

Report_20240101.xlsx
Report_20240102.xlsx
Report_20240103.xlsx

Selecting the newest file saves users from mistakes.

✅ Open Specific File Types Inside a Folder

・Open only Excel files

f = Dir(folderPath & "*.xlsx")

・Open only CSV files

f = Dir(folderPath & "*.csv")

・Open only text files

f = Dir(folderPath & "*.txt")

・Combined pattern

f = Dir(folderPath & "*.xlsx")
f = Dir(folderPath & "*2024*.csv")

Filtering by extension improves automation accuracy and prevents errors.

✅ Step-by-Step: Folder Selection + Partial Filename + Opening Files

・Step 1: User selects a folder

folderPath = "C:\Reports\"

(or using folder picker)

・Step 2: Search for files

f = Dir(folderPath & "*Sales*.xlsx")

・Step 3: Loop through matching files

Do While f <> ""

・Step 4: Open and process

Workbooks.Open folderPath & f

・Step 5: Close file safely

ActiveWorkbook.Close False

・Step 6: Move to next result

f = Dir

This structure works for any folder automation task.

✅ Advanced Techniques for Folder-Based Automation

・Restrict search to subfolders (manual recursion)

If GetAttr(path) And vbDirectory Then

Useful for deep directory systems.

・Check if folder exists

If Dir(folderPath, vbDirectory) = "" Then

・Ignore temporary files

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

・Sort files by date before opening

Create an array → sort → open.

・Combine multiple filters

If InStr(f, "2024") > 0 And Right(f, 5) = ".xlsx" Then

・Use read-only mode for shared drive files

Workbooks.Open folderPath & f, ReadOnly:=True

Helps avoid accidental overwriting.

✅ Practical Real-World Examples

・Example 1: Import all CSV exports from a folder

f = Dir(folderPath & "*.csv")

・Example 2: Open the latest file from a customer system

FileDateTime(...) logic

・Example 3: Search for files containing specific codes

Dir(folderPath & "*A102*.xlsx")

・Example 4: Consolidation script for 12 monthly reports

Dir(folderPath & "Report_*.xlsx")

・Example 5: Preprocessing for UiPath robots

Prepare workbook before handing it off to RPA.

✅ Error Handling When Opening Files

・File not found

If f = "" Then MsgBox "No files found."

・Folder not selected

Handled via dialog cancellation.

・File in use

On Error GoTo Locked
Workbooks.Open folderPath & f

・Invalid format

Check extension:

If Right(f, 5) = ".xlsx" Then

・Network delays

Use DoEvents to prevent UI freezing.

✅ Best Practices for Folder-Based File Automation

・Always validate folder selection

Avoid missing-folder errors.

・Always validate file existence

Prevent runtime failures.

・Use filters to avoid opening unintended files

Ensures data consistency.

・Use read-only mode in shared environments

Protects master files.

・Integrate with RPA workflows

VBA can preprocess before UiPath takes over.

・Avoid ActiveWorkbook dependence

Use variables instead.

・Log which files were opened

Helps with auditing and debugging.

✅ Business Use Cases for Folder-Based File Opening

Importing daily sales or inventory data
Processing whole-month files at once
Loading accounting exports dropped by other departments
Automatically syncing RPA-generated files
Scanning shared drives for new uploads
Opening versioned files with partial names
Building enterprise reporting pipelines

Folder-level automation is one of the most universally useful VBA skills.

✅ Summary：Mastering Folder-Based File Opening Makes Your VBA Tools Flexible, Safe, and Enterprise-Ready

Use Folder Picker dialogs to let users choose the folder.
Use Dir with wildcards to search for files inside the folder.
Open all files, specific file types, or files matching part of a name.
Open the newest file using FileDateTime.
Loop through contents for consolidation and batch processing.
Use error handling for missing or locked files.
Use read-only mode for shared resources.
Combine VBA automation with UiPath/RPA for powerful hybrid workflows.

By mastering these folder-based techniques, you can create advanced import tools, high-performance automation scripts, and flexible data-processing systems used across business operations.