如何用固定文本加通配符精准匹配目标Excel文件？

The issue here is that your current wildcard pattern *Panel countries*.xlsx uses a leading asterisk, which matches any characters before the substring "Panel countries". That's why "Non-Panel Countries_15.05.xlsx" gets picked up—it contains the substring "Panel countries" later in the filename.

Here are two reliable ways to fix this:

1. Use a Prefix-Exact Wildcard Pattern #

Since your target files start with the exact string "Panel Countries" (followed by the date suffix), remove the leading asterisk from your pattern. This ensures only files that begin with that exact prefix are matched.

Corrected code:

Dim directory As String, panelFileName As String, nonPanelFileName As String

directory = "C:\Users\calin.lencar\Desktop\Feasibility & Capacity\Raw Data\"

' Match files starting with "Panel Countries"
panelFileName = Dir(directory & "Panel Countries*.xlsx")

' Match files starting with "Non-Panel Countries"
nonPanelFileName = Dir(directory & "Non-Panel Countries*.xlsx")

This works because:

• Panel Countries*.xlsx only matches files where the name starts with "Panel Countries" followed by any characters (like your date suffix _15.05).
• "Non-Panel Countries..." won't be matched here, as it starts with "Non-" instead.

2. Explicitly Filter Unwanted Matches (Fallback) #

If you need to account for edge cases where filenames might not strictly start with your target prefix, you can loop through all potential matches and exclude the unwanted ones using the Like operator:

Dim directory As String, fileName As String

directory = "C:\Users\calin.lencar\Desktop\Feasibility & Capacity\Raw Data\"

' Find Panel Countries files (exclude any starting with "Non-")
fileName = Dir(directory & "*Panel Countries*.xlsx")
Do While fileName <> ""
    If Not fileName Like "Non-*" Then
        ' This is your target Panel file
        Debug.Print "Found Panel file: " & fileName
        Exit Do ' Exit loop once we find the first match
    End If
    fileName = Dir ' Get next matching file
Loop

' Find Non-Panel Countries files
fileName = Dir(directory & "Non-Panel Countries*.xlsx")
If fileName <> "" Then
    Debug.Print "Found Non-Panel file: " & fileName
End If

This approach adds an extra check to skip any files that start with "Non-" when searching for Panel files, ensuring you get the correct match every time.

Also, note that your original code had duplicate directory assignments—you only need to set it once!

内容的提问来源于stack exchange，提问作者Calin Lencar