Get a part of a filename

[Peter Pantus]

Hi All

I'am trying to get a part of a filename
I don't know how to do this - must I work with jokers!!
Example: Filename is "Test 07-11-03.xls"
I want, whitin VBA, only type te word "Test" and then the result must be
that the file "Test 07-11-03.xls" opens

Thanks for any answer
Peter P

 

[Bernie Deitrick]

Peter,

And if there is a file "Test 07-11-03.xls" AND the file "Test
07-12-03.xls", what do you want to do? Open them both?

Bernie
MS Excel MVP

 

[Peter Pantus]

Sorry Bernie I post the response to the wrong box

There is only one file that begins with "Test"
The rest of the name can be a date or anything else

 

[Bernie Deitrick]

Peter,

In that case, try something like:

Dim myFName As String
myFName = Application.InputBox("Enter the first four letters...")

With Application.FileSearch
.NewSearch
.LookIn = "C:\Excel\DeleteThese"
.SearchSubFolders = False
.Filename = myFName & "*"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Workbooks.Open .FoundFiles(1)
Else
MsgBox "There was no file found."
End If
End With

HTH,
Bernie
MS Excel MVP

 

[Peter Pantus]

Thanks Bernie
That is acced what I'am looking for

Peter P.