0

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?

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
AngelaG
  • 7
  • 3
  • *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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Foxfire And Burns And Burns Nov 29 '19 at 12:42
  • 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 Nov 29 '19 at 12:56

1 Answers1

0

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.

ASH
  • 20,759
  • 19
  • 87
  • 200