Newbie vba - How do I reference cells in another workbook

[Ian]

Data is transferred from another program and each transfer opens a new
workbook in Excel. I have a workbook (Main) that processes the data. I have
the workbook name in cell B4 say Book5 and part of the macro looks like this

Dim wbook

Range("b4").Select
wbook = ActiveCell.Offset(0, 0).Value
Workbooks(wbook).Activate

'Processes some data

Workbooks("Main").Activate
Range("a7").Select
ActiveCell.Offset(1, 1).Value = "=max([book5]sheet1!$b:$b)"

My question is how do I write the last line using the variable wbook
instead of the workbook name book5

Thanks

Ian

 

[Patrick Molloy]

DIM sWBook as sring
DIM WB as Workbook
DIM WS as Worksheet
DIM sFormula as String

set WS = Activesheet
sWBook = WS.Range("B4").Value
SET WB = Workbooks.Open(sWBook)

sFormula = = "=max([" & sWBook & "]sheet1!$b:$b)"

WS.Range("A7").Offset1,1).Formula = sFormula

'this line converts the formula to values
WS.Range("A7").Offset1,1).value = _
WS.Range("A7").Offset1,1).value

WB.Cloase False


HTH
Patrick Molloy
Microsoft Excel MVP

 

[Nigel]

Ian, Suggest a few changes to your code, as follows plus the one way of
referencing another workbook using the formula.

Dim wbook As String

wbook = Range("B4").Value & ".xls"
Workbooks(wbook).Activate

'Processes some data

Workbooks("Main.xls").Activate
Range("A7").Formula = "=max([" & wbook & "]sheet1!$B:$B)"

Cheers
Nigel

 

[Ian]

Thanks Patrick and Nigel for fixing the problem

Regards

Ian