Solved: Update Word Form Field with Excel Value

[tca_VB]

I've searched around and cannot find an answer...

In VBA code within Excel, I want to open a word template and set a form field value in that document to a field in excel.

here's what I've tried...

Sub OpenPINDoc()
'
' Open PIN Doc Macro for New TN Client
Dim xlApp As Object
Dim wdApp As Object
Dim xlString As String
Dim catString As String
Dim storString As String
Dim dotString As String
Dim pinString As String

Sheets("Doc_Index").Select



storString = Sheets("Doc_Index").Range("B2")
dotString = Sheets("Doc_Index").Range("C2")
pinString = storString & dotString


'Open Word Instance
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True

'Open Template as Document
wdApp.Documents.Add Template:=(pinString)
wdApp.Activate

'Prefill Data
With wdApp.ActiveDocument
.FormFields("docCategory").Result = ThisWorkbook.Sheets("DocIndex").Range("F1").Value
End With


'Clear
Set xlApp = Nothing
Set wdApp = Nothing
End Sub


I have also tried to select form field and copy paste using:
'copy from excel
ThisWorkbook.Sheets("Doc_Index").Range("F1").Copy
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=docCategory
wdApp.Selection.Paste


The second will past the value into the word doc, but at the top of the page and not the selected form field.

Thanks for the help.

[jfournier]

I'm not sure if this is the problem but I have a word macro that copies one form field's value to another, and when setting a form field's value I use ".result.text = " to set the text, where you're just saying ".result = ". So I'd say try changing it to:
.FormFields("docCategory").Result.text = ThisWorkbook.Sheets("DocIndex").Range("F1").Value

[tca_VB]

Thanks for the idea. I tried that and still hit an error on that line. It's a Run-time error '9': Subscript out of range error if that helps anyone.

[jfournier]

You might want to try breaking that out into multiple lines. First set a handle to your form field and make sure it's getting a proper reference, then get your worksheet value as a string, and then try setting the result text and see which one gives an error.
For text fields I've only used the document's fields( index ) and not formfields( index ), so setting the result text may be a little different between the two.

[tca_VB]

Thanks for the suggestion to break it down and find the problem - see the tree in the forest so to speak.

Here's what solved it...

Set the string value from excel before opening the word application. Also, I didn't need .text at the end...

Here's the final code for those reading along.
Thanks again for your help and this forum.

Sheets("Pin_Index").Select
xlvalString = Sheets("Doc_Index").Range("F1")


'Open Word Instance
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True

'Open Template as Document
wdApp.Documents.Add Template:=(pinString)
wdApp.Activate

'Prefill Data

With wdApp.ActiveDocument
.FormFields("docCategory").Result = xlvalString
End With