how to find the excel file is already open

[s_balasubramanian]

im new learner in vba

i have opened file (part1.xlsx) and copy the active cell content. (Vendorcode: 200050)

i want to filter the records in another file in part2.xlsx pertains to that vendor code 200050 (3rd column).

if part2.xlsx file is already opened in readonly, the msgbox1 is appear.

if i press yes copied value is not coming

if i press no the prog error is shown as msgbox2

Guide me how to proceed the next step of prog if file is already open

 

[Peter_SSs]

Could you post you existing code?
Please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

 

[s_balasubramanian]

Sub p2_1020_vc()

' Ctrl + Shift + H

ActiveCell.Copy

Workbooks.Open "P:\Part 2\Part2_1020.XLSX", , True

Range("a1").pastespecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("H1").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-7],1)=""1"",LEFT(RC[-7],1)=""2""),""vc"",IF(LEFT(RC[-7],1)=""6"",""PO"",""Vname""))"
If Range("h1") = "vc" Then
Range("b1").Value = Left(Range("a1"), 6)
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=2, Criteria1:=Cells(1, 2).Value
Range("B1").Value = "Vendor"
ElseIf Range("h1") = "PO" Then
Range("f1").Value = Left(Range("a1"), 10)
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=6, Criteria1:=Cells(1, 6).Value
Range("f1").Value = "PO No"
Else
Range("f1").Value = Range("a1").Value
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=5, Criteria1:=Cells(1, 5).Value
Range("E1").Value = "Vendor Name"

End If


Range("a1").Value = "Plant"

End Sub

Sub p2_1020_vc()

' Ctrl + Shift + H

  ActiveCell.Copy

   Workbooks.Open "P:\Part 2\Part2_1020.XLSX", , True
   
    Range("a1").pastespecial Paste:=xlPasteValues
    Application.CutCopyMode = False
   
     Range("H1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(LEFT(RC[-7],1)=""1"",LEFT(RC[-7],1)=""2""),""vc"",IF(LEFT(RC[-7],1)=""6"",""PO"",""Vname""))"
       If Range("h1") = "vc" Then
    Range("b1").Value = Left(Range("a1"), 6)
        Selection.AutoFilter
         ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=2, Criteria1:=Cells(1, 2).Value
            Range("B1").Value = "Vendor"
         ElseIf Range("h1") = "PO" Then
             Range("f1").Value = Left(Range("a1"), 10)
          Selection.AutoFilter
         ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=6, Criteria1:=Cells(1, 6).Value
          Range("f1").Value = "PO No"
         Else
         Range("f1").Value = Range("a1").Value
          Selection.AutoFilter
         ActiveSheet.Range("$A$1:$AG$6000").AutoFilter Field:=5, Criteria1:=Cells(1, 5).Value
          Range("E1").Value = "Vendor Name"
        
    End If
   
       
    Range("a1").Value = "Plant"
  
End Sub

 

[Peter_SSs]

I will look at your question later but you didn't follow my request..

Please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

You will get better and faster help if you make it as easy as possible for people to help you. Look how much easier the code at the bottom of your previous post (where I have re-posted your code with the code tags) is to read and follow compared to the top part.

 

[Peter_SSs]

I will look at your question later ..

One way to try would be to start your procedure like this

Sub p2_1020_vc()
  Dim wsP2 As Workbook

  ActiveCell.Copy

  On Error Resume Next
  Set wsP2 = Workbooks("Part2_1020.XLSX")
  On Error GoTo 0
  If wsP2 Is Nothing Then Workbooks.Open "P:\Part 2\Part2_1020.XLSX", , True
  Workbooks("Part2_1020.XLSX").Activate
  
  Range("a1").PasteSpecial Paste:=xlPasteValues

 

[s_balasubramanian]

It is working
Thanks for your timely help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.