Linking to a cell in another (closed) workbook

[John Wirt]

I would like to retrieve the value in a range named cell in a closed
workbook. The
workbook name, sheet name, and range name are known.

Is this possible Without opening the workbook?

Thanks.

JOhn Wirt

 

[Rob Bovey]

Hi John,

You can do this as long as the workbook is not password-protected. For a
workbook-level range name the formula would look like this:

='C:\Files\MyBook.xls'!BookLevelRangeName

for a worksheet-level range name the formula would look like this:

='C:\Files\[MyBook.xls]Sheet1'!SheetLevelRangeName

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Daniel]

Yes, just type following

='c:\test\[test.xls]sheet'!$A$1
instead of test type your own "adress" and at the end u typ wich cell
u want to retrive data from!

Enyoj
Daniel

 

[John Wirt]

Thanks for responding but I shoudl have said that I need a solution n Excel
VBA. That is, I would prefer looking up the value in the closed workbook and
then inserting in the cell in the current workbook as a cell value.

Ther reason for this is that the workbook is an "application" that will be
used by many different people and the "c:\test" directory will be different
for each person.

One solution would be to use the ChangeLink method. The Auto_Open code of
the workbook saves the name of "c:\test" directory in the registry, so it cn
be retireved by the code for each user. Then it would be possible to
rewrite the external link [='c:\test\[test.xls]sheet'!$A$1] in the Auto_Open
sub as you specify.

I would prefer some way of using the stored directory path to look up the
cell value in the (closed) workbook, if you can think of anything.

If not I can live with the ChangeLink approach.

Thank you.

John

Yes, just type following

='c:\test\[test.xls]sheet'!$A$1
instead of test type your own "adress" and at the end u typ wich cell
u want to retrive data from!

Enyoj
Daniel



"John Wirt" <[email protected]> wrote in message

I would like to retrieve the value in a range named cell in a closed
workbook. The
workbook name, sheet name, and range name are known.

Is this possible Without opening the workbook?

Thanks.

JOhn Wirt

 

[Rob Bovey]

I would prefer some way of using the stored directory path to look up the

cell value in the (closed) workbook, if you can think of anything.

Hi John,

As far as I know, the only way to do this is to enter a linking formula
directly into a worksheet cell. You can certainly create and enter this
formula using VBA, but you can't evaluate the result of the formula without
entering it into a worksheet cell.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks for responding but I shoudl have said that I need a solution n Excel
VBA. That is, I would prefer looking up the value in the closed workbook and
then inserting in the cell in the current workbook as a cell value.

Ther reason for this is that the workbook is an "application" that will be
used by many different people and the "c:\test" directory will be different
for each person.

One solution would be to use the ChangeLink method. The Auto_Open code of
the workbook saves the name of "c:\test" directory in the registry, so it cn
be retireved by the code for each user. Then it would be possible to
rewrite the external link [='c:\test\[test.xls]sheet'!$A$1] in the Auto_Open
sub as you specify.

I would prefer some way of using the stored directory path to look up the
cell value in the (closed) workbook, if you can think of anything.

If not I can live with the ChangeLink approach.

Thank you.

John

Yes, just type following

='c:\test\[test.xls]sheet'!$A$1
instead of test type your own "adress" and at the end u typ wich cell
u want to retrive data from!

Enyoj
Daniel



"John Wirt" <[email protected]> wrote in message

I would like to retrieve the value in a range named cell in a closed
workbook. The
workbook name, sheet name, and range name are known.

Is this possible Without opening the workbook?

Thanks.

JOhn Wirt

 

[Thomas Ramel]

Grüezi John

John Wirt schrieb am 11.05.2004

Thanks for responding but I shoudl have said that I need a solution n Excel
VBA. That is, I would prefer looking up the value in the closed workbook and
then inserting in the cell in the current workbook as a cell value.

I would prefer some way of using the stored directory path to look up the
cell value in the (closed) workbook, if you can think of anything.

There is indeed a way to get values from closed workbooks; it's an xl4macro
John Walkenbach implemented in a function (works only in VBA *not* in a
cell on a worksheet):

Private Function GetValue(path, file, sheet, range_ref)
'Retrieves a value from a closed workbook
'The GetValue function, listed below takes four arguments:
'path: The drive and path to the closed file (e.g., "d:\files")
'file: The workbook name (e.g., "99budget.xls")
'sheet: The worksheet name (e.g., "Sheet1")
'ref: The cell reference (e.g., "C4")
Dim arg As String
'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win 2000Pro SP-4 / xl2000 SP-3]

 

[John Wirt]

Fantastisch, thanks. I'll try it. John

Grüezi John

John Wirt schrieb am 11.05.2004

Thanks for responding but I shoudl have said that I need a solution n Excel
VBA. That is, I would prefer looking up the value in the closed workbook and
then inserting in the cell in the current workbook as a cell value.

I would prefer some way of using the stored directory path to look up the
cell value in the (closed) workbook, if you can think of anything.

There is indeed a way to get values from closed workbooks; it's an xl4macro
John Walkenbach implemented in a function (works only in VBA *not* in a
cell on a worksheet):

Private Function GetValue(path, file, sheet, range_ref)
'Retrieves a value from a closed workbook
'The GetValue function, listed below takes four arguments:
'path: The drive and path to the closed file (e.g., "d:\files")
'file: The workbook name (e.g., "99budget.xls")
'sheet: The worksheet name (e.g., "Sheet1")
'ref: The cell reference (e.g., "C4")
Dim arg As String
'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win 2000Pro SP-4 / xl2000 SP-3]