Excel: Create a TOC linked to a Word Doc

If you need to create an Excel document, and you need to refer to certain sections of a Word document, it's possible to create direct links to bookmarks. This means you can click on a cell in Excel and go directly to a section in the Word doc.

Sub ConvertWordBookmarkToHyperLink()

' Converts a Word Table of Contents into direct links to the

' relevant section of the Word document.

' Edward Chan 2022

'

' To use:

' 1. Enter the path and name of the Word Document in Cell B2.

'    Note: If the Word doc is in the same folder as the Excel document then

'    just enter the filename.

' 2. Copy and paste the Table of Contents from Word into A2 onwards.

' 3. In Excel, select the filename and heading titles.

' 4. Run this macro.

'

' If you want the links to have the heading titles,

' copy-paste values of the heading titles over the resultant links.

Dim Rng As Range

Dim WorkRng As Range

Dim FileName As Range

On Error Resume Next

xTitleId = "Select Contents pasted from Word Doc"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

xTitleId = "Select the filename of the Word Doc"

Set FileName = Application.InputBox("Range", xTitleId, WorkRng(1).Address, Type:=8)

For Each Rng In WorkRng

    Rng.Offset(0, 1).Value = FileName.Value & "#" & Rng.Hyperlinks(1).SubAddress

    Application.ActiveSheet.Hyperlinks.Add Rng.Offset(0, 1), Rng.Offset(0, 1).Value

    Rng.Offset(0, 1).Value = Rng.Value

Next

End Sub

NOTE: This is also possible with HTML files, but only if the file is HTTP (not local), since Word strips the # character.

Here is a macro to extract hyperlinks from cells if you just need to do that:

Sub ExtractHyperLink()

' Extracts a hyperlink from a cell

Dim rng As range

Dim workrng As range

On Error Resume Next

xTitleId = "Select Hyperlinks"

Set workrng = Application.Selection

Set workrng = Application.InputBox("Range", xTitleId, workrng.Address, Type:=8)

For Each rng In workrng

    rng.Offset(0, 1).Value = rng.Hyperlinks(1).SubAddress

Next

End Sub