+ Reply to Thread
Results 1 to 12 of 12

Getting value from an unopened file (indirectly referenced)

  1. #1
    learnexcel
    Guest

    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. #2
    FSt1
    Guest

    RE: 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. #3
    learnexcel
    Guest

    RE: 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. #4
    Dave Peterson
    Guest

    Re: 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. #5
    FSt1
    Guest

    Re: 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. #6
    learnexcel
    Guest

    Re: 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. #7
    Dave Peterson
    Guest

    Re: 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. #8
    learnexcel
    Guest

    Re: 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. #9
    Dave Peterson
    Guest

    Re: 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. #10
    learnexcel
    Guest

    Re: 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. #11
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #12
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1