Macro sending data between Excel and Word

[Dani2735]

Hello, I'm working on a project which consists on two documents, an Excel and a Word files.

Excel contains some data in some cells, while Word contains some DocProperty fields.

I want to take the data in the Excel cells and insert it into the Word fields so every time I change the data in the cells it got updated in the fields.

I've developed a macro in Excel that does this, but the problem is that it creates a new document every time I execute it.

Is it possible to modify it so it changes Word fields without creating new copies of the document?

Here it's the code:

Sub Update_fields()

patharch = ThisWorkbook.Path & "\NewDoc.docx"
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.documents.Add Template:=patharch, NewTemplate:=False, DocumentType:=0


CMC = objWord.ActiveDocument.CustomDocumentProperties("cell1")
Ref_cell1 = Hoja1.Cells(1, 4)


CMS = objWord.ActiveDocument.CustomDocumentProperties("cell2")
Ref_cell2 = Hoja1.Cells(2, 4)


DEL = objWord.ActiveDocument.CustomDocumentProperties("cell3")
Ref_cell3 = Hoja1.Cells(3, 4)


objWord.Selection.Move 6, -1
objWord.ActiveDocument.CustomDocumentProperties("cell1") = Ref_cell1
objWord.ActiveDocument.CustomDocumentProperties("cell2") = Ref_cell2
objWord.ActiveDocument.CustomDocumentProperties("cell3") = Ref_cell3


objWord.Activate


End Sub

Thank you so much.

Best regards.

 

[Macropod]

All you need for this is to link the two files. To do that, simply copy the Excel data, then paste it into Word using Paste Special with the 'paste link' option and your preferred data format. No code required.