It's actually very easy to pull data  from a closed workbook  into an open
workbook. I'm don't think you can retrieve the data via code, then put the
retrieved value in a cell. I think you will have to enter a formula in a cell to
pull the value from a cell in the closed workbook. Once the value is retrieved,
you can convert the formula to a value. Here is an example:

Sub Macro1()
'Enter a formula in A1 to pull A1 from Sheet1 of closed wkbk A.
'For the closed workbook, must include the path.
Range("A1").FormulaR1C1 = _
"=If(Len('D:\Data\[A.xls]Sheet1'!RC)>0,'D:\Data\[A.xls]Sheet1'!RC,"""")"
'Copy the formula in A1 down through A4.
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A4"), Type:=xlFillDefault
'Copy & paste as values.
Range("A1:A4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

There are some limitations to this technique. If the closed workbook is
password protected, you will have to provide the password when you enter the
first retrieve formula. You can create a SUMPRODUCT formula referring to a
closed workbook, but not SUMIF. I don't remember all the caveats, but I hope
this is helpful.