Urgent: Hyperlink Issue

  • Thread starter Thread starter CST
  • Start date Start date
C

CST

Hi All,

I posted this yesterday, but got no response. I don't usually do
re-posts, but this one is urgent. Please see below:

I am having problems with opening a new html window when the xls file
is viewed via a browser. The link seems to want to open within the
same browser window. I want the link to open a new browser window
when clicked. The following snipet of code works as expected when the
xls file is opened via Excel on the local machine, but doesn't when
viewed via the web. This is the code I have in Workbook_Open():

Range("E" & iDetRowNdx & ":E" & iLastRowNdx).Select
With ActiveSheet.UsedRange
For RowNdx = iDetRowNdx To .Rows.Count Step 1
If InStrRev(Range("C" & RowNdx).Value, "Total") Or Range("C" &
RowNdx).Value = "" Then
'Do Nothing
Else
ActiveSheet.Hyperlinks.Add .Range("E" & RowNdx), Address:= _
"http://www.mysite.com/WebReports/Report.asp?ID=" &
Range("G3").Value & "&BusDt=" & Range("A" & RowNdx).Value
End If
Next RowNdx
End With

Is there an alternate way that I can do this? I dynamically build
this spreadsheet, so the values are never the same. As you can see, I
need to use the cell values to determine what I put into the links. I
think I need to do something like Hyperlink.Follow NewWindow:=True,
but that doesn't seem to work.

My second question pertains to opening an SSL link, I get the
following error: Unable to open https://www.mysite.com. A security
problem has occured. Is there a way to get around this? Using just
http, the link opens fine.

Sorry for the repost.
 
CST

There is no option to set a hyperlink to open in a new window. The
workaround options that I've discovered are:

1. Open the html file as a text file and fix up the hyperlinks with a
_blank then save it and re-open in Excel. I won't dwell on this solution
because it's really a bad one.

2. When you create your hyperlinks, don't set the Address property to the
URL, rather set the TextToDisplay property equal to the URL. Set the
Address property to the Range in which the hyperlink sits. Then use the
FollowHyperlink event to issue a ThisWorkbook.FollowHyperlink statement
where you can use the New Window argument to get the desired effect.

Setting the Address property equal to the parent range's address basically
causes the hyperlink to do nothing - except fire the FollowHyperlink event.
The Add method might look like this

ActiveSheet.Hyperlinks.Add .Range("E" & RowNdx), TextToDisplay:= _
"http://www.mysite.com/WebReports/Report.asp?ID=" & _
Range("G3").Value & "&BusDt=" & Range("A" & RowNdx).Value, _
Address:=.Range("E" & RowNdx).Address

And the FollowHyperlink event would look like this

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

ThisWorkbook.FollowHyperlink Target.TextToDisplay, , True

End Sub

You store the URL in the TextToDisplay when you add, then use it in your
FollowHyperlink method to get what you want.

I hope that was the urgent part, because I have no clue on the SSL question.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
 
Hi Dick,

I just wanted to say thank you very much for the response and yes that
was the urgent piece. I took a look at your code and in theory, that
makes complete sense. However, when I try to implement it, I get a
dialog box prompting me "cannot open the specified file." Since I am
using the parent range's address, my guess is that it is trying to open
the worksheet. When I hover over the link it displays this "C:Path of
File\Value of Cell" (e.g., "C:\Temp\-20.91", but I think it should
probably show the URL. This is the code I changed according to your
feedback:

On the Workbook:
Private Sub Workbook_Open()
Range("E" & iDetRowNdx & ":E" & iLastRowNdx).Select
With ActiveSheet.UsedRange
For RowNdx = iDetRowNdx + 1 To .Rows.Count Step 1
If InStrRev(Range("C" & RowNdx).Value, "Total") Or Range("C"
& RowNdx).Value = "" Then
'Do Nothing
Else
ActiveSheet.Hyperlinks.Add .Range("E" & RowNdx),
Address:=.Range("E" & RowNdx),
TextToDisplay:="http://www.mysite.com/WebReports/test_html.asp?ID="
Range("B" & RowNdx).Value

Range("E" & RowNdx).Font.Size = 8
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End If
Next RowNdx
End With
End Sub

And on the Worksheet:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
' MsgBox Target.TextToDisplay
ThisWorkbook.FollowHyperlink Address:=Target.TextToDisplay,
NewWindow:=True
End Sub

Perhaps I am a true noob and I missed something. Would it be possible
if you could take a quick look see? Thanks again for you help.
 
Con Tang

ActiveSheet.Hyperlinks.Add .Range("E" & RowNdx),
Address:=.Range("E" & RowNdx),
TextToDisplay:="http://www.mysite.com/WebReports/test_html.asp?ID="
Range("B" & RowNdx).Value
Click to expand...

Change the Address part to this

Address:=.Range("E" & RowNdx).Address,

You will telling the hyperlink to point to the range that it sits in - which
means it does nothing at all. By omitting the .Address property, it is
assuming you want the .Value property (.Value is the default property of the
Range object.) See if adding the Address property takes care of it.
 
Back
Top