Search code examples
vbaexcelobjectworksheet

Specify the workbook when working on a worksheet in VBA

I would like to get the value of cell. I have multiple workbooks, and some of the workbooks have sheets with the same name.

For example, test.xlsx and test2.xlsx both has a sheet named sheet1.xlsx

So, when working on a sheet, I would like to specify the workbook. I use wb.sh.*expression* all the time, and I am surprised that this does not work.

What am I missing here conceptually?

Code:

set wb1 = Workbooks("Test1.xlsx")
Set sh1 = Worksheets("Sheet1")
Debug.Print wb1.sh1.Range("A1").Value

Code which would work, but is not specific enough:

set wb1 = Workbooks("Test1.xlsx")
Set sh1 = Worksheets("Sheet1")
Debug.Print sh1.Range("A1").Value

Note: Test1.xlsx has a sheet named Sheet1

Solution

  • When you open the workbook, the Open method gives you the Workbook object reference - hold on to it instead of discarding it:

    Dim wb As Workbook
Set wb = Workbooks.Open(path)

    That way you never need to query the Workbooks collection and supply a hard-coded file name to get your workbook.

    Next up, the Worksheet reference:

    Set sh1 = Worksheets("Sheet1")

    The Worksheets collection is a property that belongs to a Workbook object. Indeed, that's not "specific" enough: if you don't qualify the property call, then you're implicitly referring to ActiveWorkbook - which may or may not be the workbook you need to be using.

    That's where that wb reference comes into play:

    Set sh1 = wb.Worksheets("Sheet1")

    Lastly, what you have here:

    Debug.Print wb1.sh1.Range("A1").Value

    Is not only illegal, it's overkill: sh1 already knows what Workbook object it belongs to - you can get that object reference through sh1.Parent and compare it to the wb reference:

    Debug.Assert sh1.Parent Is wb

    sh1 is a local variable, not a member of the Workbook interface: that's why you can't do wb1.sh1.

    I use wb.sh.expression all the time

    If your code ever worked, I guarantee you don't.