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.

=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

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

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

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

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

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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

Hi, Dave:

I really appreciate and thank for your help and the precious time you are
spending with this problem.
I checked your notes, corrected the points you showed (in fact, there was
an error on that point), but the problem still persists.
The pull function keeps returning the errors 2015 or 2023 (as earlier
posted) in the command line:
pull = Evaluate(xref)
Executing the code in the single step mode, I see that the values
assigned to the pull variable after this point we

"Error 2023" or "Error 2015" (depending on the parameter passed)

Then, I did two more tests, simplifying the command line as shown below:
pull = Evaluate("'d:\tmp\[teste2005.xls]Junho'!$B$10")
and also
pull = Evaluate("d:\tmp\[teste2005.xls]Junho!$B$10")

getting the same errors (note that doing so, I 'got rid' of the xref
variable and any 'malfunction' it could lead to).

May be, the problem has been caused by any wrong configuration item in my
Excel. Is it possible? If so, do you have any clue?

Tks again.

"Dave Peterson" escreveu:

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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

I don't think it's a configuration problem. Harlan's UDF works fine for me.

I'd double check the values and the concatenated string--just to make sure there
is not typing mistake.

In fact, I'd create a simple file: "c:\test.xls" with a worksheet "sheet1" and
put something in A1 ("asdf").

And see if the UDF works in that test case.

Maybe it'll help find what's going wrong.

Antonio Duarte wrote:

Hi, Dave:

I really appreciate and thank for your help and the precious time you are
spending with this problem.
I checked your notes, corrected the points you showed (in fact, there was
an error on that point), but the problem still persists.
The pull function keeps returning the errors 2015 or 2023 (as earlier
posted) in the command line:
pull = Evaluate(xref)
Executing the code in the single step mode, I see that the values
assigned to the pull variable after this point we

"Error 2023" or "Error 2015" (depending on the parameter passed)

Then, I did two more tests, simplifying the command line as shown below:
pull = Evaluate("'d:\tmp\[teste2005.xls]Junho'!$B$10")
and also
pull = Evaluate("d:\tmp\[teste2005.xls]Junho!$B$10")

getting the same errors (note that doing so, I 'got rid' of the xref
variable and any 'malfunction' it could lead to).

May be, the problem has been caused by any wrong configuration item in my
Excel. Is it possible? If so, do you have any clue?

Tks again.

"Dave Peterson" escreveu:

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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


--

Dave Peterson

Hi, Dave:

I made a 'triple-check', and every byte of code and data is absolutelly
right, but the call to the Evaluate function still doesn't work.

Doing other tests, I got a positive result with this code:

-------------------------- start of code
---------------------------------------------
Sub test()
GetValuesFromAClosedWorkbook "D:\TMP", "teste2005.xls", "Junho", "B11",
"A3"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName as String, cellRange As String, cellDest
As String)
With ActiveSheet.Range(cellDest)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
----------------------------- end of code
----------------------------------------

and in my WB, I just called the Macro named test()

BUT.....

If I change "Sub test()" to "Function test()" (of course, changing also the
'end' command accordingly) and try to use it in cell A5 in the expression:
"=test()", the code stops working. IT ONLY WORKS WHEN 'TEST()' IS A MACRO,
AND NOT WHEN IT BECAMES A FUNCTION!!!!!
Supposing we give up with the pull function (for me, I reached a deadend
with it),
I have two new probems if I try to explore the macro solution:
1) How can I set parameters to a macro? Once I put parameters in the
definition of 'Sub test()' it doesn't appear to my WB as a macro anymore.
2) How can I build a function (with parameters) that calls the macro? In
fact, I tryed that, but the function calling a macro doesn't work, too...

Tks.
"Dave Peterson" escreveu:

I don't think it's a configuration problem. Harlan's UDF works fine for me.

I'd double check the values and the concatenated string--just to make sure there
is not typing mistake.

In fact, I'd create a simple file: "c:\test.xls" with a worksheet "sheet1" and
put something in A1 ("asdf").

And see if the UDF works in that test case.

Maybe it'll help find what's going wrong.

Antonio Duarte wrote:

Hi, Dave:

I really appreciate and thank for your help and the precious time you are
spending with this problem.
I checked your notes, corrected the points you showed (in fact, there was
an error on that point), but the problem still persists.
The pull function keeps returning the errors 2015 or 2023 (as earlier
posted) in the command line:
pull = Evaluate(xref)
Executing the code in the single step mode, I see that the values
assigned to the pull variable after this point we

"Error 2023" or "Error 2015" (depending on the parameter passed)

Then, I did two more tests, simplifying the command line as shown below:
pull = Evaluate("'d:\tmp\[teste2005.xls]Junho'!$B$10")
and also
pull = Evaluate("d:\tmp\[teste2005.xls]Junho!$B$10")

getting the same errors (note that doing so, I 'got rid' of the xref
variable and any 'malfunction' it could lead to).

May be, the problem has been caused by any wrong configuration item in my
Excel. Is it possible? If so, do you have any clue?

Tks again.

"Dave Peterson" escreveu:

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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


--

Dave Peterson

If you think you're going to fool excel into letting you call that sub from a
UDF that is in a cell, then that won't work.

If you look at Harlan's code again, you'll notice that he actually opens up a
second instance of excel. That's one of the reasons it works.

You didn't say what happened when you tried the more basic test.

Antonio Duarte wrote:

Hi, Dave:

I made a 'triple-check', and every byte of code and data is absolutelly
right, but the call to the Evaluate function still doesn't work.

Doing other tests, I got a positive result with this code:

-------------------------- start of code
---------------------------------------------
Sub test()
GetValuesFromAClosedWorkbook "D:\TMP", "teste2005.xls", "Junho", "B11",
"A3"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName as String, cellRange As String, cellDest
As String)
With ActiveSheet.Range(cellDest)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
----------------------------- end of code
----------------------------------------

and in my WB, I just called the Macro named test()

BUT.....

If I change "Sub test()" to "Function test()" (of course, changing also the
'end' command accordingly) and try to use it in cell A5 in the expression:
"=test()", the code stops working. IT ONLY WORKS WHEN 'TEST()' IS A MACRO,
AND NOT WHEN IT BECAMES A FUNCTION!!!!!
Supposing we give up with the pull function (for me, I reached a deadend
with it),
I have two new probems if I try to explore the macro solution:
1) How can I set parameters to a macro? Once I put parameters in the
definition of 'Sub test()' it doesn't appear to my WB as a macro anymore.
2) How can I build a function (with parameters) that calls the macro? In
fact, I tryed that, but the function calling a macro doesn't work, too...

Tks.
"Dave Peterson" escreveu:

I don't think it's a configuration problem. Harlan's UDF works fine for me.

I'd double check the values and the concatenated string--just to make sure there
is not typing mistake.

In fact, I'd create a simple file: "c:\test.xls" with a worksheet "sheet1" and
put something in A1 ("asdf").

And see if the UDF works in that test case.

Maybe it'll help find what's going wrong.

Antonio Duarte wrote:

Hi, Dave:

I really appreciate and thank for your help and the precious time you are
spending with this problem.
I checked your notes, corrected the points you showed (in fact, there was
an error on that point), but the problem still persists.
The pull function keeps returning the errors 2015 or 2023 (as earlier
posted) in the command line:
pull = Evaluate(xref)
Executing the code in the single step mode, I see that the values
assigned to the pull variable after this point we

"Error 2023" or "Error 2015" (depending on the parameter passed)

Then, I did two more tests, simplifying the command line as shown below:
pull = Evaluate("'d:\tmp\[teste2005.xls]Junho'!$B$10")
and also
pull = Evaluate("d:\tmp\[teste2005.xls]Junho!$B$10")

getting the same errors (note that doing so, I 'got rid' of the xref
variable and any 'malfunction' it could lead to).

May be, the problem has been caused by any wrong configuration item in my
Excel. Is it possible? If so, do you have any clue?

Tks again.

"Dave Peterson" escreveu:

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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


--

Dave Peterson


--

Dave Peterson

For sure I answered, and it was not the expected answer: even with the more
basic test, the call to the Evaluate function still behaves the same way.
Just note the following: the command line that calls the Evaluate is in the
first half of the code, BEFORE the CreateObject("Excel.Application") is
called (it is done only some lines below...).
Before calling the Evaluate, Harlan's code simply works on strings,
preparing the parameter 'xref' that will be used by Evaluate, and NOTHING
ELSE!
As reported before, 'pull = Evaluate(xref)' returns "Error 2023" or "Error
2015" (depending either the " ' " delimiter is used or not in the composition
of the xref parameter).
Any clue?
TKS!

"Dave Peterson" escreveu:

If you think you're going to fool excel into letting you call that sub from a
UDF that is in a cell, then that won't work.

If you look at Harlan's code again, you'll notice that he actually opens up a
second instance of excel. That's one of the reasons it works.

You didn't say what happened when you tried the more basic test.

Antonio Duarte wrote:

Hi, Dave:

I made a 'triple-check', and every byte of code and data is absolutelly
right, but the call to the Evaluate function still doesn't work.

Doing other tests, I got a positive result with this code:

-------------------------- start of code
---------------------------------------------
Sub test()
GetValuesFromAClosedWorkbook "D:\TMP", "teste2005.xls", "Junho", "B11",
"A3"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName as String, cellRange As String, cellDest
As String)
With ActiveSheet.Range(cellDest)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
----------------------------- end of code
----------------------------------------

and in my WB, I just called the Macro named test()

BUT.....

If I change "Sub test()" to "Function test()" (of course, changing also the
'end' command accordingly) and try to use it in cell A5 in the expression:
"=test()", the code stops working. IT ONLY WORKS WHEN 'TEST()' IS A MACRO,
AND NOT WHEN IT BECAMES A FUNCTION!!!!!
Supposing we give up with the pull function (for me, I reached a deadend
with it),
I have two new probems if I try to explore the macro solution:
1) How can I set parameters to a macro? Once I put parameters in the
definition of 'Sub test()' it doesn't appear to my WB as a macro anymore.
2) How can I build a function (with parameters) that calls the macro? In
fact, I tryed that, but the function calling a macro doesn't work, too...

Tks.
"Dave Peterson" escreveu:

I don't think it's a configuration problem. Harlan's UDF works fine for me.

I'd double check the values and the concatenated string--just to make sure there
is not typing mistake.

In fact, I'd create a simple file: "c:\test.xls" with a worksheet "sheet1" and
put something in A1 ("asdf").

And see if the UDF works in that test case.

Maybe it'll help find what's going wrong.

Antonio Duarte wrote:

Hi, Dave:

I really appreciate and thank for your help and the precious time you are
spending with this problem.
I checked your notes, corrected the points you showed (in fact, there was
an error on that point), but the problem still persists.
The pull function keeps returning the errors 2015 or 2023 (as earlier
posted) in the command line:
pull = Evaluate(xref)
Executing the code in the single step mode, I see that the values
assigned to the pull variable after this point we

"Error 2023" or "Error 2015" (depending on the parameter passed)

Then, I did two more tests, simplifying the command line as shown below:
pull = Evaluate("'d:\tmp\[teste2005.xls]Junho'!$B$10")
and also
pull = Evaluate("d:\tmp\[teste2005.xls]Junho!$B$10")

getting the same errors (note that doing so, I 'got rid' of the xref
variable and any 'malfunction' it could lead to).

May be, the problem has been caused by any wrong configuration item in my
Excel. Is it possible? If so, do you have any clue?

Tks again.

"Dave Peterson" escreveu:

ps. Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

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


--

Dave Peterson


--

Dave Peterson

One of the things that first: pull = Evaluate(xref) does is to check to see
what it evalates to. If it's not an error, then it evaluates ok and that means
the workbook is open.

If it returns an error, then the workbook is closed. I got error 2023 in my
tests with the workbook closed and this in the cell:

''C:\My Documents\excel\[Book2.xls]Sheet1'!$A$1

Note the first single quote is used to indicate that the cell is text. (Excel
will use this prefixcharacter and that first one isn't part of the value in the
cell.



Antonio Duarte wrote:

For sure I answered, and it was not the expected answer: even with the more
basic test, the call to the Evaluate function still behaves the same way.
Just note the following: the command line that calls the Evaluate is in the
first half of the code, BEFORE the CreateObject("Excel.Application") is
called (it is done only some lines below...).
Before calling the Evaluate, Harlan's code simply works on strings,
preparing the parameter 'xref' that will be used by Evaluate, and NOTHING
ELSE!
As reported before, 'pull = Evaluate(xref)' returns "Error 2023" or "Error
2015" (depending either the " ' " delimiter is used or not in the composition
of the xref parameter).
Any clue?
TKS!

<<snipped

Sorry! My fault!
I thought the problem was due the error returned by the Evaluate function
call, so when executing the code step by step (using the VBA debbuger), I
always stopped at that point. Now, reading your newest considerations, I
understood and let the code run freely. Result: everything runs fine!
I really appreciate your help and patience!
Thanks.

"Dave Peterson" escreveu:

One of the things that first: pull = Evaluate(xref) does is to check to see
what it evalates to. If it's not an error, then it evaluates ok and that means
the workbook is open.

If it returns an error, then the workbook is closed. I got error 2023 in my
tests with the workbook closed and this in the cell:

''C:\My Documents\excel\[Book2.xls]Sheet1'!$A$1

Note the first single quote is used to indicate that the cell is text. (Excel
will use this prefixcharacter and that first one isn't part of the value in the
cell.



Antonio Duarte wrote:

For sure I answered, and it was not the expected answer: even with the more
basic test, the call to the Evaluate function still behaves the same way.
Just note the following: the command line that calls the Evaluate is in the
first half of the code, BEFORE the CreateObject("Excel.Application") is
called (it is done only some lines below...).
Before calling the Evaluate, Harlan's code simply works on strings,
preparing the parameter 'xref' that will be used by Evaluate, and NOTHING
ELSE!
As reported before, 'pull = Evaluate(xref)' returns "Error 2023" or "Error
2015" (depending either the " ' " delimiter is used or not in the composition
of the xref parameter).
Any clue?
TKS!

<<snipped

Whew! Glad you got it worked out.


Antonio Duarte wrote:

Sorry! My fault!
I thought the problem was due the error returned by the Evaluate function
call, so when executing the code step by step (using the VBA debbuger), I
always stopped at that point. Now, reading your newest considerations, I
understood and let the code run freely. Result: everything runs fine!
I really appreciate your help and patience!
Thanks.

"Dave Peterson" escreveu:

One of the things that first: pull = Evaluate(xref) does is to check to see
what it evalates to. If it's not an error, then it evaluates ok and that means
the workbook is open.

If it returns an error, then the workbook is closed. I got error 2023 in my
tests with the workbook closed and this in the cell:

''C:\My Documents\excel\[Book2.xls]Sheet1'!$A$1

Note the first single quote is used to indicate that the cell is text. (Excel
will use this prefixcharacter and that first one isn't part of the value in the
cell.



Antonio Duarte wrote:

For sure I answered, and it was not the expected answer: even with the more
basic test, the call to the Evaluate function still behaves the same way.
Just note the following: the command line that calls the Evaluate is in the
first half of the code, BEFORE the CreateObject("Excel.Application") is
called (it is done only some lines below...).
Before calling the Evaluate, Harlan's code simply works on strings,
preparing the parameter 'xref' that will be used by Evaluate, and NOTHING
ELSE!
As reported before, 'pull = Evaluate(xref)' returns "Error 2023" or "Error
2015" (depending either the " ' " delimiter is used or not in the composition
of the xref parameter).
Any clue?
TKS!

<<snipped


--

Dave Peterson