none
How to replace text in bookmark in Word with Excel VBA. RRS feed

  • Question

  • I have some code that replaces text at a Word bookmark with a blank so new text can be copied from Excel to Word without removing the bookmark or adding to the text already there. On the line,

        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range

    I get the 'error 13 Type mismatch' error. Here is the code:

    Sub UpdateBookmark(BookmarkToUpdate As String, TestToUse As String)
        Dim BMRange As Range
        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
        BMRange.Text = TextToUse
        ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
    End Sub

    The subroutine is run from other code like this:

        UpdateBookmark "B4", ""
        Range("B4").Copy
        wrdApp.ActiveDocument.Bookmarks("B4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False 'Clears clipboard

    I am copying data from cells in Excel to bookmarks in word.  What do I need to change in the UpdateBookmark subroutine to fix the error. I thought Range is the proper use here.  I have the reference libraries in VBA set up for Excel and Word.
    Wednesday, June 10, 2015 5:09 PM

Answers

  • Hi,

    for the variable BMRange, you dim it as a Excel range, which should be a Word.range for a bookmark.You may dim the BMPRange this

    Dim BMRange As Word.Range

    And I have made a test to update the bookmark for a word document in an Excel file, it works fine.

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 1, 2015 4:42 PM
    Moderator

All replies

  • Hi,
    I have tested the UpdateBookmark sub, it could replace the text for the bookmark in the word.Since we couldn’t reproduce your issue. Could you please share a sample that could appear this problem to fix it if it is convenient.

    Best Regards,
    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 12, 2015 5:12 PM
    Moderator
  • Here is the complete code:

    Option Explicit
    Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
    Dim fDialog As FileDialog
        On Error GoTo err_Handler
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            .Title = strTitle
            .AllowMultiSelect = False
            .Filters.Clear
    '        If bExcel Then
    '            .Filters.Add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
    '        Else
                .Filters.Add "Word documents", "*.doc,*.docx,*.docm"
    '        End If
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then GoTo err_Handler:
            BrowseForFile = fDialog.SelectedItems.Item(1)
        End With
    lbl_Exit:
        Exit Function
    err_Handler:
        BrowseForFile = vbNullString
        Resume lbl_Exit
    End Function
    Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
        Dim BMRange As Range
        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
        BMRange.Text = TextToUse
        ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
    End Sub
    Sub sbVBA_To_Open_Word_FileDialog()
    Dim strWordDoc As String
    Dim objWord As Object
    Dim wrdApp As Object
    Dim wrdDoc As Object
    
        strWordDoc = BrowseForFile("Select Word Document", True)
        If strWordDoc = "" Then
            MsgBox "No file selected."
            GoTo lbl_Exit
        Else
            On Error Resume Next
            Set wrdApp = GetObject(, "Word.Application")
            If Err Then
                Set wrdApp = CreateObject("Word.Application")
            End If
            On Error GoTo 0
            Set wrdDoc = wrdApp.Documents.Open(Filename:=strWordDoc)
            wrdApp.Visible = True
        End If
    
    
        UpdateBookmark "B4", ""
        Range("B4").Copy
        wrdApp.ActiveDocument.Bookmarks("B4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False 'Clears clipboard
        
        UpdateBookmark "B6", ""
        Range("B6").Copy
        wrdApp.ActiveDocument.Bookmarks("B6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "B13", ""
        Range("B13").Copy
        wrdApp.ActiveDocument.Bookmarks("B13").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "C4", ""
        Range("C4").Copy
        wrdApp.ActiveDocument.Bookmarks("C4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "C6", ""
        Range("C6").Copy
        wrdApp.ActiveDocument.Bookmarks("C6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "C12", ""
        Range("C12").Copy
        wrdApp.ActiveDocument.Bookmarks("C12").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "C13", ""
        Range("C13").Copy
        wrdApp.ActiveDocument.Bookmarks("C13").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "D4", ""
        Range("D4").Copy
        wrdApp.ActiveDocument.Bookmarks("D4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
    '    Range("D5").Copy
    '    wrdApp.ActiveDocument.Bookmarks("D5").Select
    '    wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
    ' Placement:=wdInLine, DisplayAsIcon:=False
    '    Application.CutCopyMode = False
        
        UpdateBookmark "D6", ""
        Range("D6").Copy
        wrdApp.ActiveDocument.Bookmarks("D6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "D9", ""
        Range("D9").Copy
        wrdApp.ActiveDocument.Bookmarks("D9").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
           
        UpdateBookmark "D12", ""
        Range("D12").Copy
        wrdApp.ActiveDocument.Bookmarks("D12").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "D13", ""
        Range("D13").Copy
        wrdApp.ActiveDocument.Bookmarks("D13").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "E4", ""
        Range("E4").Copy
        wrdApp.ActiveDocument.Bookmarks("E4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "E6", ""
        Range("E6").Copy
        wrdApp.ActiveDocument.Bookmarks("E6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "F4", ""
        Range("F4").Copy
        wrdApp.ActiveDocument.Bookmarks("F4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "F6", ""
        Range("F6").Copy
        wrdApp.ActiveDocument.Bookmarks("F6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
    
        UpdateBookmark "G4", ""
        Range("G4").Copy
        wrdApp.ActiveDocument.Bookmarks("G4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "G6", ""
        Range("G6").Copy
        wrdApp.ActiveDocument.Bookmarks("G6").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H2", ""
        Range("H2").Copy
        wrdApp.ActiveDocument.Bookmarks("H2").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H3", ""
        Range("H3").Copy
        wrdApp.ActiveDocument.Bookmarks("H3").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H3_1", ""
        Range("H3").Copy
        wrdApp.ActiveDocument.Bookmarks("H3_1").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H3_2", ""
        Range("H3").Copy
        wrdApp.ActiveDocument.Bookmarks("H3_2").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H4", ""
        Range("H4").Copy
        wrdApp.ActiveDocument.Bookmarks("H4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H5", ""
        Range("H5").Copy
        wrdApp.ActiveDocument.Bookmarks("H5").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
    '    Range("H6").Value = Application.WorksheetFunction.Proper(Range("H6").Value)
    '    Range("H6").Copy
    '    wrdApp.ActiveDocument.Bookmarks("H6").Select
    '    wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
    ' Placement:=wdInLine, DisplayAsIcon:=False
    '    Application.CutCopyMode = False
    
        UpdateBookmark "H6_1", ""
        Range("H6").Copy
        wrdApp.ActiveDocument.Bookmarks("H6_1").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H6_2", ""
        Range("H6").Copy
        wrdApp.ActiveDocument.Bookmarks("H6_2").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H7", ""
        Range("H7").Copy
        wrdApp.ActiveDocument.Bookmarks("H7").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        
        UpdateBookmark "H8", ""
        Range("H8").Copy
        wrdApp.ActiveDocument.Bookmarks("H8").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
    
    
    lbl_Exit:
        Set wrdApp = Nothing
        Set wrdDoc = Nothing
        Set objWord = Nothing
        Exit Sub
    End Sub
    
    
    

    Hope this helps.

    Monday, June 15, 2015 8:14 PM
  • Hi,

    for the variable BMRange, you dim it as a Excel range, which should be a Word.range for a bookmark.You may dim the BMPRange this

    Dim BMRange As Word.Range

    And I have made a test to update the bookmark for a word document in an Excel file, it works fine.

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 1, 2015 4:42 PM
    Moderator
  • Hi, I think i'm having a similar problem with a userform. I want a form whereby the user enters details (text) and this text is updated in specific places in the document. I'm currently using bookmarks to achieve this.  The only issue I can't work out how to get that text to multiple locations in the document without coding each bookmark individually and cross referencing doesn't seem to update.

    My current code is:

    Private Sub OKbut_Click()
        Dim ProductNameasperSPC As Range
        Set ProductNameasperSPC = ActiveDocument.Bookmarks("PNameTitlePage").Range
        ProductNameasperSPC = Me.TextBox1.Value
        Dim Active1 As Range
        Set Active1 = ActiveDocument.Bookmarks("Active1TitlePage").Range
        Active1.Text = Me.TextBox2.Value
        Dim Active2 As Range
        Set Active2 = ActiveDocument.Bookmarks("Active2TitlePage").Range
        Active2.Text = Me.TextBox3.Value
        Dim PLnumber As Range
        Set PLnumber = ActiveDocument.Bookmarks("refnumberTitlePage").Range
        PLnumber.Text = Me.TextBox4.Value
        Dim MAHName As Range
        Set MAHName = ActiveDocument.Bookmarks("MAHCAPSTitlePage").Range
        MAHName.Text = Me.TextBox5.Value
        Me.Repaint
        prodinfo10c.Hide
        End Sub

    This code works nicely to fill my first page of the document, however, the Pname also features in the header of the next page, and other entries are needed to be repeated also.

    Another forum had a solution of:

    Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
        Dim BMRange As Range
        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
        BMRange.Text = TextToUse
        ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
    End Sub

    But I don't understand the code, could someone explain, or offer another alternative?

    Thanks a mill.

    J

    Friday, July 7, 2017 12:58 PM