[Excel VBA] How to Export Excel Data to a Word Document and Paste It as a Table

2025年12月27日

There is a very common need to output data managed in Excel into a well-formatted Word document that combines both text and tables.

Using VBA, you can automate more than just exporting simple text; you can also perform advanced operations like pasting a range of cells directly as a Word table.

In this article, I will explain the complete procedure for using VBA to create a new Word document, input specific cell values as text, paste a separate cell range as a table, and finally save the document.

Excel Sample Data

First, assume the source data is prepared on Sheet1 as follows:

Cell B2: Text to serve as the document title (e.g., “August 2025 Sales Report”)
Range B4:D10: Data you want to paste into Word as a table

Completed VBA Code

Below is the VBA code to output the data above into a Word document.

Sub ExportDataToWordDocument()

    ' Declare variables
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim textRange As Range
    Dim tableRange As Range

    ' --- 1. Launch Word Application ---
    Set wordApp = CreateObject("Word.Application")
    ' Uncomment the line below to watch the process happen
    ' wordApp.Visible = True

    ' --- 2. Set Target Excel Ranges ---
    Set textRange = ThisWorkbook.Worksheets("Sheet1").Range("B2")
    Set tableRange = ThisWorkbook.Worksheets("Sheet1").Range("B4:D10")

    ' --- 3. Add a New Document and Write Data ---
    Set wordDoc = wordApp.Documents.Add

    ' Use the Selection object to type text at the cursor position
    wordApp.Selection.TypeText textRange.Value
    ' Insert a line break (Paragraph)
    wordApp.Selection.TypeParagraph
    wordApp.Selection.TypeParagraph ' Add another line break for spacing

    ' Copy the Excel range to become the table
    tableRange.Copy
    ' Paste into Word at the cursor position as an Excel table
    wordApp.Selection.PasteExcelTable False, False, False

    ' --- 4. Save and Close ---
    With wordDoc
        .SaveAs ThisWorkbook.Path & "\SalesReport.docx"
        .Close
    End With
    wordApp.Quit

    ' --- 5. Release Objects ---
    Set wordDoc = Nothing
    Set wordApp = Nothing
    
    MsgBox "Word document creation is complete."

End Sub

Key Points of the Code

1. Launching Word and Creating a Document

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Add

We use CreateObject to launch Word in the background and .Documents.Add to create a new blank document. This is the standard way to start controlling Word from Excel.

2. Text Input Using the Selection Object

wordApp.Selection.TypeText textRange.Value
wordApp.Selection.TypeParagraph

The Selection object represents the current cursor position within the Word document.

.TypeText: A method to input the specified string at the cursor position.
.TypeParagraph: Acts like pressing the Enter key, inserting a line break (new paragraph).

3. Copying Excel Range and Pasting into Word

tableRange.Copy
wordApp.Selection.PasteExcelTable False, False, False

This is the core part of this article.

First, use the Excel VBA command tableRange.Copy to copy the specified range to the clipboard. Next, use the Word VBA command wordApp.Selection.PasteExcelTable to paste the clipboard contents as a Word table.

The three arguments for PasteExcelTable (False, False, False) correspond to the following options:

LinkedToExcel: If False, it becomes an independent table, not linked to the original Excel file.
WordFormatting: If False, it tries to maintain the Excel formatting rather than using standard Word formatting.
RTF: If False, it pastes as a standard Word table rather than Rich Text Format.

4. Save and Close

.SaveAs ...
.Close
wordApp.Quit

Once editing is complete, use .SaveAs to save the file, .Close to close the document, and finally, strictly use .Quit to terminate the Word application itself.