Search code examples
excelvbams-word

How to Replace Text at a Bookmark in Word with Formatted Text from an Excel Cell Using VBA

I'm working on a VBA script to copy a single cell from Excel and paste it into a Word document at a specific bookmark. I want to paste not just the text but also preserve the formatting of the cell. I'm using .PasteExcelTable with the parameters False, False, True to achieve this.

However, I'm encountering an issue where the pasted content doesn't replace the existing bookmark text. Instead, it's added before the text, and if I run the code again, the new text is inserted before the old one, which is not the behavior I desire.

Here is the function I'm using:

Function ReplaceContentAtBookmark(TWD As Word.Document, ByVal BookmarkName As String)
    
    Dim bmRange As Word.Range
    Set bmRange = TWD.Bookmarks(BookmarkName).Range
    
    ' Delete the previous element
    bmRange.Delete

    bmRange.PasteExcelTable False, False, True
    
    ' Recreate the bookmark with the new range
    TWD.Bookmarks.Add Name:=BookmarkName, Range:=bmRange

End Function

This is the single cell to copy from Excel:

enter image description here

This is the output wanted: The text in the Word document bookmark should be replaced with the new text from the Excel cell, retaining the cell's formatting.

enter image description here

This is the output that I get: The new text is inserted before the existing text (if I run multiple times the code), and the bookmark range doesn't seem to update to include the new text.

enter image description here

Solution

  • The problem here is that after the paste operation, the pasted selection is not selected. This means that when you re-add the bookmark, the bookmark is at the point immediately before the pasted text - it doesn't span the pasted text.

    Fixing this is tricky because there is no way to "paste and select" using Word VBA.

    One approach is to manually select the text after pasting. The difficulty is in identifying where that text is, and how long it is. However, here is one possible approach.

    First, add a VBA reference to "Microsoft Forms 2" via Tools > References. If you don't see "Microsoft Forms 2" in the list, click Browse... and add the file FM20.DLL (from your Windows\System32 folder, which should be the default browsing folder).

    Then, replace your code with the following:

    Function ReplaceContentAtBookmark(twd As Word.Document, ByVal BookmarkName As String)

Dim bmRange As Word.Range
Dim dataObj As New MSForms.DataObject
Dim str As String
Dim lenText As Integer

' Get the clipboard's contents.
dataObj.GetFromClipboard
str = dataObj.GetText

' Get the length of the copied text.
' The "-2" is needed because the clipboard appends CR-LF (new line) to the text.
lenText = Len(str) - 2

' Identify and delete the bookmark.
Set bmRange = twd.Bookmarks(BookmarkName).Range
bmRange.Delete

' Paste the copied Excel cell.
bmRange.PasteExcelTable False, False, True

' Extend the range to include the pasted text.
bmRange.End = bmRange.End + lenText

' Re-add the bookmark, now spanning the newly inserted range.
twd.Bookmarks.Add Name:=BookmarkName, Range:=bmRange

End Function

    The comments explain how the code works.

    A couple of warnings:

    • If your clipboard selection is anything other than a single Excel cell, this will fail.
    • If using non-English text or special characters, you might have some issues with character encoding (where the length of the clipboard text doesn't match the length of the pasted text in Word).