Using Like operator to open Excel workbooks | VBA

Thank you for taking the time to read this.

I am trying to use the LIKE operator to open workbooks. Previously I went with this code which works really well

Report_Name = "\XYZ.xls"
Workbooks.open Filename:= ThisWorkbook.Path & Report_Name

My main goal is to essentially open a report and sometimes the names differ i.e healthdoc or healthassess

I tried utilizing the LIKE operator to pick up on the name of the workbook however I cannot find a way to code it.

Any direction or help is appreciated. Thank you!

I was trying to use this syntax

Dim Report_Name as Workbook
if Report_Name LIKE "*Health*" then

xyz

else

xyz

However I could only get LIKE operator working only with strings

Use FSO to loop through files and check their names:

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

Here are 2 sub that I can suggest. Since I don't understand from your question if you want to loop, open and modify the files in a folder, or you want simply to check the files names in a folder, I made 2 sub. One "LoopFilesNamesInFolder" will loop to check the file names without opening the files, and second one "LoopAndOpenFilesInFolder" allows you to open and make changes if you want to the files. You can use any of them based on your needs. Let me know if it helps you

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