Problem using =HYPERLINK() formula rather than literal hyperlinks incells

[FUBARinSFO]

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]

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????

 

[Gary''s Student]

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.

 

[FUBARinSFO]

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.

 

[FUBARinSFO]

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

 

[FUBARinSFO]

** 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")

 

[Dave Peterson]

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]

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]

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]

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.)

Dave:

This was actually one of my earlier test cases. I tried it again, but
failed with the debug error when it has displaytext field non-null,
and isn't recognized as a hyperlink.

At this point, the conclusion is that the =HYPERLINK() formula will
work, but you have to leave the displaytext argument null. Very
annoying. No idea why this should be the case.

Thanks for your help.

-- Roy Zider

 

[Dave Peterson]

Do you mean:

=hyperlink(...,"")
or
=hyperlink(...)

I've never seen a case where I had to leave that second argument empty.

 

[FUBARinSFO]

Do you mean:

=hyperlink(...,"")
or
=hyperlink(...)

I've never seen a case where I had to leave that second argument empty.

Dave:

What I've used is your second case:
=hyperlink(...)

I didn't try the first case =hyperlink(...,"") Sorry if my
description was confusing.

-- Roy

 

[Dave Peterson]

I still don't understand why you would have to leave that second argument
empty. It's just a displayed value. It shouldn't cause any harm.

 

[FUBARinSFO]

I still don't understand why you would have to leave that second argument
empty.  It's just a displayed value.  It shouldn't cause any harm.

Either do I, Dave. Beats me, too.

-- Roy