Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Getting values from hyperlink
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. |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Importing Data from an Access Database Including a Hyperlink Colum | Excel Discussion (Misc queries) | |||
Importing Access File with Hyperlink | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Removing hyperlink | Excel Discussion (Misc queries) |