open file with wildcard or partial file name

[sheppy72]

I am trying to try and get my macro to work but struggling. Any help please.

in the same dir as where my sheet with the code is I save a files. They have different names and I want to be able to open the correct file to work on. The file names can be long but they tend to have some short easy to remember model codes.
I would like therefore to be able to just type in *x123* in to the inputbox and this would open the file test_macro_x123_test.xls, or if i type L888 it would open the file test_macro_L888_test.xls.

Dim filename1 As String
filename1 = InputBox("enter model code")
Workbooks.Open filename:=ThisWorkbook.Path & "\" & filename1 & ".xls"

 

[Firefly2012]

Rather than that, how about the user physically locates the file to use via the same dialog interface you would use to open a file in Excel via File>Open?

 

[sheppy72]

The sheet that the code is on is a sheet that has several macros that then need to be run. The workbook that the input box refers to is the sheet with the data on and many many sheets. All these sheets are used and called for in my code currently.

 

[Firefly2012]

OK you could use Dir for this eg:

Dim filename1 As String, strFile As String
Dim wb As Workbook


filename1 = InputBox("enter model code")

'find matching file in directory:
strFile = Dir(ThisWorkbook.Path & "\*" & filename1 & "*.xls")

'check filename found:
If strFile<>"" Then   'if it isn't "" then strFile holds the name (not path) of matching file
   Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & strFile)
Else
  MsgBox "No matching file found! Exiting Sub..."
  Exit Sub
End If

'you now have the workbook open and a reference set to it so you can do stuff with it eg:

wb.Sheets(1).Activate