How to Automatically Open Files Using Wildcards in Excel VBA: Complete Guide for Smarter File Automation

Automating file operations in Excel VBA often requires handling files whose names change daily, weekly, or monthly. For example, a report might include today’s date, a timestamp, or a version number. In such cases, it is not practical to hard-code the file name every time. Instead, VBA allows you to use wildcards, enabling your macro to automatically find and open the correct file based on a pattern.

Wildcards such as * and ? allow you to search for files when only part of the name is known — a powerful technique frequently used in business automation, data consolidation, and RPA workflows with UiPath or Power Automate. This article explains how to use wildcards in VBA to locate and automatically open files, complete with practical examples and best practices.

---

✅ Understanding How to Use Wildcards to Open Files Automatically in VBA

・What wildcards are

Wildcards allow flexible pattern matching:

• * matches any number of characters
• ? matches a single character

・Where wildcards can be used in VBA

VBA wildcards work in:

• Dir function
• File searches
• Folder scanning loops
• Dynamic file-path generation

Wildcards cannot be used directly in Workbooks.Open, so Dir is used to identify matching files first.

・Why use wildcards in automation

Wildcards allow VBA to open files such as:

• Sales_2024_01.xlsx
• Sales_2024_02.xlsx
• Sales_2024_03.xlsx

…using only:

Sales_*.xlsx

This is invaluable in:

• Monthly reporting
• Version-based file naming
• Timestamped files
• Daily data imports
• RPA workflows with unpredictable file names

---

✅ How to Use the Dir Function to Find Files with Wildcards

・Basic syntax of Dir (Syntax: Dir with wildcard)

Dir("C:\Folder\Sales_*.xlsx")

This returns the first file that matches the pattern.

・Getting all matching files

Call Dir repeatedly:

fileName = Dir("C:\Folder\Sales_*.xlsx")
Do While fileName <> ""
' Process the file here
fileName = Dir
Loop

・Checking if no file matches

If Dir("C:\Folder\Sales_*.xlsx") = "" Then
MsgBox "No matching files found."
End If

---

✅ Automatically Opening the File Found Using a Wildcard

You must combine Dir with Workbooks.Open.

・Example: Open the first matching file

Sub OpenWildcardFile()
Dim fp As String
fp = Dir("C:\Data\Report_*.xlsx")
If fp = "" Then
MsgBox "File not found."
Exit Sub
End If
Workbooks.Open "C:\Data\" & fp
End Sub

・Example: Open the latest file using a pattern

Often multiple files match the pattern.
In this situation, you may want the most recent one.

Sub OpenLatestWildcardFile()
Dim folder As String
Dim file As String
Dim latestFile As String
Dim latestDate As Date
folder = "C:\Data\"
file = Dir(folder & "Report_*.xlsx")
Do While file <> ""
If FileDateTime(folder & file) > latestDate Then
latestDate = FileDateTime(folder & file)
latestFile = file
End If
file = Dir
Loop
If latestFile <> "" Then
Workbooks.Open folder & latestFile
Else
MsgBox "No matching files found."
End If
End Sub

This is perfect for timestamped logs or update files.

---

✅ Using Wildcards in Real-World Automation Scenarios

---

・Example 1: Open the newest daily export

Sub OpenNewestExport()
Dim f As String, newest As String
Dim path As String
Dim newestTime As Date
path = "C:\Exports\"
f = Dir(path & "Export_*.csv")
Do While f <> ""
If FileDateTime(path & f) > newestTime Then
newest = f
newestTime = FileDateTime(path & f)
End If
f = Dir
Loop
If newest <> "" Then
Workbooks.Open path & newest
End If
End Sub

---

・Example 2: Open every file that matches a wildcard

Sub OpenAllMatchingFiles()
Dim f As String
Dim p As String
p = "C:\Reports\"
f = Dir(p & "Sales_*.xlsx")
Do While f <> ""
Workbooks.Open p & f
f = Dir
Loop
End Sub

Useful for consolidating data.

---

・Example 3: Open the only file in a folder when its name changes

Sub OpenSingleUnknownFile()
Dim f As String
f = Dir("C:\Input\*.xlsx")
If f <> "" Then Workbooks.Open "C:\Input\" & f
End Sub

This is common in scheduled data-drops.

---

・Example 4: Using wildcards to open versioned files

Report_v1.xlsx  
Report_v2.xlsx  
Report_v3.xlsx

Pattern:

Dir("C:\Reports\Report_v*.xlsx")

---

・Example 5: Opening files for UiPath preprocessing

Sub CleanForUiPath()
Dim f As String
f = Dir("C:\RPA\Input_*.xlsx")
If f = "" Then Exit Sub
Dim wb As Workbook
Set wb = Workbooks.Open("C:\RPA\" & f)
wb.Sheets(1).UsedRange.ClearFormats
wb.Close SaveChanges:=True
End Sub

Wildcards ensure UiPath robots always get the correct file.

---

✅ Step-by-Step Guide: Building a Flexible Wildcard-Based File Loader

・Step 1: Define the folder path

Always include the trailing backslash.

・Step 2: Create a wildcard pattern

Example:

"Report_*.xlsx"

・Step 3: Use Dir to identify files

file = Dir(path & pattern)

・Step 4: Check for errors or absence of files

If file = "" Then Exit Sub

・Step 5: Open the file

Workbooks.Open path & file

・Step 6: Add optional processing

Copy, clean, transform, or analyze the file.

・Step 7: Close files when needed

Always control save behavior.

---

✅ Advanced Wildcard Techniques for Power Users

・Wildcard in folder name

Using Dir twice allows wildcard folders:

f = Dir("C:\Archive\2024*\Report_*.xlsx")

・Dynamic patterns with variables

pattern = "Sales_" & Year(Date) & "*.xlsx"
file = Dir(folder & pattern)

・Combining multiple wildcards

Dir("C:\Data\*_Sales_*.xlsx")

・Finding files matching date patterns

Dir("C:\Logs\*" & Format(Date, "yyyymmdd") & "*.txt")

・Matching partial extensions

Dir("*.xl*")

---

✅ Error Handling When Opening Files with Wildcards

・Avoiding “Subscript out of range”

Use existence checks:

If file = "" Then Exit Sub

・Handling read-only or locked files

On Error Resume Next
Set wb = Workbooks.Open(path & file, ReadOnly:=True)
On Error GoTo 0

・Handling corrupted files

On Error GoTo Failed
Workbooks.Open path & file
Exit Sub
Failed:
MsgBox "File could not be opened."

・Handling network latency

For shared drives, implement retry logic if needed.

---

✅ Best Practices for Wildcard-Based File Automation

・Use full paths

Relative paths cause confusion in complex automation.

・Avoid hard-coded dates

Use wildcards instead of embedding dates in filenames.

・Always test for Cancel or missing files

Prevents runtime errors.

・Use FileDateTime to pick the best file

Ideal for timestamped exports.

・Clean up after opening

Close workbooks not needed.

・Consider UiPath coordination

RPA robots prefer clean, deterministic file access.

・Avoid infinite loops with Dir

Always use Do While fp <> “” structure.

---

✅ Real Business Use Cases for Wildcard File Opening

・Monthly report automation

Pattern:

Sales_*.xlsx

・Daily log imports

Pattern:

Log_*.txt

・Version-controlled worksheets

Budget_v*.xlsx

・Automated RPA workflows

UiPath processes the newest file found with a wildcard.

・Data consolidation systems

Macros loop through all files matching a pattern.

・Financial forecasting files

Forecast_2024_Q*.xlsx

・Product master updates

Master_*.csv

---

✅ Summary：Using Wildcards in VBA Enables Flexible, Intelligent File Automation

• Use Dir to search for files with * and ? wildcards.
• Combine wildcards with Workbooks.Open to automatically open matching files.
• Use file timestamps to locate the newest or most relevant file.
• Loop through all matching files for consolidation tasks.
• Handle errors and missing files safely.
• Apply dynamic patterns for date-based or version-based filenames.
• Use wildcard-based file selection for UiPath and RPA workflows.
• Build flexible automations that adapt to real-world file naming variability.

Mastering wildcard file automation makes your VBA tools more powerful, more adaptive, and more capable of handling real business environments where filenames change constantly. This technique is essential for modern Excel automation and integrates perfectly with enterprise-level workflows.