| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting value from an unopened file (indirectly referenced)
1. I am working on file F1 and Cell A1 contains a file name (fname).
2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
hi,
Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Thanks FSt1.
I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
thank Dave
FSt1 "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Dear Dave
Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Harlan does very nice work.
learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Dear Dave
I am coming back again- sorry, But the returned string is truncated to 255 Characters. Any reason ? Reagrds learnexcel "Dave Peterson" wrote: Harlan does very nice work. learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
That's the way excel works with closed files.
The only workaround I know is to open the other file. learnexcel wrote: Dear Dave I am coming back again- sorry, But the returned string is truncated to 255 Characters. Any reason ? Reagrds learnexcel "Dave Peterson" wrote: Harlan does very nice work. learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Thanks for the info.
Regards learnexcel "Dave Peterson" wrote: That's the way excel works with closed files. The only workaround I know is to open the other file. learnexcel wrote: Dear Dave I am coming back again- sorry, But the returned string is truncated to 255 Characters. Any reason ? Reagrds learnexcel "Dave Peterson" wrote: Harlan does very nice work. learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
That's the way excel works with closed files.
The only workaround I know is to open the other file. I thought Harlan's routine opened the file in another instance of excel. So the file would be opened, would it not. A little more due diligence required? -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... That's the way excel works with closed files. The only workaround I know is to open the other file. learnexcel wrote: Dear Dave I am coming back again- sorry, But the returned string is truncated to 255 Characters. Any reason ? Reagrds learnexcel "Dave Peterson" wrote: Harlan does very nice work. learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Or maybe not.
Harlan's code starts another instance of excel, but never opens the other workbook. He uses an xlm macro to retrieve the value from a closed file into that instance. (Ps. I think we've had this conversation before <vbg.) (A long time ago, I posted a short, inaccurate description of Harlan's code. I was severely chastised by the author of the code <v,vbg.) Tom Ogilvy wrote: That's the way excel works with closed files. The only workaround I know is to open the other file. I thought Harlan's routine opened the file in another instance of excel. So the file would be opened, would it not. A little more due diligence required? -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... That's the way excel works with closed files. The only workaround I know is to open the other file. learnexcel wrote: Dear Dave I am coming back again- sorry, But the returned string is truncated to 255 Characters. Any reason ? Reagrds learnexcel "Dave Peterson" wrote: Harlan does very nice work. learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Want blank cell displayed if indirectly referenced value is zero | Excel Worksheet Functions | |||
| Excel - How to indirectly access a file whose name is in a cell | Excel Worksheet Functions | |||
| copying data from an unopened file programmatically | Excel Discussion (Misc queries) | |||
| Extracting Footer information from unopened XLS file. | Excel Programming | |||
| download .csv file indirectly from website - long | Excel Programming | |||
Linear Mode
