View Full Version : Working with vba in two excel workbooks

07-09-2010, 03:33 AM
If I have two workbooks open and I want to run a script in one workbook, which requires updating the other workbook. How do you call (write to) the already open second workbook- so that it doesn't attempt open the workbook a second time (which it can't as it is already open) ?

Bob Phillips
07-09-2010, 03:39 AM
The best way IMO is to set a variable to the other workbook when you open it

Set thatWB = Workbooks.Open(Filename:= ...)

and then use that, like so


07-09-2010, 03:50 AM
Thanks for the quick reply, but won't that only work as long as the workbook isn't already open?

For example:
Workbook ("Apples") is open and contains the code that has to be run on Workbook ("Pears") and input data to be saved into Workbook ("Pears").
However the user already has Workbook ("Pears") open.
If the user runs the code, won't it try to reopen (a read only) version of Workbook ("Pears"), and therefore be unable to write the data to Workbook ("Pears")?

Hope I've explained what I mean clearly enough.

Bob Phillips
07-09-2010, 03:57 AM
Well yes, that specific example only works on closed workbooks as you are opening it, but you can set the variable to already open workbooks, like so

Set wbApples = Workbooks("Apples.xls")

and so on.

07-09-2010, 04:18 AM
OK, so I'll need to write some code that checks if the 2nd workbook is already open, if it isn't then it needs to open it, and if it is then it just needs to action it.

i.e. the difference between using:
Set thatWB = Workbooks.Open(Filename:= ...)
Set wbApples = Workbooks("Apples.xls")

Bob Phillips
07-09-2010, 05:10 AM
Well that is easy

On Error Resume Next
Set wbApples = Workbooks("Apples.xls")
On Error Goto 0
If wbApples Is Nothing Then

Set wbApples = Workbooks.Open(Filename:= ...)
End If