Passing String From Excel to Word

Question

I am trying to pass a string from Excel userform using VBA to a Word document. In the Word document I have created a field>doc variable and called it bookingRef. The code is as follows:

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark


Set doc = objWord.Documents.Open("test.docx")

objWord.ActiveDocument.variables("bookingRef").Value = Me.txtRef.Text


objWord.ActiveDocument.Fields.Update

objWord.Documents.Save

It doesn't have any errors, however when I open the document up, I have to right click and update field (I thought objWord.ActiveDocument.Fields.Update did this?). Also, it keeps locking the document so it cannot be opened again. Is there a way to unlock after save?

Pᴇʜ · Accepted Answer · 2019-05-10 12:36:02Z

1

The document is locked because you didn't close it with the Document.Close method, so the document is still opened and therefore cannot be opened again. Also avoid using ActiveDocument the document that was opened is set to doc

Set doc = objWord.Documents.Open("test.docx")

and can therefore be referenced with doc.

Dim objWord As New Word.Application
Dim doc As Word.Document
'Dim bkmk As Word.Bookmark

Set doc = objWord.Documents.Open("test.docx")

doc.variables("bookingRef").Value = Me.txtRef.Text
doc.Fields.Update
doc.Save
doc.Close

Also don't forget to quit your Word application after you are done.

objWord.Quit

Otherwise the instance of Word will be open until you shut down your computer.

The Fields.Update method should update the fields, but it might be unsuccessful because of an error. Check it for errors:

If doc.Fields.Update = 0 Then 
    MsgBox "Update Successful" 
Else 
    MsgBox "Field " & doc.Fields.Update & " has an error" 
End If

What I did (a test according comments below this answer):

(following steps according How to store and retrieve variables in Word documents)

Created a file C:\Temp\test.docx

To use the DocVariable field, follow these steps: On the Insert menu, click Field.
- In the Categories box, select Document Automation.
- In the Field names list, select DocVariable.
- In the New Name box, under Field properties, type the name of the document variable bookingRef.
- Click OK.
  
  Note you will see nothing in the document yet but that's ok because the variable bookingRef does not exist yet.
- Save file and close Word.

Run the following code in Excel

Option Explicit

Public Sub Test()
    Dim objWord As New Word.Application

    On Error GoTo CLOSE_WORD_APP 'error handling to ensure there will not be any orphaned and invisible Word application left

    Dim doc As Word.Document
    Set doc = objWord.Documents.Open("C:\Temp\test.docx")

    doc.Variables("bookingRef").Value = "This is the updated Value: " & Time
    doc.Fields.Update
    doc.Save
    doc.Close

CLOSE_WORD_APP:
    objWord.Quit SaveChanges:=False

    If Err.Number <> 0 Then
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
End Sub

Open the Word document C:\Temp\test.docx and see that everything is updated:

edited May 10, 2019 at 12:36

answered May 10, 2019 at 11:30

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BCLtd Over a year ago

Hi, thanks for the advice - especially regarding closing the doc and quitting word. However, I still have to go in to the doc, right click and press update field. Isnt that what doc.Fields.Update should do?

Pᴇʜ Over a year ago

@BCLtd see my edited answer and check if the update was successfull or had errors.

BCLtd Over a year ago

It definetly is pass the variable across, but fails to update - even though I receive the message 'update succesful' - its a docvarible I am using - is that correct?

BCLtd Over a year ago

It's blank on print preview too :(

Pᴇʜ Over a year ago

@BCLtd See my updated answer. I described what I did to test it, and it worked perfectly.

|

Collectives™ on Stack Overflow

Passing String From Excel to Word

1 Answer 1

What I did (a test according comments below this answer):

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

What I did (a test according comments below this answer):

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related