CODEWITHSUNDEEP
excelvbams-word
Erik De Luca
Erik De Luca

Reputation: 307

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

Upvotes: 2

Views: 162

Answers (2)

taller
taller

Reputation: 18778

  • Another option to get it done without using MSForms.DataObject

Microsoft documentation:

Bookmarks.Add method (Word)

Document.Characters property (Word)

Function PasteXlAtBookmark(oDoc As Word.Document, ByVal bmName As String)
    Dim bmRng As Word.Range
    Dim lenDoc As Long
    Set bmRng = oDoc.Bookmarks(bkName).Range
    ' The quantity of chars in doc exclude `bmName` range
    lenDoc = oDoc.Characters.Count - bmRng.Characters.Count
    bmRng.PasteExcelTable False, False, True
    ' Adjust the range after paste
    bmRng.End = bmRng.End + oDoc.Characters.Count - lenDoc
    oDoc.Bookmarks.Add Name:=bmName, Range:=bmRng
End Function

Upvotes: 2

Neil T
Neil T

Reputation: 3285

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).

Upvotes: 1

Related Questions