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

In the activeworkbook's location...

Files can be in any numbers and not less than 2 for the same preceedin
file name.

File signature (last 4 characters) of the file are required to chec
and open through stored in arrays only and not by prompting to ask an
open.

Somebody solved atmost to my requirement. please look below give
codes...


Assuming my filename starts from : "Project_"

Code
-------------------

Sub OpenFile_Location()
Dim aryNames 'to store file signatures

Dim i As Long
Dim oWb As Workbook
Dim sLastOpened As String

Dim NoOfWb As Long
Dim NoOpened As Long
Const MaxNoOfWb As Long = 2

'FILE SIGNATURES....
aryNames = Array("ILYS", "SAMR", "RMSH", "PRVZ", "CHET", "PRTP")

txtFile1.Text = ActiveWorkbook.Path & ActiveWorkbook.FullName

For i = LBound(aryNames) To UBound(aryNames)
On Error Resume Next
Set oWb = Workbooks("Project_" & aryNames(i) & ".xls")

If oWb Is Nothing Then
Set oWb = Workbooks.Open("" + ActiveWorkbook.Path + "\Project_" & aryNames(i))
If Not oWb Is Nothing Then
sLastOpened = oWb.FullName
NoOpened = NoOpened + 1
End If
End If
On Error Goto 0

If Not oWb Is Nothing Then
NoOfWb = NoOfWb + 1
Set oWb = Nothing
If NoOfWb = MaxNoOfWb Then
Exit For
End If
End If

Next i
If NoOfWb = 0 Then
MsgBox "File not found for Partner-2"
ElseIf NoOpened 0 Then
txtFile2.Text = "" + sLastOpened
Else
MsgBox "None opened - 2 already opened"
End If
End Sub

-------------------

Here, if file is not found for the next file to open or both the fil
(if only 2 similar name file is in the same location) is alread
opened, then it displays the last msgbox="None opened - 2 alread
opened"

Upto not found the file is ok. But I need action here to open the nex
file even though it is already opened and get the locationpath and it
workbook name in the txtfile2.tex

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

is it possible?? plz somebody help me out..

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

Yes, it's possible, but you still haven't fully explained your logic. Give
an example with

- the activefile name
- a directory listing
- which files should be opened

HTH,
Bernie
MS Excel MVP


"ilyaskazi" wrote
in message ...

is it possible?? plz somebody help me out...


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

Bernie,

Here I go with detailed explaination.... (dont bother previous post a
present)

Please follow each step given below very important...

Folder Location: "C:\Project"

In above folder following excel files are there...
Project_CHET
Project_ILYS
Project_RMSH
Project_ROSH

Contract_CHET
Contract_ILYS
Contract_RMSH

Sample_ROSH

Example_ILYS
Example_CHET

For each-n-every excel file above, last 4 characters are the fil
signature assigned for the person completed the file. One project ar
done by atleast 2 and above partners. So there can be 2 or more than
files for the same project, or can be even single file if the anothe
partners must hv not updated the above folder.

Now in my Userform, there is 4 txtboxes for..
txtfile1.text = to get path for partner-1 file
txtfile1name.text = to get name of partner-1 file
txtfile2.text = to get path for partner-2 file
txtfile2name.text = to get name of partner-2 file

2 buttons for...
btnFile1 = it opens browser window for selecting file-1 to open
btnFile2 = ---- " ---- for selecting file-2 to open

...once the file is selected in browser window, it opens the selecte
file and puts the path in txtfile1.text and filename i
txtfile1name.text and so as with same for file-2 in its txtboxes-2.

Suppose if "Project_CHET" (file-1) is already opened and active, the
upon loading my Userform, it gets the filepath and filename fo
txtfile1.text and txtfile1name.text automatically. If no excel file i
opened than does nothing.

This much is coded in my example file attached here in this post.

Now what i need....

1) Suppose if "Project_RMSH" (file-1) is already opened and active, ge
its filepath and filename as doing previously and open automatically th
next file (file-2) from the *same location of file-1*. Next file shoul
be any first found apart from the file-1. Means, open file-2 o
different file signature but containing same preceeding character
(before signature of file-1 ..i.e "Project_") and get its path and nam
for txtboxes-2.

2) But before that, check whether file-2 (of any signature with sam
preceeding chars) is already opened of *same location of file-1*. I
yes get its path and name for txtboxes2 without trying to open any nex
file (file-2). If the next file which is already opened but of not sam
location of file-1, then goto open next file in the location o
file-1.

3) There is possibilities of files may have opened more than 2 o
diffrent signature with same preeceding chars. If yes then get the pat
and name of any first file found (apart from file-1) for txtboxes2.

4) If file-2 (which is not opened of file-1 location) is not there i
the file-1 location then dont try to open any next file (file-2). Thi
is when only one file is done and the next partners hv not updated th
folder.

5) If file-1 which is active and does not contain file signature, the
again dont try to open any next file (file-2).

Last point(5) is also very important. Signatures are constant for ou
company employees. It should be stored in arrays. If any active fil
does not contain the signatures or may contain but not matches wit
stored in arrays then don't open the next file. Also if next file to b
open or already opened does not matches the signatures stored in array
then don't open the file or get the path and name in txtboxes2.

Folder may contain any types of files other than excel. Program shoul
open only excel files only.

Above folder is attached here with some example files only. Plz note
VBA Code is in "Book4.xls".

I m trying since last 2 weeks to solve this issue on so many forum'
websites. It is now most urgent to me.
I request you to kindly do the needful. Thanking you in anticipation.

Regards,
Ilyas Kaz

+-------------------------------------------------------------------
|Filename: Project.zip
|Download: http://www.excelforum.com/attachment.php?postid=3469
+-------------------------------------------------------------------

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

see my previous post and let me know whether it is possible???

it is simple but explaining with detailed story. sorry for that.

but somebody plz help me out. I m now almost given up with this.


--
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 macro below. I think it handles all of your requirements. Copy both
the macro and the function to a codemodule in your "Book4.xls".

When you have a workbook open and active that 1) has an underscore in its
name and 2) the name code matches one of your signature names, then it will
open other files that match if they (or similarly named files from other
folders) aren't already open.

Note that this doesn't require the use of a userform and text boxes - it
just opens the matching files automatically.

HTH,
Bernie
MS Excel MVP

Sub IlyaTryNow()
Dim myNames As Variant
Dim myFileName As String
Dim myFilePath As String
Dim myUSPos As Integer
Dim myShortFName As String
Dim i As Integer

myNames = Array("CHET", "ILYS", "RMSH", "ROSH")
myFileName = ActiveWorkbook.Name
myFilePath = ActiveWorkbook.Path

myUSPos = InStr(1, myFileName, "_")
If myUSPos = 0 Then Exit Sub
If IsError(Application.Match(Replace(Mid(myFileName, myUSPos + 1, _
Len(myFileName)), ".xls", ""), myNames, False)) Then Exit Sub

On Error GoTo OpenFile:
With Application.FileSearch
.NewSearch
.LookIn = myFilePath
.FileType = msoFileTypeExcelWorkbooks
.Filename = Left(myFileName, 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)
Resume AlreadyOpen:
AlreadyOpen:
End If
Next i
End If
End With
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 ...

see my previous post and let me know whether it is possible???

it is simple but explaining with detailed story. sorry for that.

but somebody plz help me out. I m now almost given up with this.


--
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

Ilya,

You're very welcome. I was beginning to get worried that you had given up
on this thread, so I'm glad to hear that you got it all worked out.

HTH,
Bernie
MS Excel MVP

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

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