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

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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!


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

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
One way:
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Sorry, the last few lines should be
Code:
    On Error GoTo 0
    Range("A1").Copy
    Workbooks("Try.xlsm").Activate
    ActiveSheet.Paste
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Shg, thanks for quick response. Thats works perfect. yes, the modification part got rid of the error too... I also added application.cutcopymode = false:biggrin:


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!
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

Try this:
Code:
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Shg, thanks for quick response. Thats works perfect. yes, the modification part got rid of the error too... I also added application.cutcopymode = false:biggrin:


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.

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Another way

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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Another way

Code:
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! :p
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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! :p


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

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top