OPEN EXCEL FILE ACCORDING TO CELL VALUE

[K]

Hi all, I am looking for a macro which should do something like this
(see below) when I click the button on sheet

Sub openfile()
if Mid(any file in "C:\Document\KK\" , 5 , 6).Name = Range("A1").Value
Then
Open that file
Else
MsgBox "File not Exist"
End If
End Sub

in other words I want macro to check all excel files in Folder "C:
\Document\KK\" and if any file "Mid(file name , 5 , 6)" equal to
Range("A1"). Value then Open that file. For example if the file have
name is "kks 556331" then if I put only "556331" in cell A1 and click
the button then macro should open that file. I hope I was able to
explain what i want. Please can any body help

 

[Nigel]

Take a look at using the Dir function.

Sub myFile()
Dim fName As String
fName = Dir("C:\Document\KK\*.xls")
Do While Len(fName) > 0
If Mid(fName, 20, 6) = Range("A1") Then
Workbooks.Open (fName)
Exit Do
End If
fName = Dir
Loop
End Sub

You will need to adjust the test condition above to suit your file name
format, take a look at InStrRev to strip out the file name.