If 1st file is not found then look for 2nd file or 3rd file opened....!

[pedie]

Hi,

If 1st file ("For Pedie(1).xls") is not found then look for 2nd file ("For Pedie(2).xls") if not found then look for 3rd file ("For Pedie(3).xls"). If file is found anytime then stop looking for the other files and do what my code has to do. Below is the code that looks for single file and if there is no file then gives me error....
P.S: Anyone of these three files will be open when this code is running from book "try.xlsm"

Thanks to everyone for helping!

Sub try2()
' the bold line below has nothing to do with my code but i thought this line stoped the error i was receiving earlier:biggrin:
[B]Dim wb As Workbook, myfile As String[/B]
Workbooks("For Pedie(1).xls").Activate
    Range("A1").Copy
    Windows("Try.xlsm").Activate
    Cells(1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

[shg]

One way:

Sub try2()
    On Error Resume Next
 
    Workbooks("For Pedie(1).xls").Activate
    If Err.Number Then
        Err.Clear
        Workbooks("For Pedie(2).xls").Activate
        If Err.Number Then
            Err.Clear
            Workbooks("For Pedie(3).xls").Activate
            If Err.Number Then
                Exit Sub
            End If
        End If
    End If
 
    On Error GoTo 0
    Range("A1").Copy Destination:=Workbooks("Try.xlsm").Range("A1")
End Sub

 

[shg]

Sorry, the last few lines should be

    On Error GoTo 0
    Range("A1").Copy
    Workbooks("Try.xlsm").Activate
    ActiveSheet.Paste

 

[pedie]

Shg, thanks for quick response. Thats works perfect. yes, the modification part got rid of the error too... I also added application.cutcopymode = false


And is there a way to find our all the excel file that is currently being opened and used by me without having to mentioned the file path or name or anything like that?? If it can list just the excel workbooks that are currently open....

Thanks again!

 

[p45cal]

Try this:

Sub try2()
Dim ws As Worksheet
For i = 1 To 5  'to  6? to whatever.
  On Error Resume Next
  Set ws = Workbooks("For Pedie(" & i & ").xls").ActiveSheet
  On Error GoTo 0
  If Not ws Is Nothing Then
    Workbooks("Try.xlsm").Activate
    ws.Range("A1").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Exit For
  End If
Next i
End Sub

If you knew the name of the sheet you want to pcopy to in try.xlsm the code could be significantly shorter.

 

[p45cal]

Shg, thanks for quick response. Thats works perfect. yes, the modification part got rid of the error too... I also added application.cutcopymode = false


And is there a way to find our all the excel file that is currently being opened and used by me without having to mentioned the file path or name or anything like that?? If it can list just the excel workbooks that are currently open....

Thanks again!

This will pop up a message box with the name of each open workbook in turn, and you'll see a list in the Immediate Pane of the vbe too.

For Each wb In Workbooks
  MsgBox wb.Name
  Debug.Print wb.Name
Next wb

 

[VoG]

Another way

Function bBookOpen(wbName As String) As Boolean
On Error Resume Next
bBookOpen = Len(Workbooks(wbName).Name)
End Function


Sub try2()
Dim wb As Workbook, myfile As String, i As Integer
Dim wbs
wbs = Array("For Pedie (1).xls", "For Pedie (2).xls", "For Pedie (2).xls")
For i = LBound(wbs) To UBound(wbs)
    If bBookOpen(wbs(i)) Then
        wbs(i).Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("Try.xlsm").Sheets("Sheet1").Range("A1")
        Application.CutCopyMode = False
        Exit Sub
    End If
Next i
End Sub

 

[pedie]

That was short and perfect.
Can I make the code select the workbook eg: called "Book1.xls" if found in the above code process (then copy something from sheet1 maybe)???

 

[pedie]

Another way

Function bBookOpen(wbName As String) As Boolean
On Error Resume Next
bBookOpen = Len(Workbooks(wbName).Name)
End Function
 
 
Sub try2()
Dim wb As Workbook, myfile As String, i As Integer
Dim wbs
wbs = Array("For Pedie (1).xls", "For Pedie (2).xls", "For Pedie (2).xls")
For i = LBound(wbs) To UBound(wbs)
    If bBookOpen(wbs(i)) Then
        wbs(i).Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("Try.xlsm").Sheets("Sheet1").Range("A1")
        Application.CutCopyMode = False
        Exit Sub
    End If
Next i
End Sub

Peter, perfect...thanks for sharing another way of doing it....
I hope you guys are getting well paid for doing such an owesome job everyday...everytime....fast & accurate!

 

[VoG]

Peter, perfect...thanks for sharing another way of doing it....
I hope you guys are getting well paid for doing such an owesome job everyday...everytime....fast & accurate!

We don't get paid, we are all volunteers.