Software

Ask something
Discussion Locked

Hyperlink in Excel Viewed in Browser

0 helpful
8 replies

by kennethaj

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an excel spreadsheet workbook that users view on the web in their browser (Where the spreadsheet is actuall viewed in IE). This spreadsheet contains hyperlinks. Although most users simply save the spreadsheet to their hard drive, others tend to click on the link while viewing the workbook in the browser. The problem is that IE tries to open the link in the same browser window. This normally would not be a problem as long as no changes were made to the sheet (ex: resize a column). If there is a change, IE prompt you to either accept the change, reject the change, or cancel the action. What I need to do is have the link open in another IE window. The formula I use in the cells that contain the hyperlink is "=HYPERLINK("http://www.fedex.com/Tracking?tracknumbers="&A1&","&B1, Track Now)"). This command does not allow for any other parameters. The "hyperlink wizard" in excel does not support this type of formatting. Does anyone know any other way to make a hyperlink in an excel spreadsheet viewed in IE over the web open in another window rather than the current window. I wish that the excel hyperlinks supported HTML so I can use the "target="_blank" but it doesn't. I'll give 500 points to anyone that can make this work reliably.

8 replies

by rewrite

You will need to create a macro and then link a button to the macro. Try this code for the macro:

Sub Link()
ActiveWorkbook.FollowHyperlink Address:= _
"http://www.google.com/", NewWindow:= _
False, AddHistory:=True
Application.WindowState = xlNormal
End Sub

Then create a button in the spreadsheet and link it to that macro. This should open a new window with the url specified (change www.google.com to your desired url).

Cheers.

RW

by kennethaj

A apologize to everyone for not responding lately, I got tied up with projects at work. I'll through what works and award the points this weekend. thanks to everyone for helping out.

by mcspamlist

Set your Print Area and then...

'Save as nonstandard htm to c:\
Application.Goto Reference:="Print_Area"
With ActiveWorkbook.PublishObjects.Add(xlSourcePrintArea, _
"C:\index.htm", "Sheet1", "", _
xlHtmlStatic, "Testing Macro_17302", "")
.Publish (True)
.AutoRepublish = False
End With
' ADD below body tag to open links in new windows
'open your file to get what is on the file in text form
Open "C:\index.htm" For Input As #1
'open your output file as a different file
Open "C:\Hello1.html" For Output As #2
'read information into a string variable to see the original
'loop the input
Do Until EOF(1)
Line Input #1, strInfo
intPosition = InStr(1, strInfo, "", vbTextCompare)
If intPosition <> 0 Then
strNewInfo = strNewInfo & strInfo & "" & Chr(13)
Else
strNewInfo = strNewInfo & Chr(13) & strInfo
End If
Loop
'close the original document
Close #1
'output the information to the new file
Print #2, strNewInfo
'close the new file
Close #2
'replace the old file with the new file
FileCopy "C:\Hello1.html", "C:\index.htm"
'delete the new, maintain the original
Kill "C:\Hello1.html"

This discussion is closed to new replies.