Originally Posted by
Som
Open file with vba based on file name
….I know how to open a file based on a name …
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
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 ,
Example Folder.JPG
, 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