Specify the workbook when working on a worksheet in VBA

Question

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

What doesn't work? Also when setting your sh1s you can do Set sh1 = wb1.Worksheets("Sheet1")...That way you don't have to include the workbook name each time too. — BruceWayne
– BruceWayne, Commented Sep 19, 2017 at 15:18
This is what doesn't work: wb1.sh1... You can't nest declared objects like that. It either needs to be wb1.Sheets("Sheet1")... or simply sh1.... If you want to specify the workbook for the sh1 variable, do it in your Set line: Set sh1 = wb1.Sheets("Sheet1") and then you can check what workbook it's a part of by looking at it's .Parent.Name property: Debug.Print sh1.Parent.Name — tigeravatar
– tigeravatar, Commented Sep 19, 2017 at 15:21
How are you opening the workbooks? Looks like you're discarding the Workbook object reference returned by Workbooks.Open, somewhere down the line. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 19, 2017 at 16:00

Mathieu Guindon · Accepted Answer · 2017-09-19 16:07:42Z

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.

Collectives™ on Stack Overflow

Specify the workbook when working on a worksheet in VBA

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related