Open file with vba based on file name

[Som]

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!

[DocAElstein]

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


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

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 ,
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

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

[Dave]

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

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

[DocAElstein]

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/dotnet/visual-basic/language-reference/operators/like-operator )
A useful thing, maybe like a poor man’s equivalent of more complicated WildCard Reginald Expressions & co.

Alan