VBA如何打开指定文件夹下未知文件名的CSV文件？

Got it, let's break this down together! The core issue here is that VBA's standard Workbooks.Open method doesn't support wildcard patterns like *.csv directly—it needs an exact, full filename to work. That's why your hardcoded filename works perfectly, but the wildcard throws that frustrating error.

Here are two straightforward solutions depending on what you need:

Solution 1: Open the First CSV File in the Folder #

Use the Dir function to locate the first CSV file matching the pattern, then pass that exact filename to Workbooks.Open:

Sub OpenFirstCSV()
    Dim targetFolder As String
    Dim firstCSV As String
    
    ' Set your folder path (make sure to include the trailing backslash!)
    targetFolder = "C:\Your\Folder\Path\"
    
    ' Find the first CSV in the folder
    firstCSV = Dir(targetFolder & "*.csv")
    
    ' Check if we found a file before trying to open it
    If firstCSV <> "" Then
        Workbooks.Open Filename:=targetFolder & firstCSV
    Else
        MsgBox "Oops, no CSV files found in that folder!"
    End If
End Sub

Key Notes: #

• Always add a trailing backslash (\) to your folder path—without it, VBA will combine the path and wildcard incorrectly (like C:\Folder*.csv instead of C:\Folder\*.csv).
• Dir returns files in the system's default sort order (usually alphabetical).

Solution 2: Open All CSV Files in the Folder #

If you need to process every CSV in the folder, extend the Dir logic with a loop:

Sub OpenAllCSVs()
    Dim targetFolder As String
    Dim currentCSV As String
    
    targetFolder = "C:\Your\Folder\Path\"
    currentCSV = Dir(targetFolder & "*.csv")
    
    ' Loop through all matching CSV files
    Do While currentCSV <> ""
        Workbooks.Open Filename:=targetFolder & currentCSV
        currentCSV = Dir() ' Grab the next CSV file
    Loop
    
    ' Optional: Alert if no files were found
    If Dir(targetFolder & "*.csv") = "" Then
        MsgBox "No CSV files found in the specified folder."
    End If
End Sub

Extra Tips: #

• If you need to sort the CSV files before opening them, collect all filenames into an array first, sort the array, then loop through it to open files.
• Ensure your Excel has permission to access the target folder—if it's a restricted directory, you might get a permission error instead of the filename one.

内容的提问来源于stack exchange，提问作者Marc-Andri Etterlin