Open file with vba based on file name

Hi everyone!
I realy need some help on this topic.
I know how tó open a file based on a name.
But now I ám strugling to manage the follow ing.


I would like to open a file, when the filename includes either "cp4" or "gt4"

Then I have tó open a file, if the filename doesnt include "ghl"

I could realy use some help and I would realy apprechiate it.

Thanks in advance!

Quote:

---

Originally Posted by Som

Open file with vba based on file name

….I know how to open a file based on a name …

---

Quote:

---

Originally Posted by Som

I would like to open a file, when the filename includes either "cp4" or "gt4"
Then I have to open a file, if the filename doesn’t include "ghl"

---

Hi, Perhaps a better title would have been something like, Open file if the file name contains certain text.
The basic problem is perhaps doing something or not based on certain text in a file name.

Here a few ideas
_1 select a string using the Like-Operator

Code:

---

Sub SomNames()  '    www.vbaexpress.com/forum/showthread.php?70999-Open-file-with-vba-based-on-file-name
Dim arrNms() As Variant
 Let arrNms() = Array("abcd.xls", "abcp4cd.xlsm", "abgt4cd.xlsx", "abghlcd.xls", "efgh.xls")
Dim SomName As Variant
    For Each SomName In arrNms()
        Select Case True
        Case SomName Like "*cp4*"      '  https://learn.microsoft.com/de-de/dotnet/visual-basic/language-reference/operators/like-operator
          MsgBox prompt:="Name  """ & SomName & """  got  ""cp4""  in it"
        Case SomName Like "*gt4*"
          MsgBox prompt:="Name  """ & SomName & """  got  ""gt4""  in it"
        End Select
    Next SomName
    For Each SomName In arrNms()
        Select Case False
        Case SomName Like "*ghl*"
          MsgBox prompt:="Name  """ & SomName & """  not got  ""ghl""  in it"
        End Select
    Next SomName
End Sub

---

_2 Use wild cards in the typical Do Dir Loop coding
If you are familiar with a commonly used way to get at files in a folder, a Do Dir Loop type coding, then getting at files in a folder knowing only part of the file name is inherent to it
Example
Say you have a folder like this: https://i.postimg.cc/T2cyT3YK/Example-Folder.jpg ,
Attachment 31107
, and say you only want to get at Excel files meeting those requirements of yours. Then this coding, in for example that file named LikeWildCards.xls, should do something close to what you need

Code:

---

Sub OpenExcelfileiffilenamecontainscertaintext()    '    '    www.vbaexpress.com/forum/showthread.php?70999-Open-file-with-vba-based-on-file-name        www.vbaexpress.com/forum/showthread.php?70999-Open-file-with-vba-based-on-file-name&p=421362#post421362
Dim SomName As String
' get at files with  cp4  in the file name
 Let SomName = Dir(pathname:=ThisWorkbook.Path & "\" & "*cp4*.xls*", Attributes:=vbNormal)  '  VBA is looking for an Excel file ( .xls* ) at that path with  cp4  in its name
    Do While SomName <> ""
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & SomName
    Application.Wait Time:=(Now + TimeValue("0:00:07"))                                    '  Wait 7 seconds , just for demo purposes so that you can see the file is open before its closed
    Workbooks("" & SomName & "").Close
    Let SomName = Dir                                                                      '    If you use  Dir  like this, then VBA tries to find the next file meeting the same search criteria that was last used
    Loop
' get at files with  gt4  in the file name
 Let SomName = Dir(pathname:=ThisWorkbook.Path & "\" & "*gt4*.xls*", Attributes:=vbNormal)  '  VBA is looking for an Excel file ( .xls* ) at that path with  gt4  in its name
    Do While SomName <> ""
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & SomName
    Application.Wait Time:=(Now + TimeValue("0:00:07"))                                    '  Wait 7 seconds , just for demo purposes so that you can see the file is open before its closed
    Workbooks("" & SomName & "").Close
    Let SomName = Dir                                                                      '    If you use  Dir  like this, then VBA tries to find the next file meeting the same search criteria that was last used
    Loop
' get at files without  ghl  in the file name  (but not the file with this coding in it)
 Let SomName = Dir(pathname:=ThisWorkbook.Path & "\" & "*.xls*", Attributes:=vbNormal)      '  VBA is looking for an Excel file ( .xls* ) at that path
    Do While SomName <> ""
    If InStr(1, SomName, "ghl", vbBinaryCompare) = 0 And InStr(1, SomName, ThisWorkbook.Name, vbBinaryCompare) = 0 Then
        Workbooks.Open Filename:=ThisWorkbook.Path & "\" & SomName
        Application.Wait Time:=(Now + TimeValue("0:00:03"))                                    '  Wait 3 seconds , just for demo purposes so that you can see the file is open before its closed
        Workbooks("" & SomName & "").Close
    Else
    ' case there is  ghl  in file name
    End If
    Let SomName = Dir                                                                      '    If you use  Dir  like this, then VBA tries to find the next file meeting the same search criteria that was last used
    Loop
End Sub

---

Alan

Hi Som. DocAElstein has already provided a superb response to your request which includes alternate solutions. For the sake of completeness and because of my disdain for the use of the Dir function, I would like to offer the following which uses the file system object to search the directory and the Instr function to select the needed files. HTH. Dave

Code:

---

Private Sub Test()
Dim objFSO As Object, PathFolder As String
Dim objFolder As Object, objFile As Object
'****adjust YourFolderName to suit path
PathFolder = "C:\YourFolderName\"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(PathFolder)
'loop all files in folder
For Each objFile In objFolder.Files
'access only XL files
If objFile.Name Like "*" & ".xls" & "*" Then
'open only file names with "cp4" or "gt4" and not "ghl"
If InStr(objFile.Name, "cp4") Or InStr(objFile.Name, "gt4") And _
Not InStr(objFile.Name, "ghl") Then
Workbooks.Open Filename:=objFile
'do stuff
Workbooks(objFile.Name).Close savechanges:=True
End If
End If
Next objFile
Set objFolder = Nothing
Set objFSO = Nothing
End Sub

---

Hello Dave
It’s good to see alternatives in Threads, JMHO
I don’t have enough real life programming experience myself to comment on the merits of either way. The Dir way seems to crop up a lot in forum answers, that’s where I picked it up from. It does the simple job for a folder, but I think the file system object is probably better for more complicated things like sub Folders and things recursion.
(Before I went off into a technology coma for almost 30 years, I think I used the Dir to find my way around as I recall, in the early days of computers.)

(One small thing: I read the request as doing for "cp4" or "gt4" and then for the Not "ghl". But I am not sure, and it doesn’t really matter, I think there is enough here for the OP to adapt for exactly what he wants.) My guess was that perhaps a key thing the OP was missing was something like the Visual Basic Like operator, which we both used.
( https://learn.microsoft.com/en-us/do.../like-operator )
A useful thing, maybe like a poor man’s equivalent of more complicated WildCard Reginald Expressions & co.

Alan