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

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.


--

Regards,
Nigel




"K" wrote in message
...
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

something like this should do the trick -- you will have to manipulate the
path name, the call statement (specify your reqs --i searched for files that
had a c in them) and possibly put an error trap in the For I loop before the
workbook open command.

HTH
dmoney

Sub openfile()
Dim arrFiles() As String
Dim strPath As String
Dim I As Integer

strPath = "c:\documents and settings\desktop\test"

ReDim arrFiles(0)
Call FindFilesRecursive(strPath, "*cc*.*", arrFiles())

For I = 0 To UBound(arrFiles())

Debug.Print arrFiles(I)
Workbooks.Open arrFiles(I)
Next I

End Sub


Function FindFilesRecursive(strPath As String, strSearch As String,
arrFiles() As String) As Long
'Returns: Total file length for all files
Dim strFilename As String ' Walking filename variable.
Dim strDirName As String ' SubDirectory Name.
Dim arrDirTemp() As String ' Buffer for directory name entries.
Dim intDirTemp As Integer ' Number of directories in this path.
Dim intI As Integer ' For-loop counter.
Dim intLast As Integer ' Last array subscript number


On Error GoTo SysFileERR

If Right(strPath, 1) < "\" Then strPath = strPath & "\"

' Search for subdirectories.
intDirTemp = 0
ReDim arrDirTemp(intDirTemp)

strDirName = Dir(strPath, vbDirectory Or vbHidden Or vbArchive Or
vbReadOnly Or vbSystem) 'Even if hidden, or otherwise.

Do While Len(strDirName) 0
' Ignore the current and encompassing directories.
If (strDirName < ".") And (strDirName < "..") Then
' Check for directory with bitwise comparison.
If GetAttr(strPath & strDirName) And vbDirectory Then
arrDirTemp(intDirTemp) = strDirName
intDirTemp = intDirTemp + 1

ReDim Preserve arrDirTemp(intDirTemp)

intLast = UBound(arrFiles())
ReDim Preserve arrFiles(intLast + 1)
arrFiles(intLast + 1) = strPath & strDirName

End If
SysFileERRCont:
End If
strDirName = Dir() ' Get next subdirectory.
Loop


' Search through this directory and sum file sizes.
strFilename = Dir(strPath & strSearch, vbNormal Or vbHidden Or vbSystem
Or vbReadOnly Or vbArchive)

While Len(strFilename) < 0
FindFilesRecursive = FindFilesRecursive + FileLen(strPath &
strFilename)

intLast = UBound(arrFiles())
ReDim Preserve arrFiles(intLast + 1)
arrFiles(intLast + 1) = strPath & strFilename

strFilename = Dir() ' Get next file.
Wend

' If there are sub-directories..
If intDirTemp 0 Then
' Recursively walk into them
For intI = 0 To intDirTemp - 1
FindFilesRecursive = FindFilesRecursive +
FindFilesRecursive(strPath & arrDirTemp(intI) & "\", strSearch, arrFiles())
Next intI
End If

AbortFunction:
Exit Function

SysFileERR:
If Right(strDirName, 4) = ".sys" Then
Resume SysFileERRCont 'Known issue with pagefile.sys
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description, ,
"Unexpected Error"
Resume 'AbortFunction
End If

End Function


"Nigel" wrote:

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.


--

Regards,
Nigel




"K" wrote in message
...
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