8

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.

4
  • I cannot make this an answer as I can't test right now but have you tested using encoding so using '%23' instead of '#'
    – HTDutchy
    Jan 10, 2013 at 10:36
  • I just checked this, it doesn't seem to work. Excel pops an error message "Cannot open the file specified"
    – JohnoBoy
    Jan 10, 2013 at 12:38
  • @terdon 'Pound sign/key' is a valid name for the # character, see the WikiPedia article, also notice the forum post and the KB articles linked to it use the same name
    – JohnoBoy
    Jan 14, 2013 at 6:31
  • @JohnoBoy, so it is. I stand corrected, sorry. Feel free to roll back my edit.
    – terdon
    Jan 14, 2013 at 11:19

4 Answers 4

2
+25

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!

2
  • @JohnoBoy - did this work for you? Jun 23, 2015 at 19:50
  • This was long ago, I believe I gave up on the hyperlinks altogether.
    – JohnoBoy
    Jun 23, 2015 at 19:53
0

Here is another solution for you, if you don't want to use VBA coding you can still use the Excel function "hyperlink". Here is a step by step guide to do it.

online file : Step 1: let's say your file name is : http://en.wikipedia.org/wiki/Intel_Core Step 2: now add your anchor names in A1:A4
Core_Duo
Core_Solo
Core_2_Duo
Core_i7
Step 3: in cell B2 enter this :

=HYPERLINK("http://en.wikipedia.org/wiki/Core_i3#"&A2,"A2")  

Step 4: click & drag to apply for the rest of the cells
Tested working!!!

2
  • 1
    Unfortunately kamalam, the original poster was looking to do this for files hosted locally on the C: drive. If you try this again using file://C:/ this doesn't work. It's a shame because it would be much easier if that worked!
    – Andi Mohr
    Jan 18, 2013 at 9:58
  • Also, this doesn't even work on the web
    – Gerrat
    Aug 9, 2013 at 15:13
-1

If i understund your question well here is a work arround Source = Here WORKAROUND To work around this problem, use one of the following methods. Method 1

Rename the file with a name that does not include the pound sign, and then edit or recreate the hyperlink.

For more information about how to rename a file in Windows, see your Windows printed documentation or online Help.

To edit the hyperlink, follow these steps:

For Office 2003 and earlier versions of Office: Right-click the cell that contains the problem hyperlink. On the shortcut menu that appears, point to Hyperlink, and click Edit Hyperlink. In the Type the file or Web page name box, type the name of the file that you are linking to. If you do not know the name of the file, click File and browse to the file. Click OK.

For Office 2007 and Office 2010 programs: Right-click the cell that contains the problem hyperlink, and then point to Edit Hyperlink. In the Address box, type the name of the file that you are linking to. If you do not know the name of the file, use the search tools on the Look in... line to locate the file. Select the file, and then click OK. Method 2

Use the Paste as Hyperlink command:

For Office 2003 and earlier versions: Open the document that you are trying to link to (which contains the # sign in the name). Copy the cell that you want to link to. In your document, on the Edit menu, click Paste as Hyperlink.

For Office 2007 and Office 2010 programs: Open the document that you are trying to link to. This is the document that contains the # sign in the name. Copy the cell that you want to link to. In your document, click the cell in which you want the hyperlink to appear. On the Home tab, click the arrow below Paste in the Clipboard group, and then click Paste as Hyperlink

if you are using VBA code this line may be of help I tested it.it's working in both chrome and firefox
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= "C:\path\to\your\file.htm", SubAddress:="nameOfYourAnchor", TextToDisplay:= "text to display")

here nameOfYourAnchor w/o the # Sym for example if your link is C:/Users/[User]/Desktop/Intel Core - Wikipedia, the free encyclopedia.htm#Core_i3 your code will be (ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= "C:/Users/User/Desktop/Intel Core - Wikipedia, the free encyclopedia.htm", SubAddress:="Core_i3", TextToDisplay:= "text to display")
hope this helped.

3
  • The problem is, the file doesn't include a pound sign in its name. I use the pound sign to link to a specific section in the file
    – JohnoBoy
    Jan 13, 2013 at 6:53
  • 2
    have a look at the last two paragraphs.
    – kamalam
    Jan 13, 2013 at 19:31
  • @kamalam, on the surface this is a very neat solution. However I can't get this code to work? Can you post the full sub that works for you please?
    – Andi Mohr
    Jan 16, 2013 at 16:13
-1

The following generally works on Windows 7 with Excel 2007. The file is collocated with the spreadsheet. The cell contents:

=HYPERLINK("dump_faults_current_leg_bite.htm#BiteRec_000", "BITE record 1/35")

There some desktop PCs here that are having trouble with this link, however, getting "Cannot open specified file" error.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .