Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent: Hyperlink Issue
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent: Hyperlink Issue
CST
There is no option to set a hyperlink to open in a new window. The workaround options that I've discovered a 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. "CST" wrote in message m... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent: Hyperlink Issue
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent: Hyperlink Issue
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 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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird issue with very simple line chart - urgent | Excel Discussion (Misc queries) | |||
Please help -- URGENT -- need to resolve the issue before submittingmy project | Excel Discussion (Misc queries) | |||
Hyperlink Help Urgent | Excel Worksheet Functions | |||
Hyperlink Help Urgent | Excel Discussion (Misc queries) | |||
EXCEL Hyperlink to Charts Questions.. URGENT.. =( | Excel Discussion (Misc queries) |