Transferring Excel data into Word

[gsxr4fg]

Hello VBA Family:hi:

I am trying to automatically transfer specific Excel cells into certain locations in Word. The Excel file is named Technical Competency and the the cell is G1. In Word, I have used "Design Mode" in the Controls section to insert the element into the document, this label is named Employee. Here is the VBA script I have thus far that is giving me a Runtime 1004 error, Appication-defined or object defined error:


Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb = objExcel.Workbooks.Open ("C:\Users\famison\Documents\Temp\Workplace\Technical Competency.xls")
ThisDocument.Employee.Caption = exWb.Sheets("Form Template").Cells (G1)
exWb.Close
Set exWb = Nothing
End Sub

Forgive my ignorance if the formula makes little sense, I am a newborn when it comes to writing VBA formulas.

Blessings,
Sean

[mana]

Option Explicit


Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\Users\famison\Documents\Temp\Workplace\Technical Competency.xls")

ThisDocument.Employee.Caption = exWb.Sheets("Form Template").Range("G1")

exWb.Close
objExcel.Quit

Set exWb = Nothing
Set objExcel = Nothing

End Sub

[gsxr4fg]

Thanks Mana, these changes worked accurately!:)

[gsxr4fg]

Will this allow any updates to the Excel form to auto populate into the Word file?