Excel

Insert a hyperlink to a Word Bookmark

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Killian

Description:

Allows the user to insert a hyperlink in the active cell, chosen from a list of all the bookmarks in a nominated Word document. 

Discussion:

While Excel has a native "Insert Hyperlink" feature, it only allows a file path to other valid document types (such as a Word doc). And it's "Bookmarks" option only allows links to sheets, cells and named ranges within the active workbook. This example provides a similar interface to select bookmarks in a nominated Word document, using the HYPERLINK worksheet formula so can be used for creating linked references to different sections of a word document(s). 

Code:

instructions for use 

			
 '####################################################
 '### Code for standard module
 '####################################################
Option Explicit 
 
Sub InsertHyperLinkToWordRange() 
     
    Dim appWord As Word.Application 
    Dim docTarget As Word.Document 
    Dim objBookmark As Word.Bookmark 
    Dim varWordFile As Variant 
     
     'Get path to Word document
    varWordFile = Application.GetOpenFilename( _ 
    fileFilter:="Word Documents (*.doc), *.doc") 
     
    If Not varWordFile = False Then 
         'create a new instance of Word
        Set appWord = New Word.Application 
        With appWord 
            .Visible = False 
            Set docTarget = .Documents.Open(varWordFile) 
             'add all the target documents bookmark names to the listbox
            Load frmInsertWordHL 
            frmInsertWordHL.lblDocPath.Caption = docTarget.FullName 
            For Each objBookmark In docTarget.Bookmarks 
                frmInsertWordHL.lstBookmarks.AddItem objBookmark.Name 
            Next 
             'close the document and clean up
            docTarget.Close False 
            .Quit 
            Set docTarget = Nothing 
            Set appWord = Nothing 
             
             'show the form
            frmInsertWordHL.Show 
        End With 
    Else 
         'if GetOpenFilename dilaog was closed with no selected file
        MsgBox "No file was chosen", vbExclamation, "Insert HyperLink - File check" 
    End If 
     
End Sub 
 '####################################################
 '### Code for standard module - END
 '####################################################
 
 '####################################################
 '### Code for userform
 '####################################################
Option Explicit 
 
Private Sub lstBookmarks_Click() 
     'set the default display text for the hyperlink as the bookmark name
     'the user can then choose to overwrite this
    txtDisplayText = lstBookmarks.Value 
End Sub 
 
Private Sub cmdInsert_Click() 
     
     'set the active cell formula by building a string using the HYPERLINK syntax
    ActiveCell.Formula = "=HYPERLINK(" & """" & "[" & lblDocPath.Caption & _ 
    "]" & lstBookmarks.Value & """" & "," & """" & "Link to " & txtDisplayText & _ 
    """" & ")" 
     
    Unload Me 
     
End Sub 
 
Private Sub cmdCancel_Click() 
    Unload Me 
End Sub 
 '####################################################
 '### Code for userform - END
 '####################################################

How to use:

  1. In a new workbook, Alt + F11 to enter the VBE
  2. From VBE>Tools>References, add a reference to the Microsoft Word Object Library
  3. Choose Insert>Module
  4. Paste the section of code marked 'Code for standard module' into the code pane
  5. Choose Insert>UserForm
  6. Change the "Name" property of the userform to 'frmInsertWordHL'
  7. Choose View>Toolbox
  8. Using the Toolbox menu:
  9. add a listbox to the userform - change it's name property to 'lstBookmarks'
  10. add a textbox to the userform - change it's name property to 'txtDisplayText'
  11. add a commandbutton to the userform - change it's name property to 'cmdInsert'
  12. add a commandbutton to the userform - change it's name property to 'cmdCancel'
  13. Paste the section of code marked 'Code for userform' into the code pane
  14. Press Alt + Q to close the VBE and save the workbook
  15. Open Word and create a test document that contains at least one bookmark
  16. Save the test document and close Word
 

Test the code:

  1. Press Alt + F8, select 'InsertHyperLinkToWordRange', click Run
  2. Word's FileOpen dialog box will be displayed, navigate to your test Word document and click 'Open'
  3. The userform will be displayed, select a bookmark from the listbox
  4. The name displayed in the hyperlink will default to the bookmark name - this can be changed in the text field below the listbox
  5. Click 'Insert'
  6. Click on the new hyperlink - this should link to the Word document and display the bookmarked range selected
 

Sample File:

InsertHyperLinkToWordBookMark.zip 17.93KB 

Approved by mdmackillop


This entry has been viewed 154 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express