Exporting specific Excel data from Multiple fields into Word-CodePudding

Let me preface this by saying I'm very new to VBA, so any help is appreciated. I have been approached by someone to see if I can create a basic level of automation. The need is to export data from Excel into Word with a button push.

The end goal is to have fields of data within excel such as "First name" "Surname" "Address" "Contact Number". Then select say an entire row of a person (B1,B2,B3,B4) and when the Macro button is pressed, it opens a word document and pre-fills the fields into the document. It is to prefill letters for sending, this person has also stated that mail merge does not do what they need. Below is what I'm working with.

Sub ExcelToWord()
    Dim wordApp As Word.Application
    Dim mydoc As Word.Document

    Set wordApp = New Word.Application
    wordApp.Visible = True
    Set mydoc = wordApp.Documents.Add()
    ThisWorkbook.Worksheets("sheet1").Range("").Copy
    mydoc.Paragraphs(1).Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, _
                               RTF:=False
    mydoc.SaveAs2 "MyDoc"
End Sub

This allows for a specific range such as (A2,B3) However not the "Selected" area. I apologise if this is very basic but as I said, I don't really use VBA.

CodePudding user response：

If I understanding your question, I think you only need to replace:

ThisWorkbook.Worksheets("sheet1").Range("").Copy

by:

Selection.Copy

CodePudding user response：

It seems you need to use the Application.Selection property which returns the currently selected object on the active worksheet for an Application object.

Sub ExcelToWord()
    Dim wordApp As Word.Application
    Dim mydoc As Word.Document

    Set wordApp = New Word.Application
    wordApp.Visible = True
    Set mydoc = wordApp.Documents.Add()    
    
    ThisWorkbook.Worksheets("sheet1").Activate()
    Application.Selection.Copy()
    
    mydoc.Paragraphs(1).Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, _
                               RTF:=False
    mydoc.SaveAs2 "MyDoc"
End Sub