Working with vba in two excel workbooks

[ukdane]

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) ?

Thanks

[Bob Phillips]

The best way IMO is to set a variable to the other workbook when you open it

[vba]

Set thatWB = Workbooks.Open(Filename:= ...)
[/vba]

and then use that, like so

[vba]

thaWb.Worksheets(1)...
[/vba]

[ukdane]

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]

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

[vba]

Set wbApples = Workbooks("Apples.xls")
[/vba]

and so on.

[ukdane]

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:= ...)
and
Set wbApples = Workbooks("Apples.xls")

[Bob Phillips]

Well that is easy

[vba]

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
[/vba]