it DID actually work without opening  the original file...
The resulting formula I used was:

='C:\temp\[Book1.xls]Sheet1'!$A$1

which read  the contents from Cell A1 in sheet "Sheet1"
of workbook: C:\temp\Book1.xls

i even tested it by changing the contents of Cell A1,
closed all workbooks and opened the "new" book and it updated properly.

You can programatically insert this into the cell by using:

ActiveCell.FormulaR1C1 = "='C:\temp\[Book1.xls]Sheet1'!R2C1"

Now, a blank in Book1 returns "0", so to remove that, I used instead:


ActiveCell.FormulaR1C1 = _
"=IF('C:\temp\[Book1.xls]Sheet1'!R1C1 =
0,"""",'C:\temp\[Book1.xls]Sheet1'!R1C1)"

then used:

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to copy/paste values

interesting...
Learn something new every day... Does that mean I can go home now?