I am trying to figure out the best way to send information from Excel to Word. I am currently using a data source created in excel (.csv) to send information to Word via mail merge. I have only figured out how to mail merge individual referenced cells and my data source has thousands of fields. I was wondering if it is possible to use mail merge or other technology to send either tables or a named ranges referencing multiple cells from Excel into Word. Ideally if I can send multiple cells at once I hopefully would not have to format each field. Any help or ideas would be much appreciated.
You could use DocVariables for this.
Sub PushToWord()
Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next
objWord.ActiveDocument.variables("BrokerFirstName").Value = Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value = Range("BrokerLastName").Value
objWord.ActiveDocument.variables("Ryan").Value = Range("Ryan").Value
objWord.ActiveDocument.Fields.Update
'On Error Resume Next
objWord.Visible = True
End Sub
Run the code from Excel.
In the Excel VBA Editor: Tools->References->Microsoft Word x
Insert->Field->Category:DocumentAutomation->Field Names:DocVariable->Field Codes Button-> Then enter the name of the variable.