Populate Word textbox from an Excel cell

[alexsly]

I'm trying to copy a cell value and paste it into a text box (ActiveX) in a Word document. It's a part of longer code and the rest works fine, but I'm struggling with this part. What I want to achieve is to create a new document from existing template, add two variables into it and save as a new file. The variables are simple just name and last name.

While saving a file is not a problem I can't copy values from excel into text boxes in a word file. I use ActiveX text boxes. Fields have to be easily moved and arranged in the document. Previously I just used copy/paste code from excel into word and it worked but horizontal and vertical alignments are crucial and with a copied cell I could do nothing. I couldn't find any solution here on forum and on the web. If someone has any ideas what I am missing, it would be very helpful, I'm poor at vba so I guess I miss a simple code line.



Dim WdObj As Object, fname As Stringfname = "test"
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False
WdObj.Documents.Open Filename:="C:\path"


WdObj.textbox_name.Value = Sheets("sheet_name").Range("A2").Value 'here is what does not work
WdObj.textbox_name2.Value = Sheets("sheet_name").Range("A3").Value 'here is what does not work


With WdObj
.ChangeFileOpenDirectory "C:\path2"
.ActiveDocument.SaveAs Filename:=fname & ".doc"
End With
With WdObj
.ActiveDocument.Close
.Quit
End With
Set WdObj = Nothing

[snb]

Use docvariables


sub M_snb()
with getobject("G:\OF\example.docx")
.variables("V_01")="T1"
.variables("V_02")="T2"
.fields.update
.windows(1).visible=true
.close -1
end with
End Sub

[macropod]

Try:

Dim xlWkSht As worksheet, wdObj As Object, wdDoc As Object, fname As String
fname = "test"
Set xlWkSht = Sheets("sheet_name")
Set wdObj = CreateObject("Word.Application")
With wdObj
.Visible = False
Set wdDoc = .Documents.Open(FileName:="C:\path\" & fname & ".doc")
With wdDoc
.InlineShapes(1).OLEFormat.Object.Text = xlWkSht.Range("A2").Value
.InlineShapes(2).OLEFormat.Object.Text = xlWkSht.Range("A3").Value
.SaveAs FileName:="C:\path2" & fname & ".doc"
.Close
End With
.Quit
End With
Set wdDoc = Nothing: Set wdObj = Nothing: Set xlWkSht = Nothing
Note: The above code assumes your ActiveX controls are the first two in the Word document. If not you'll need to adjust the numbering or, if their relative location in the document varies, implement a loop to find them via their OLEFormat object names (e.g. "TextBox1", "TextBox2"). Note too that there is no need to change folders for the save: simply tell Word where to save as part of the Save expression itself.

On a side note, if you're after textboxes that are easily moved around, I'd have opted for standard Word textboxes or, perhaps, content controls (docx format). Otherwise, I'd eschew all types of textbox/control and simply write to bookmarks.

[alexsly]

Thank you both very much for your help and time.

snb, I'll read more about docvariables, it seems a little more complicated than I wanted it to be.
macropod, thank you. I used your remarks to modify the code. Finally I decided to use labels instead and it works. Clean and simple.