Open next file if not found the one to open

[ilyaskazi]

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 assigned for the person completed the file.

I am trying to open only one 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.

[Bob Phillips]

Something like this?


Dim aryNames
Dim i As Long
Dim oWb As Workbook
aryNames = Array("ILYS"",RMSH", "PRVZ", "CHET", "PRTP")
For i = LBound(aryNames) To UBound(aryNames)
On Error Resume Next
Set oWb = Workbooks.Open("FILE_" & aryNames(i))
On Error GoTo 0
If Not oWb Is Nothing Then
Exit For
End If
Next i


If oWb Is Nothing Then MsgBox "No file found"

[ilyaskazi]

thankyou,

its working fine. But now I need to open any 2 file if found from the given array names.

if ILYS is already opened, then go to open next file.

[ilyaskazi]

help plz...

[Steiner]

Maybe this one helps, I slightly modified xld's code to count the number of workbooks it opened and stop once this number reaches the const MaxNoOfWb defined right at the beginning:


Option Explicit
Sub Test()
Dim aryNames
Dim i As Long
Dim oWb As Workbook
Dim NoOfWb As Long
Const MaxNoOfWb As Long = 2
aryNames = Array("ILYS"",RMSH", "PRVZ", "CHET", "PRTP")
For i = LBound(aryNames) To UBound(aryNames)
On Error Resume Next
Set oWb = Workbooks.Open("FILE_" & aryNames(i))
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 "No file found"
End Sub


Daniel

[ilyaskazi]

Fantastic,

My problem is solved, but i need to store the file-path and file-name of Second opened file in variable.

[Bob Phillips]

Fantastic,

My problem is solved, but i need to store the file-path and file-name of Second opened file in variable.


Option Explicit

Sub Test()
Dim aryNames
Dim i As Long
Dim oWb As Workbook
Dim NoOfWb As Long
Const MaxNoOfWb As Long = 2
Const kPath As String = "C:\myDir\FILE_"
aryNames = Array("ILYS"",RMSH", "PRVZ", "CHET", "PRTP")
For i = LBound(aryNames) To UBound(aryNames)
On Error Resume Next
Set oWb = Workbooks.Open(kPath & aryNames(i))
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 "No file found"
End Sub

[ilyaskazi]

I cannot give..


Const kPath As String = "C:\myDir\FILE_"

because path of workbook always changes. So i hv done like this...


Set oWb = Workbooks.Open("" + ActiveWorkbook.Path + "\FILE_" & aryNames(i))

And i need to store this filepath alongwith filename (of second) in txtbox as for eg..


txtbox.text="C:\myDir\FILE_RMSH.xls"

[Bob Phillips]

And i need to store this filepath alongwith filename (of second) in txtbox as for eg..

txtbox.text="C:\myDir\FILE_RMSH.xls"

txtBox.Text = ActiveWorkbook.FullName

after you have opened it

[ilyaskazi]

Yes it is working when "FILE_ILYS" is already opened. It is giving path-n-name in txtbox for "FILE_RMSH".

But if "FILE_RMSH" is already opened, then instead of giving path-n-name for "FILE_ILYS", it is showing "FILE_RMSH" only in txtbox.

[Bob Phillips]

Option Explicit

Sub Test()
Dim aryNames
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
aryNames = Array("ILYS", "RMSH", "PRVZ", "CHET", "PRTP")
For i = LBound(aryNames) To UBound(aryNames)
On Error Resume Next
Set oWb = Workbooks("FILE_" & aryNames(i) & ".xls")
If oWb Is Nothing Then
Set oWb = Workbooks.Open("FILE_" & 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 "No file found"
ElseIf NoOpened > 0 Then
MsgBox sLastOpened
Else
MsgBox "None opened - 2 already opened"
End If
End Sub

[ilyaskazi]

No output came

Check the attachment files and make the changes plz.

vba code is in "Test.xls"

two file to open: "PROJECT_ILYS" and "PROJECT_RMSH"

[Bob Phillips]

No output came

You should use the code I gave you. You changed


Set oWb = Workbooks("FILE_" & aryNames(i))

to


Set oWb = Workbooks.Open("" + ActiveWorkbook.Path + "\Project_" & aryNames(i))



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

[ilyaskazi]

thankyou,

sorry for that goofup...

regards
ilyas kazi

[ilyaskazi]

i need more changes...

If "2 files are already opened" then...
get me the path and file names of each.

at present, If i m executing the code, the path and file name gets stored in txtbox1.
and shows me the msgbox of "None opened - 2 already opened".

But i need now to stored the 2nd filename and its path in txtbox2, if both the files are already opened.

[ilyaskazi]

XLD, is it possible???

[Bob Phillips]

i need more changes...

If "2 files are already opened" then...
get me the path and file names of each.

at present, If i m executing the code, the path and file name gets stored in txtbox1.
and shows me the msgbox of "None opened - 2 already opened".

But i need now to stored the 2nd filename and its path in txtbox2, if both the files are already opened.

What if 3 are open?

At present it puts the active file in textbox1. What do you want now?

[ilyaskazi]

with your latest given code, I m able to get 2 file locations and its file names in the 2 txtboxes for the particular files on the userform.

but this is possible if only 1 file is opened to get file location and file name for the other on the basis of 4 character-file signature appended on the file name.

If both the files are already opened, program displays the msgbox saying "None-opened or 2 already opened"

Here, if 2 files are already opened, i need location and its names for both the files.

If program is excuted from the file-1, get its location and names on the txtbox1 and file-2 on the txtbox2.

[Bob Phillips]

with your latest given code, I m able to get 2 file locations and its file names in the 2 txtboxes for the particular files on the userform.

but this is possible if only 1 file is opened to get file location and file name for the other on the basis of 4 character-file signature appended on the file name.

If both the files are already opened, program displays the msgbox saying "None-opened or 2 already opened"

Here, if 2 files are already opened, i need location and its names for both the files.

If program is excuted from the file-1, get its location and names on the txtbox1 and file-2 on the txtbox2.

You have totally confused and didn't answer my question about 3 files. As I see, it will only ever run from file 1, so it works as wanted?

[ilyaskazi]

yes, if run from file1 then it is doing perfect. ...here u r right.

And as u asked for 3 if opened??.
Here always 2 file is constantly opened from the same folder only.

If so 3 opened, then third file will be definitely from diffferent folder.

Program must chk the 2nd file's location and if found to be same as of 1st file, then its location and its name must show on txtbox2.

[crmpicco]

ilyaskazi (http://www.vbaexpress.com/forum/member.php?u=1717) are you automatically loading Airline fares?

[ilyaskazi]

no. how do u came to know that abt I fm airline field???

[ilyaskazi]

xld, r u cleared with that??

[ilyaskazi]

:help
can i get the next file's location and name??