Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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 * "John Wirt" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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 "Daniel" wrote in message om... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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 * "John Wirt" wrote in message ... 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 "Daniel" wrote in message om... 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
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] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking to a cell in another (closed) workbook
Fantastisch, thanks. I'll try it. John
"Thomas Ramel" wrote in message ... 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export cell data to closed workbook | Excel Discussion (Misc queries) | |||
linking worksheets to a closed workbook | Excel Worksheet Functions | |||
Consolidation of data from cell in active sheet of closed workbook | Excel Worksheet Functions | |||
Linking 3 closed worksheets to master workbook template | Excel Worksheet Functions | |||
Linking to a closed workbook | Excel Worksheet Functions |