How can I create a hyperlink to an HTML file's specific sections within Excel?

I have a HTML file which includes anchors within it. I'm able to get to a specific section via a browser if I specify the anchor name, so when I write
file://<html_path>/<html_file>.html#<anchor_name>
in my browser's address box, the file opens and scrolls to the correct section.

If I place the same link into a HYPERLINK function in Excel, the link just opens the web page, trimming the # part, and doesn't scroll to the specified section. If I place a link to a web page with an anchor part in the HYPERLINK function, it seems to work correctly, so http:// links with # won't be trimmed and still work.

Searching Google returns this forum post and several KB articles which state the hash can't be used in a file name within office although it's a valid file name character. The same page states this was resolved in Excel 2010 but I'm using the 2010 version and it's apparently still an issue.

Is there a workaround to this? I intend to link every row to a different section in the target file, and there are 1000+ rows, so the option to break the original html file to multiple or generate separate URLs to each section and link to the instead would create a clutter of files.

Solution 1:

Try this. For each row, you'll need two columns. The first will contain the name of your bookmark for each row. The second will contain a hyperlink, pointing to itself.

To insert self-referencing hyperlinks for many rows, run a macro like this:

Sub AddHyperlinksPointingToOwnCell()

   For i = 1 To 100

   Range("A" & i).Select
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
       "Sheet1!A" & i, TextToDisplay:="Click Here!"

   ActiveCell.Offset(1, 0).Select

   Next i

End Sub

Now you need a sub to open the browser to the correct location. Edit this so the file and browser paths are correct:

Sub GoToBookmark()

    Dim ThisRow As Integer
    Dim ThisCol As Integer
    Dim BookmarkName As String

    ThisRow = ActiveCell.Row
    ThisCol = ActiveCell.Column
    BookmarkName = Cells(ThisRow, ThisCol - 1).Value
        'This is looking in the cell to the left for the bookmark name

    If BookmarkName = "" Then
        Exit Sub
    End If

    Shell "C:\Program Files\Internet Explorer\IEXPLORE.EXE " & _
        "C:\PathRoot\Folder\filename.html#" & BookmarkName & ", vbNormalFocus"

End Sub

Finally you'll need to add a Worksheet_FollowHyperlink event for the sheet containing your list:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Run ("GoToBookmark")
End Sub

Hopefully that should do it!