CODEWITHSUNDEEP
excelms-wordform-fieldsvba
ChingLu Tay
ChingLu Tay

Reputation: 49

Excel VBA - Cross Referencing Bookmark/Form Field to Word

I have very minimal knowledge about VBA but still learning as it goes.

I've been using bookmarks in the word in order to populate data from excel. However, due to the content that some data need to repeat in a document, I tried using Text Form Field/Bookmark and REF Field to duplicate the same data.

The problem came in when once I populated data to the word, the text form field/bookmark disappear which causes REF Field unable to track the data that was referred to, hence, the "Error! Reference source not found."

In conclusion, what I'm trying to do is to populate data from excel to a locked word document and at the same time to retain Text Field Form/Bookmark in order to let REF field to track and duplicate the same data.

Is there any way to retain the Text Field Form/Bookmark placeholder after data is populated to the word? Here's my code that I am unable to solve in excel VBA.

Appreciate your help in advance! 

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\" & Environ("username") & "\Desktop\XXX\XXX"
objWord.ActiveDocument.Unprotect Password:="xxx"
With objWord.ActiveDocument

Dim objBMRange As Range
Set objBMRange = .Bookmarks("pr1").Range.Text = ws.Range("C28").Value
objBMRange.Text = pr1
.Bookmarks.Add "pr1", BMRange
.Fields.Update

objWord.ActiveDocument.Protect Password:="xxx", NoReset:=False, Type:=wdAllowOnlyFormFields
End With

Set objWord = Nothing
End Sub

Upvotes: 2

Views: 1419

Answers (1)

Variatus
Variatus

Reputation: 14383

You were almost there. Very near, but you didn't get the Range object sorted out. Please try this code (only partially tested).

Private Sub CommandButton1_Click()

    Dim Ws As Worksheet
    Dim objWord As Object
    Dim Mark As String
    Dim Txt As String
    Dim BmkStart As Long

    Mark = "pr1"
    Set Ws = ThisWorkbook.Sheets("Sheet1")
    Txt = Ws.Range("C28").Value

    Set objWord = CreateObject("Word.Application")
    With objWord
        .Visible = True
        .Documents.Open "C:\Users\" & Environ("username") & "\Desktop\XXX\XXX"
        With .ActiveDocument
            .Unprotect Password:="xxx"

            If .Bookmarks.Exists(Mark) Then
                With .Bookmarks(Mark).Range
                    BmkStart = .Start
                    .Text = Txt
                End With
                .Bookmarks.Add Mark, .Range(BmkStart, BmkStart + Len(Txt))
            End If
            .Fields.Update
            .Protect Password:="xxx", NoReset:=False, Type:=wdAllowOnlyFormFields
        End With
    End With

    Set objWord = Nothing
End Sub

One point is that the Bookmark defines a Word.Range (different from an Excel.Range which you get when you specify no application while working in Excel). The other, that Bookmark defines a range but isn't a range itself, not even a Word.Range. Therefore you get or set its text by modifying it's range's Text property.

Upvotes: 2

Related Questions