MrExcel Publishing
Your One Stop for Excel Tips & Solutions

referencing non-open workbooks


Posted by Don on October 02, 2000 5:24 PM

I know it is possible to reference cell values
of closed workbooks. Does anyone know how to
reference cell formulas of a closed workbook.

For instance is it possible to copy a formula
from a closed workbook to a cell in another
workbook.

Thanks, -Don


Posted by Ben on October 03, 2000 6:45 AM

You can reference closed workbooks the same way you reference open workbooks. The difference is that when you open a workbook that has references to closed workbooks, you'll be asked if you want to update the link. When you open a workbook that has links to open workbooks, the links will update automatically.

In your formula, enter the spreadsheet's path and the name of the worksheet/range you're referencing:

='C:\WINDOWS\Desktop\[Source.xls]Sheet1'!$I$13

As you can see, the name of the file goes in brackets. An easy way to do it is to copy the cells you want to reference, and when you paste them select Paste Special > Paste Link.

-Ben

Posted by Don on October 03, 2000 7:44 AM

As I mentioned, I understand how to reference a
value in a closed book.

I need to know if there is a way to reference the
formulas in a closed book.

For instance in VB you can do the following:
Workbooks("Book1").Worksheets("Sheet1").Cells(1,1).Formula = Workbooks("Book2").Worksheets("Sheet1").Cells(1,1).Formula

This will copy the Formula from Book1, Sheet1, Cell A1 to Book2, Sheet1, Cell A1 but both workbooks must be open

My need is to recover an un-openable excel file.
I can reference the values but not the formulas.
However, to re-create the workbook I need the
formulas. And Yes, I have tried the recovery demo
which only recovers the values, no formulas.

It seems to me that if I can refernce the values
produced by the formulas then the formulas are
there somewhere also.

Thanks, -Don

Posted by Don on October 06, 2000 12:05 PM