There is a recent problem with google inserting extra characters when
copied|pasted to either your code window (in the VBE) or even in the newsgroups.

When I copied Harlan's code and pasted, I got this line:

Set xlapp = CreateObject("Excel.Applicatio n")

This has one of those unfortunate errors in it.

It should be:

Set xlapp = CreateObject("Excel.Application")

(no space before the final n in application.)

===
I tested with this in A1:
C:\My Documents\excel\[Book2.xls]
and this in A2:
="'"&A1&"sheet1'!a13"

And =pull(a2) worked ok.

Here's hoping that it's just a google screw up for you.

Antonio Duarte wrote:

Hi, Dave,

The plain old version works fine, but my problem is: I need a VARIABLE
filename.
So, I tryed:

In cell A1 I wrote: D:\temp\[teste2005.xls]

And in Cell A2 I wrote: ="'"&A1&"Junho'!B11"

But all i got in A2 was only the string:
'D:\temp\[teste2005.xls]Junho'!B11 instead the value of the cell.

Can you help?
Tks.

"Dave Peterson" escreveu:

Hmmmm.

I just tried this:

=pull("'C:\temp\[test1.xls]Sheet1'!$A$1")

And it worked fine.

Since you have the string built, maybe you could see what happens with this
plain old version:

="'D:\TMP\[teste2005.xls]Junho'!$B$11"

Does that work?


Antonio Duarte wrote:

Hi, Dave.
Thanks for the information, but for some reason it didn't work.
The situation is:
when I call the pull function this way:
pull("D:\TMP\[teste2005.xls]Junho!$B$11")
(without the " ' " delimiters), the VBA instruction pull = Evaluate(xref)
returns Error
2015 (#VALUE)
and when I call the pull function this way:
pull("'D:\TMP\[teste2005.xls]Junho'!$B$11")
(with the " ' " delimiters), the VBA instruction pull = Evaluate(xref)
returns Error
2023 (#REF)
I just checked, and the conditions a The file acessed really exists under
d:\tmp, and so the WB addressed in the call (Junho!$B$11). When the call is
done, the file is NOT opened.
Do you have any clue on this?
Tks.
Antonio

"Dave Peterson" escreveu:

=indirect() won't work with closed workbooks.

Harlan Grove wrote a User Defined Function that can pull from a closed
workbook:

http://groups.google.co.uk/groups?se...wsranger.co m

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Antonio Duarte wrote:

Does someone has an answer for the question below?

I have a cell (say A1) with the formula:

=HYPERLINK(€ŀœ[d:\tmp\file.xls]sheet!B11")

In the next cell, I would like to see the value that the cell pointed by the
hyperlink has. I tried:

=INDIRECT(A1)

But Excel gives a #REF error in the INDIRECT function.



Does someone have any idea on how can I solve this?

Thanks a lot

Antonio Duarte.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson