0

Suppose you have 2 Excel.Workbook variables wbA and wbB. The first variable is a workbook you have assigned with something like Excel.Workbooks.Open.

Now my Question: How can you assign wbA to wbB so that wbB is a copy of wbA and not just a reference to it?

I tried a simple wbB = wbA but that just seems to reference wbB to wbA.

Many thanks!

Erik A
  • 31,639
  • 12
  • 42
  • 67
Xam Eseerts
  • 367
  • 3
  • 19

1 Answers1

2

wbA points to a workbook object in memory, it isn't an actual workbook. In the same way if you do

Set wbB = wbA

You are just creating another pointer to the same workbook that's in memory, albeit via another pointer.

If you actually want a real copy of the workbook then you have to do just that - copy the actual workbook and set wbB to that instance instead.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • Hi! Yeah I thought so, damn. So I guess the only method would would be to do something like wbA.savecopyas and then pick that up with wbB right? Or do you know a better solution? – Xam Eseerts May 19 '16 at 13:46
  • exactly that, doesn't matter how many variables you use you're effectively just creating a daisy-chain of pointers back to the same workbook in memory - so you need to physically copy the workbook and create a new pointer to _that_ one instead. – SierraOscar May 19 '16 at 13:47
  • FYI, you can use `Set wb2 = Workbooks.Add("path to wb1 here")` to create a new workbook using the other one as a template. – Rory May 19 '16 at 13:50
  • Alright thank! @Rory: yeah I know. The problem is I do stuff with wbA first before assigning it to wbB. So I guess I will just save a copy of wbA to environ("temp") and pick it up with wbB. – Xam Eseerts May 19 '16 at 13:57
  • It's just occurred to me you'd have an issue using the same workbook as a template as it would re-open it! – Rory May 19 '16 at 14:18