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

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

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

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

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

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

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

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

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

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

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

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