Hi:

I've got hyperlink formulas in my worksheet of the following form:

=HYPERLINK("filename.htm", "displaytext")

1. When I try to count the number of hyperlinks in a cell containing
this formula, it gives me zero (0) hyperlinks:

MsgBox ActiveCell.Hyperlinks.Count

2. When I try to assign the hyperlink to a string value, it gives me
"display" instead of "filename.htm":

Dim myLink As String
myLink = ActiveCell.Value

3. When I try to Dim myLink as Hyperlink, I get a zero count of
hyperlinks, and an Error 91, 'Object variable ... not set'

Dim myLink As Hyperlink
myLink = ActiveCell.Value

4. When I use literal hyperlinks in the cell, as opposed to formulas,
and defind myLink as a string, I can work with both literal strings
and with manually-entered hyperlinks in the below code:

Dim myLink As String
' Dim myLink As Hyperlink

MsgBox ActiveCell.Hyperlinks.Count

If ActiveCell.Hyperlinks.Count 0 Then
myLink = ActiveCell.Hyperlinks(1).Address
Else
myLink = ActiveCell.Value
End If

ThisWorkbook.FollowHyperlink myLink

Any help with using the HYPERLINK() formula instead of literal
hyperlinks would be greatly appreciated.

-- Roy Zider

All those hyperlink properties that you're using in code refer to the
Insert|hyperlink variety of hyperlinks (like you've guessed).

Maybe you can parse the formula and pick out the link location from the
formula????

FUBARinSFO wrote:

Hi:

I've got hyperlink formulas in my worksheet of the following form:

=HYPERLINK("filename.htm", "displaytext")

1. When I try to count the number of hyperlinks in a cell containing
this formula, it gives me zero (0) hyperlinks:

MsgBox ActiveCell.Hyperlinks.Count

2. When I try to assign the hyperlink to a string value, it gives me
"display" instead of "filename.htm":

Dim myLink As String
myLink = ActiveCell.Value

3. When I try to Dim myLink as Hyperlink, I get a zero count of
hyperlinks, and an Error 91, 'Object variable ... not set'

Dim myLink As Hyperlink
myLink = ActiveCell.Value

4. When I use literal hyperlinks in the cell, as opposed to formulas,
and defind myLink as a string, I can work with both literal strings
and with manually-entered hyperlinks in the below code:

Dim myLink As String
' Dim myLink As Hyperlink

MsgBox ActiveCell.Hyperlinks.Count

If ActiveCell.Hyperlinks.Count 0 Then
myLink = ActiveCell.Hyperlinks(1).Address
Else
myLink = ActiveCell.Value
End If

ThisWorkbook.FollowHyperlink myLink

Any help with using the HYPERLINK() formula instead of literal
hyperlinks would be greatly appreciated.

-- Roy Zider

--

Dave Peterson

On Nov 17, 8:17*pm, Dave Peterson wrote:
All thosehyperlinkproperties that you're using in code refer to the
Insert|hyperlinkvariety of hyperlinks (like you've guessed).

Maybe you can parse the formula and pick out the link location from the
formula????

Dave:

1. Yes, they are of the type Insert|Hyperlink. I wasn't aware (until
now) that there was a difference. There is no IsHyperlink property or
method for VBA in Excel, afaik, so I depended on
activecell.hyperlinks.count to identify a hyperlink. For reasons not
clear to me, the formula =hyperlink() didn't result in a hyperlink as
far as VBA was concerned -- yet if the user clicked on it with a
mouse, the link was followed and the local file was opened (an html
web page that had been downloaded).

2. Didn't need to parse the formula, as I already used known values to
create the formula in the first place. I didn't include it in the
example here because I didn't want to clutter up the question with
extraneous detail. Here's a sample, tho:

=HYPERLINK("LotDetail.htm_lot_id(eq)"&$H2&".htm",$ H2)

3. The problem I'm trying to solve is to have a column of links that
the user can hotkey to the target. Literal Insert|Hyperlink works OK
for cell content, but as noted the formula does not.

Maybe you could modify your =hyperlink() formula:

=HYPERLINK("LotDetail.htm_lot_id(eq)"&$H2&".htm",$ H2)
to
=HYPERLINK("http://LotDetail.htm_lot_id(eq)"&$H2&".htm",$H2)

But I would think you'd need a real URL:

=HYPERLINK("http://www.something.com/LotDetail.htm_lot_id(eq)"&$H2&".htm",$H2)

But I don't know what you're hyperlinking to.



FUBARinSFO wrote:

On Nov 17, 8:17 pm, Dave Peterson wrote:
All thosehyperlinkproperties that you're using in code refer to the
Insert|hyperlinkvariety of hyperlinks (like you've guessed).

Maybe you can parse the formula and pick out the link location from the
formula????

Dave:

1. Yes, they are of the type Insert|Hyperlink. I wasn't aware (until
now) that there was a difference. There is no IsHyperlink property or
method for VBA in Excel, afaik, so I depended on
activecell.hyperlinks.count to identify a hyperlink. For reasons not
clear to me, the formula =hyperlink() didn't result in a hyperlink as
far as VBA was concerned -- yet if the user clicked on it with a
mouse, the link was followed and the local file was opened (an html
web page that had been downloaded).

2. Didn't need to parse the formula, as I already used known values to
create the formula in the first place. I didn't include it in the
example here because I didn't want to clutter up the question with
extraneous detail. Here's a sample, tho:

=HYPERLINK("LotDetail.htm_lot_id(eq)"&$H2&".htm",$ H2)

3. The problem I'm trying to solve is to have a column of links that
the user can hotkey to the target. Literal Insert|Hyperlink works OK
for cell content, but as noted the formula does not.

--

Dave Peterson

Dave:

It's possible your suggestion may be necessary, but in this case I'm
linking to some local htm files. I'm using just the filename in the
formula, and setting the base url from File|Properties Hyperlink Base
text box. So the base URL looks like this:

file://H:\WIP\Sothebys scrape on MPX at RPM workbench (Mpx)
\Sothebys get lotDetailPages_3\13\2007 6_51_15 PM\Get

and the file name looks like this:

LotDetail.htm_lot_id(eq)3BB6N.htm

(I know -- ugly -- but rather than simplify it and lose the flavor of
a real link, I've presented it in native form).

The fully qualified URL is the contatenation of the two pieces (with a
'\' in between).

-- Roy

I think you'll need to add the upfront info to the =hyperlink() formula.

I would use a helper cell to hold the drive and path. Then when/if it changes,
I could just update one cell to fix the hyperlinks.

Put:
file:////h:\wip\...that longpath that ends with a backslash\
in A1

Then you can use:

=hyperlink($a$1&a2,a2)
where a2 contains the "LotDetail.htm_lot_id(eq)3BB6N.htm" string

(But even that looks like a funny filename to me.)

FUBARinSFO wrote:

Dave:

It's possible your suggestion may be necessary, but in this case I'm
linking to some local htm files. I'm using just the filename in the
formula, and setting the base url from File|Properties Hyperlink Base
text box. So the base URL looks like this:

file://H:\WIP\Sothebys scrape on MPX at RPM workbench (Mpx)
\Sothebys get lotDetailPages_3\13\2007 6_51_15 PM\Get

and the file name looks like this:

LotDetail.htm_lot_id(eq)3BB6N.htm

(I know -- ugly -- but rather than simplify it and lose the flavor of
a real link, I've presented it in native form).

The fully qualified URL is the contatenation of the two pieces (with a
'\' in between).

-- Roy

--

Dave Peterson

Hi Roy:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

This small UDF will return the URL even if the =HYPERLINK() function was used.
--
Gary''s Student - gsnu200814

Hi Gary's Student:

Thanks for the UDF. But as I noted in my reply just now above, I"m
not trying to extract the address, since I've formed it with formula
elements in other cells/columns on the sheet. I'm trying to follow the
link using:

ThisWorkbook.FollowHyperlink myLink

Since the forumula-based link =HYPERLINK() isn't recognized as a
hyperlink, the .FollowHyperlink method fails. I'm at a bit of a loss
at the moment in figuring out what property or method to invoke to
convert the formula into a "hyperlink" as recognized when a Insert |
Hyperlink literal creation method is used.

What I did yesterday to get around this problem was to insert another
colum which contains now just the hyperlink string itself, rather than
the formula. This works for now, but it leaves me with the bad
feeling about the =HYPERLINK() formula itself.

-- Roy

** SOLVED (sort of)**

It turns out that if you don't use the displaytext option (leave the
second argument blank), the formula will still not be recognized as a
hyperlink. But the non-null displaytext argument causes the
ActiveCell.Value property/method to pluck the displaytext string,
rather than the first argument the URL itself. Leaving the second
argument null causes it to pluck the URL instead.

Still don't know what it is that would make the formula be recognized
as a hyperlink by ActiveCell.Hyperlinks.Count, tho.

-- Roy Zider

This doesn't work -- ActiveCell.Hyperlinks.Count produces zero, and
ActiveCell.Value produces "displaytext", not the URL:
=HYPERLINK("filename.htm", "displaytext")

This does work -- ActiveCell.Hyperlinks.Count also produces zero, but
ActiveCell.Value produces "filename.htm" :
=HYPERLINK("filename.htm")