CODEWITHSUNDEEP
excelvba
Justin Captain Wypij
Justin Captain Wypij

Reputation: 51

VBA - How to reference two separate open workbooks without naming them?

I have looked around for awhile and cant seem to locate what I need.

Upvotes: 1

Views: 55

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

Refer to Workbooks

If you know there are only two workbooks open, you can use the Index property.

Sub ReferToWorkbooks()

    Dim wb1 As Workbook
    Dim wb2 As Workbook

    Set wb1 = Workbooks(1)
    Set wb2 = Workbooks(2)

    Debug.Print wb1.Name
    Debug.Print wb2.Name

End Sub

It is better to loop through all open workbooks and then create references to the ones you need.

Sub ReferToWorkbooks2()

    Dim wb As Workbook

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

End Sub

In case you have a worksheet in your code you use the Parent property:

Sub ReferToWorkbooks3()

    Dim ws As Worksheet
    Dim wb As Workbook

    Set ws = Worksheets("Sheet1")

    Debug.Print ws.Parent.Name

    ' or

    Set wb = ws.Parent
    Debug.Print wb.Name

End Sub

In case you have a range in your code you use the Parent property twice:

Sub ReferToWorkbooks4()

    Dim rng As Range
    Dim wb As Workbook

    Set rng = Range("A1")

    Debug.Print rng.Parent.Parent.Name

    ' or

    Set wb = rng.Parent.Parent
    Debug.Print wb.Name

End Sub

You should better explain the scenario where you might need this.

Upvotes: 1

Related Questions