Given below are the 5 file names in location "C:\project"

FILE_ILYS
FILE_RMSH
FILE_PRVZ
FILE_CHET
FILE_PRTP

Last 4 characters of the file name are the *file signature* assigned
for the person completed the file.

I am trying to open two file on the basis of last 4 characters from
this 5 saved files.

If any of the above file is missing then it should open any next file..
and so on.


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

ilyaskazi,

Something along the lines of

Sub OpenSpecificFiles()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Project"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like "*CHET" Then
Workbooks.Open .FoundFiles(i)
End If
Next i
Else
MsgBox "There were no matching files found."
End If
End With
End Sub


HTH,
Bernie
MS Excel MVP


"ilyaskazi" wrote
in message ...

Given below are the 5 file names in location "C:\project"

FILE_ILYS
FILE_RMSH
FILE_PRVZ
FILE_CHET
FILE_PRTP

Last 4 characters of the file name are the *file signature* assigned
for the person completed the file.

I am trying to open two file on the basis of last 4 characters from
this 5 saved files.

If any of the above file is missing then it should open any next file..
and so on.


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

Oops, I forgot a trailing *, and just in case the case is different, change

If .FoundFiles(i) Like "*CHET" Then

To

If UCase(.FoundFiles(i)) Like "*CHET*" Then

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
ilyaskazi,

Something along the lines of

Sub OpenSpecificFiles()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Project"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like "*CHET" Then
Workbooks.Open .FoundFiles(i)
End If
Next i
Else
MsgBox "There were no matching files found."
End If
End With
End Sub


HTH,
Bernie
MS Excel MVP


"ilyaskazi" wrote
in message ...

Given below are the 5 file names in location "C:\project"

FILE_ILYS
FILE_RMSH
FILE_PRVZ
FILE_CHET
FILE_PRTP

Last 4 characters of the file name are the *file signature* assigned
for the person completed the file.

I am trying to open two file on the basis of last 4 characters from
this 5 saved files.

If any of the above file is missing then it should open any next file..
and so on.


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread:
http://www.excelforum.com/showthread...hreadid=375966

Assume, I hv preceeding characters of file name as "FILE1_" or "FILE2_"
in same location, and my activeworkbook is "FILE1_ILYS".

Now on excuting the macro, i must get filepath (C:\Project) and its
filename (FILE1_ILYS) in txtbox1 and find the similar preceeding
characters of the file name in the same location only but with the
different signature (i.e. for eg. "FILE1_CHET"). Open the next file if
founded and get its path and name in txtbox2.

Location may contain: FILE1_ILYS, FILE1_CHET, FILE2_ILYS, FILE2_CHET,
FILE3...

Here if FILE1_ILYS workbook is active, then open the similar name file
of different signature.

File signature is always contant: ILYS, CHET, PRVZ, NMRT,....


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

Also please note *file signatures* are around 20, so i cannot call
specifically each to check and open.

Instead it will be better to store this file signatures in arrays.

for eg:
aryNames = Array("BART", "CHET", "DISL", "FRDS", "ILYS", "JOEM",
"KUSH",)


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

ilyaskazi,

Try the macro below. Assumes you want to open a file from the same folder as
the activeworkbook, with the same starting part of the file name (up to the
underscore character).

HTH,
Bernie
MS Excel MVP


Sub OpenSpecificFiles2()
Dim i As Integer
Dim myFile As Workbook

Set myFile = ActiveWorkbook

With Application.FileSearch
.NewSearch
.LookIn = myFile.Path
.Filename = Left(myFile.Name, InStr(1, myFile.Name, "_")) & "*"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
If MsgBox("Open " & .FoundFiles(i) & "?", vbYesNo) = vbYes Then
Workbooks.Open .FoundFiles(i)
Exit Sub
End If
Next i
Else
MsgBox "There were no matching files found."
End If
End With
End Sub


"ilyaskazi" wrote
in message ...

Also please note *file signatures* are around 20, so i cannot call
specifically each to check and open.

Instead it will be better to store this file signatures in arrays.

for eg:
aryNames = Array("BART", "CHET", "DISL", "FRDS", "ILYS", "JOEM",
"KUSH",)


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

hi Bernie,

thankyou for replying and your codes.

Userform is necessary for me and its textboxes bcoz i m handling other
things with it with my actual userform.

The userform which i provided was an just example with the features to
my problem.

Your code is working good but not completely to my requirements.

See my post title...I need only 2 file to be opened of similar found
(i.e active-file and the other to open/already opened)
The correct filepath and filename of 2nd file is required to be entered
for txtboxes-2.

awaiting your reply,

regards,
ilyaskazi


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

Ilyua,

If you have this file open:

Project_CHET

And these three fileas are in the same folder:
Project_ILYS
Project_RMSH
Project_ROSH

If all are valid names, how do you decide which to open?

HTH,
Bernie
MS Excel MVP


"ilyaskazi" wrote
in message ...

hi Bernie,

thankyou for replying and your codes.

Userform is necessary for me and its textboxes bcoz i m handling other
things with it with my actual userform.

The userform which i provided was an just example with the features to
my problem.

Your code is working good but not completely to my requirements.

See my post title...I need only 2 file to be opened of similar found
(i.e active-file and the other to open/already opened)
The correct filepath and filename of 2nd file is required to be entered
for txtboxes-2.

awaiting your reply,

regards,
ilyaskazi


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

if *first file* is opened (i.e...Project_CHET) then

check similar file of same location is whether *already opened*.

If *YES* get its filepath and filename for txtboxes-2 without trying to
open any files.

If *NO* open *ANY* next file found to be first programatically(*BUT
ONLY ONE*) of same location (It is not necessary to open which)

If NOT found any file in same location then do nothing.


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=375966

Ilya,

Try the sub below.

HTH,
Bernie
MS Excel MVP


Sub IlyaTryNow2()
Dim myNames As Variant
Dim myFileName1 As String
Dim myFilePath1 As String
Dim myFileName2 As String
Dim myFilePath2 As String
Dim myUSPos As Integer
Dim myShortFName As String
Dim myFileType As String
Dim mySig As String
Dim i As Integer

myNames = Array("CHET", "ILYS", "RMSH", "ROSH")
myFileName1 = ActiveWorkbook.Name
myFilePath1 = ActiveWorkbook.Path

myUSPos = InStr(1, myFileName1, "_")
'Check for an underscore
If myUSPos = 0 Then Exit Sub
'Check that the signature is one of the array
mySig = Replace(Mid(myFileName1, myUSPos + 1, Len(myFileName1)), ".xls", "")
If IsError(Application.Match(mySig, myNames, False)) Then Exit Sub
'Get the file type
myFileType = Left(myFileName1, myUSPos)

On Error GoTo NotOpen:
For i = LBound(myNames) To UBound(myNames)
If myNames(i) < mySig Then
Windows(myFileType & myNames(i) & ".xls").Activate
If ActiveWorkbook.Path = myFilePath1 Then
myFilePath2 = myFilePath1
myFileName2 = ActiveWorkbook.Name
GoTo AllDone:
End If
End If
NotOpen:
Resume FindNext:
FindNext:
Next i


On Error GoTo OpenFile:
With Application.FileSearch
.NewSearch
.LookIn = myFilePath1
.FileType = msoFileTypeExcelWorkbooks
.Filename = Left(myFileName1, myUSPos - 1) & "*"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
myShortFName = ReturnShortName(.FoundFiles(i))
If Not IsError(Application.Match(Mid(myShortFName, myUSPos + 1, _
Len(myShortFName)), myNames, False)) Then
Windows(myShortFName & ".xls").Activate
GoTo AlreadyOpen:
OpenFile:
Workbooks.Open .FoundFiles(i)
myFilePath2 = ActiveWorkbook.Path
myFileName2 = ActiveWorkbook.Name
GoTo AllDone:
Resume AlreadyOpen:
AlreadyOpen:
End If
Next i
End If
End With
AllDone:

MsgBox "First File path: " & myFilePath1
MsgBox "First File name: " & myFileName1
MsgBox "Second File path: " & myFilePath2
MsgBox "Second File name: " & myFileName2
End Sub


Function ReturnShortName(inString As String) As String
Dim numSlash As Integer
Dim i As Integer
'Functions takes a path and filename, and
'returns just the filename w/o extension
numSlash = Len(inString) - Len(Application.Substitute(inString, "\", ""))
For i = 1 To numSlash
inString = Right(inString, Len(inString) - InStr(1, inString, "\"))
Next i
ReturnShortName = Left(inString, Len(inString) - 4)
End Function


"ilyaskazi" wrote
in message ...

if *first file* is opened (i.e...Project_CHET) then

check similar file of same location is whether *already opened*.

If *YES* get its filepath and filename for txtboxes-2 without trying to
open any files.

If *NO* open *ANY* next file found to be first programatically(*BUT
ONLY ONE*) of same location (It is not necessary to open which)

If NOT found any file in same location then do nothing.

Thats great! I hv checked today...

Thankyou. It is now working exactly as I wanted. Problem is completel
solved and updated in my vba.

Thanking you once again for your time taken and for kindly helping wit
this

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=37596