使用FSO循环文件并检查文件名：

Option Explicit
Sub Example()
    
    Dim WB As Workbook
    Dim FSO As New FileSystemObject
    Dim oFolder As Object
    Dim oFile As Object
    
    Set FSO = CreateObject("Scripting.filesystemobject")
    Set oFolder = FSO.GetFolder("C:\Users\cameron\Documents")
    
    For Each oFile In oFolder.Files
        If oFile.Name Like "*Your Partial File Name*" Then
            
            Set WB = Workbooks.Open(oFile.Path)
            
            'Do whatever you want with your workbook.
            
            WB.Close '(Optional True/False for save changes)
            
        End If
    Next oFile
    
End Sub

这里有两个子，我可以建议。因为我不明白从你的问题，如果你想循环，打开和修改文件夹中的文件，或者你想简单地检查文件夹中的文件名，我做了两个子。一个“LoopFilesNamesInFolder”将循环检查文件名，而不打开文件，第二个“LoopAndOpenFilesInFolder”允许您打开文件并对其进行更改。您可以根据自己的需要使用其中的任何一个。如果对您有帮助，请告诉我

Public Sub LoopFilesNamesInFolder()

Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim MyFileName As String

myPath = ThisWorkbook.Path & "\"
'The file name that you are looking for
MyFileName = "*Health*"

myExtension = "*.xls*"
'Current File in loop
myFile = Dir(myPath & myExtension)

 Do While myFile <> vbNullString

   If myFile Like MyFileName Then
   'You can also use something like
   'If LCase(myFile) Like LCase(MyFileName) Then
   'If you want to make it not case sensitive
   'Your code
   Else
   'Your code
   End If

   'Get next file name
   myFile = Dir
  Loop
  
End Sub

Public Sub LoopAndOpenFilesInFolder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim MyFileName As String

'if you want to Optimize code keep the following 3 instructions
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
  
myPath = ThisWorkbook.Path & "\"
'The file name that you are looking for
MyFileName = "*Health*"

myExtension = "*.xls*"
'Current File in loop
myFile = Dir(myPath & myExtension)
 
 Do While myFile <> vbNullString

      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'You can add a DoEvents here to give time to Excel to open the workbook before moving fwd
      DoEvents
    
    'Do Whatever you want to do
   If wb.Name Like MyFileName Then
   'You can also use something like
   'If LCase(wb.Name) Like LCase(MyFileName) Then
   'If you want to make it not case sensitive
   'Your Code
   Else
   'Your Code
   End If

    'If you want to save your changes, replace the False by True to Save and Close Workbook
     wb.Close SaveChanges:=False
      
    'You can add a DoEvents here as well to give time to your Excel to close before moving to next one
      DoEvents

    'Get next file name
      myFile = Dir
  Loop
  
'Reset Settings
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub