Workbook Cell as Target of Hyperlink Object

[Jay]

Hi all -

I'm trying to place a hyperlink in the ActiveCell with the hyperlink
pointing to a cell on a sheet in a closed workbook. I'm committed to using
the hyperlink object and not the function =Hyperlink(etc...).

The following code fails on the line before End Sub, presumably because the
variable hypAddress violates syntax rules for the "Address:=" argument of the
Hyperlinks.Add method. Any advice about constructing a valid string for the
hypAddress string variable would be appreciated.

Sub hLink()
iw = 7
folderPath = "G:\My Documents\"
fName = "Test.xls"

hypAddress= folderPath & fName & Sheets(iw).Name & "!A1")

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=hypAddress,
TextToDisplay:=iw
End Sub

 

[Joel]

Jay you didn't include enough code for me to beable to duplicate the problem.
So instead, I did a learn macro to insert a hyperlink. See code below. I
can run this code without an errors. You may need to add the
Range("C17").Select line to get your code to work.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"D:\Visual basic\Master.xls", TextToDisplay:="D:\Visual
basic\Master.xls"
Range("C17").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet2!A1", TextToDisplay:="Sheet2!A1"

 

[Gary''s Student]

As an alternative, you can create a hyperlink on the worksheet to just about
anywhere without using the Hyperlinks.Add method. Just put the full address
in a cell and enable the link:

Sub hyper_make()
' gsnuxx
Set r = ActiveCell
If r.Hyperlinks.Count > 0 Then
r.Hyperlinks.Delete
End If
s = "file:///C:\Documents and Settings\Owner\Desktop\Book2.xls#Sheet2!B9"
r.Value = s
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
r.Hyperlinks(1).TextToDisplay = "7"
End Sub

you can get to any drive
any folder
any file
any sheet
any cell

 

[Jay]

Hi Joel and Gary’’s Student –

Thank you both for your quick and information-packed responses. They led
directly to a solution in my application.

I amalgamated the information from both of you to produce a solution to my
immediate problem and a third generic listing (in addition to yours) using
variables that might be useful to others:
----------------------------
Sub make_hypLink()
'One generic method for inserting a hyperlink to a cell on a workbook
'Collaborative Effort: Joel, Gary''s Student, Jay / MS DG Thread 3/18/07.

‘Variables; modify to suit
lnk_display = "Whatever you want"
lnk_folderPath = "C:\Documents and Settings\<Owner>\Desktop\"
lnk_fName = "Book2.xls"
lnk_sheetName = "Invoices" 'must be the friendly sheetname (on the worksheet
Tab)
lnk_Range = "A9"

hypaddress = "file:///" & lnk_folderPath & lnk_fName
hypSubAddress = lnk_sheetName & "!" & lnk_Range
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=hypaddress,
SubAddress:=hypSubAddress, _
TextToDisplay:=lnk_display
End Sub
-----------------------------

1. Joel, your code to produce the hyperlink to a worksheet cell worked
perfectly when I referred to the Address and SubAddress as separate
components of the link; thanks for that information. I was stuck on
stringing the Address and SubAddress together as a single Address argument
(like a fly in a window...).

2. Gary’’s Student: The fact that the general pathname needs a prefix of
“file:/// “ in the Address argument of the Hyperlinks.Add method makes or
breaks the procedure above; thanks for that information.

In addition, the utility you provided is a clever alternative and I’d like
to find out why I couldn’t make it work completely. It would run
(WinXP/XL2003), but the “…{Enter}…†statement would execute without
converting the cell entry into a blue hyperlink. Also, the
r.Hyperlinks(1).TextToDisplay statement produced a ‘Subscript out of range’
error. Finally, although it doesn’t impact the utility’s process, the
“...{F2}...†statement would open the Object Browser (any way to stop this?).
Any advice on what might be causing these behaviors would be appreciated as
you have provided us with a very useful utility.

 

[Gary''s Student]

Using SendKeys can be a little tricky.

1. For example, you can't run hyper_make from a VBA window. It must be run
from the worksheet.

2. Once the DoEvents is executed, control will go back to the worksheet and

F2
ENTER

will be sent to the Active (Selected) cell (which should have the hyperlink
text in it). This should "enable" the hyperlink (make it "clickable").

3. Control then goes back to the sub and the
r.Hyperlinks(1).TextToDisplay = "7"
should now work because a real, click-able, link should be there.


If step 2 fails, then the hyperlink will still not be enabled and step 3
will also fail.

 

[Jay]

Bingo! It doesn't get any better than that solution (no work involved, at
least not on my part). I should have known to run it from the worksheet
side...

Thank you very much; I've added your utility to my quiver of hyperlink tools
and will think of you and Joel whenever I'm working with hyperlinks.

 

[Gary''s Student]

You are very welcome. Many people have an irrational fear of SendKeys
because they don't understand the sequential interplay of VBA and the
worksheet.