[Excel VBA] Automating Word Document Formatting: Styles and Alignment

When creating Word documents from Excel using VBA, simply pasting data isn’t always enough. You often need to format the document—applying heading styles, centering text, or styling tables—to make it look professional.

With VBA, you can fully automate these formatting tasks. This article explains how to programmatically control paragraph styles, alignment, and table designs after pasting text and tables from Excel to Word.

Important: Setting References Before Execution

To make the code easier to read and write, we use Word-specific constants (like wdAlignParagraphCenter). To use these, you must enable the “Reference” in the VBA editor beforehand.

1. Open the VBE (Visual Basic Editor) by pressing Alt + F11.
2. Click Tools > References in the menu.
3. Find “Microsoft Word XX.0 Object Library” in the list, check the box, and click OK. (Note: The “XX.0” part varies depending on your Word version).

Enabling this allows VBA to recognize Word commands and constants smoothly.

Complete VBA Code

The following code pastes text and a table into a Word document and then applies specific formatting to them.

' Reference Required: Microsoft Word XX.0 Object Library
Sub FormatWordDocumentFromExcel()

    ' Declare variables
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim titleRange As Range
    Dim dataTableRange As Range

    ' Set the Excel cell ranges to operate on
    Set titleRange = ThisWorkbook.Worksheets("Sheet1").Range("B2")
    Set dataTableRange = ThisWorkbook.Worksheets("Sheet1").Range("B4:E9")

    ' Launch Word Application
    Set wordApp = New Word.Application
    wordApp.Visible = True ' Turn on screen visibility

    ' Add a new document
    Set wordDoc = wordApp.Documents.Add

    ' --- 1. Transfer Data from Excel to Word ---
    
    ' Copy the title and paste as unformatted text
    titleRange.Copy
    wordApp.Selection.PasteSpecial DataType:=wdPasteText

    ' Insert two line breaks (Paragraphs)
    wordApp.Selection.TypeParagraph
    wordApp.Selection.TypeParagraph

    ' Copy the data table and paste as an Excel Table
    dataTableRange.Copy
    wordApp.Selection.PasteExcelTable False, False, False

    ' --- 2. Configure Word Document Formatting ---
    With wordDoc
        ' Apply the "Title" style to the 1st paragraph
        .Paragraphs(1).Style = "Title"
        
        ' Center align the 1st paragraph
        .Paragraphs(1).Alignment = wdAlignParagraphCenter
        
        ' Apply the "Table Grid" style to the 1st table
        .Tables(1).Style = "Table Grid"
        
        ' Center align (vertically) all rows in the 1st table
        .Tables(1).Rows.Alignment = wdCellAlignVerticalCenter
    End With

    ' Save the file (Optional)
    ' wordDoc.SaveAs ThisWorkbook.Path & "\FormattedReport.docx"
    ' wordDoc.Close
    ' wordApp.Quit
    
    ' Release objects
    Set wordDoc = Nothing
    Set wordApp = Nothing

End Sub

Explanation of Key Points

1. Data Transfer (Pasting Text and Tables)

wordApp.Selection.PasteSpecial DataType:=wdPasteText
wordApp.Selection.PasteExcelTable False, False, False

First, we transfer the data. The key here is using PasteSpecial with wdPasteText. This ignores the font/color formatting from Excel and pastes the title as plain text, allowing Word’s styles to take over cleanly later.

2. Paragraph Formatting (Styles and Alignment)

.Paragraphs(1).Style = "Title"
.Paragraphs(1).Alignment = wdAlignParagraphCenter

To manipulate specific objects in a Word document, you access them via their collection index number.

• .Paragraphs(1): Refers to the first paragraph in the document.
• .Style = "Title": Applies Word’s built-in “Title” style. You can change this to “Heading 1”, “Normal”, etc.
• .Alignment = wdAlignParagraphCenter: Sets the paragraph alignment. Constants like wdAlignParagraphLeft or wdAlignParagraphRight are also available.

3. Table Formatting

.Tables(1).Style = "Table Grid"
.Tables(1).Rows.Alignment = wdCellAlignVerticalCenter

Similar to paragraphs, you can target the first table using .Tables(1).

• .Style = "Table Grid": Sets the design of the entire table to the built-in “Table Grid” style (simple borders).
• .Rows.Alignment = wdCellAlignVerticalCenter: Centers the content vertically within the cells for all rows in the table.

Summary

The basic flow of manipulating Word formatting via VBA is: “Identify the object (Paragraph, Table, etc.) by index, and change its properties (.Style, .Alignment, etc.).”

Using this method, you can programmatically control fonts, sizes, colors, and almost any formatting option available in Word manually. This enables the complete automated generation of beautiful, professional reports.