Copy records from Excel into Word VBA

Question

I am trying to export cells from Excel file into new Word file and then format the document with the use of VBA. Until now I have simple code:

Sub Macro1()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("abc")
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\xxx\test.docx"

With objWord.ActiveDocument 
    Range("A2:B2").Select
    Selection.Copy
    'here I have problems
End With 

End Sub

My problem is to paste selected cells into the Word document and format them. What should I type below Selection.Copy?

What should I type below Selection.Copy Have you tried the macro recorder of Word? You could see there the basic code, and the adapt it to your needs. Also, you should read How to avoid using Select in Excel VBA — Foxfire And Burns And Burns
Thank you for tips. I've done it already: > Range("A2:B2").Select > Selection.Copy > objWord.Selection.PasteExcelTable False, False, False > Application.CutCopyMode = False — AngelaG

ASH ASH · Accepted Answer · 2019-11-30T11:31:26

You can use BookMarks or DocVariables for this. I would recommend using DocVariables. Add the relevant DocVariables to your Word Document (if you don't know how to do this Google it). Copy the code below and paste it in Excel.

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("FirstName").Value = Range("FirstName").Value
objWord.ActiveDocument.variables("LastName").Value = Range("LastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

Set a reference to Microsoft Word xx.x Object Library.

Copy records from Excel into Word VBA

1 Answers