Using VBA to Combine Several Excel Tables into a Single Word Document

Using VBA to Combine Several Excel Tables into a Single Word Document
Using VBA to Combine Several Excel Tables into a Single Word Document
Hugo Bertrand
Friday, July 19, 2024 at 10:15:53 AM

Efficiently Merging Excel Data into Word

Managing data across numerous platforms can be difficult, especially when you need to merge multiple tables from Excel into a Word document. You may automate this process using VBA, ensuring that data is transferred seamlessly while preserving the proper format and structure.

This post looks at a VBA routine that currently generates three separate Word documents from Excel tables. We will show how to change the code to output all tables in a single Word document, with page breaks between each table for clarity and organization.

Command Description
Set wdApp = New Word.Application Sets up a new Word application instance.
wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak Creates a page break at the conclusion of the document.
.Rows(1).HeadingFormat = True This specifies that the table's first row is a header row.
.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol) Adds a new table to the Word document with the desired rows and columns.
With wdTbl.Borders Sets the border style for the table's inner and outer lines.
wdApp.Visible = True Makes the Word application visible to the user.
If (r - startRow + 2) > .Rows.Count Then .Rows.Add If the current row is greater than the number of rows in the table, a new row is added.
Set wdDoc = .Documents.Add Creates a new Word document.

Understanding the VBA Macro to Combine Tables

The accompanying scripts explain how to use VBA to automate the process of moving data from numerous Excel tables into a single Word document. The main script, Sub ConsolidateTablesInOneDocument(), starts a new Word instance with Set wdApp = New Word.Application and produces a new document with Set wdDoc = .Documents.Add. It finds the rows in Excel where the tables finish by checking for blank cells and stores them in variables First and Second. This enables the script to determine where each table stops and begins. The macro then generates tables in Word using .Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol) and populates them with data from Excel.

To clearly distinguish each table, the script inserts a page break after each table using wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak. The CreateTable function is run three times to create and prepare each table. This subroutine builds headers, populates rows and columns, and adds border styles to the tables with .Rows(1).Range.Font.Bold = True . Finally, the macro sets the Word application's visibility to true with wdApp.Visible = True, allowing the user to view the created document. This method easily consolidates several tables from Excel into a single Word page while preserving clarity and format.

Consolidating multiple Excel tables into a single Word document

This script shows how to utilize VBA in Excel to combine numerous tables into a single Word document, including page breaks after each table.

Sub ConsolidateTablesInOneDocument()
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim wdTbl As Word.Table
    Dim xlSht As Worksheet
    Dim lRow As Integer, lCol As Integer
    Dim r As Integer, c As Integer
    Dim Blanks As Integer, First As Integer, Second As Integer
    lRow = Sheets("Feedback Sheets").Range("A1000").End(xlUp).Row - 2
    Blanks = 0
    i = 1
    Do While i <= lRow
        Set rRng = Worksheets("Feedback Sheets").Range("A" & i)
        If IsEmpty(rRng.Value) Then
            Blanks = Blanks + 1
            If Blanks = 1 Then First = i
            If Blanks = 2 Then Second = i
        End If
        i = i + 1
    Loop
    Set xlSht = ActiveSheet: lCol = 5
    With wdApp
        .Visible = True
        Set wdDoc = .Documents.Add
        Call CreateTable(wdDoc, xlSht, 1, First, lCol)
        wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak
        Call CreateTable(wdDoc, xlSht, First + 1, Second, lCol)
        wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak
        Call CreateTable(wdDoc, xlSht, Second + 1, lRow, lCol)
    End With
End Sub
Sub CreateTable(wdDoc As Word.Document, xlSht As Worksheet, startRow As Integer, endRow As Integer, lCol As Integer)
    Dim wdTbl As Word.Table
    Dim r As Integer, c As Integer
    Set wdTbl = wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol)
    With wdTbl
        .Rows(1).Range.Font.Bold = True
        .Rows(1).HeadingFormat = True
        .Cell(1, 1).Range.Text = "Header 1"
        If lCol > 1 Then .Cell(1, 2).Range.Text = "Header 2"
        If lCol > 2 Then .Cell(1, 3).Range.Text = "Header 3"
        For r = startRow To endRow
            If (r - startRow + 2) > .Rows.Count Then .Rows.Add
            For c = 1 To lCol
                .Cell(r - startRow + 2, c).Range.Text = xlSht.Cells(r, c).Text
            Next c
        Next r
    End With
    With wdTbl.Borders
        .InsideLineStyle = wdLineStyleSingle
        .OutsideLineStyle = wdLineStyleDouble
    End With
End Sub

Merging Excel Data into Word using VBA

This script uses VBA to merge tables from an Excel sheet into a single Word document while maintaining proper formatting and page breaks.

Sub MergeTablesIntoWord()
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim wdTbl As Word.Table
    Dim xlSht As Worksheet
    Dim lRow As Integer, lCol As Integer
    Dim r As Integer, c As Integer
    Dim Blanks As Integer, First As Integer, Second As Integer
    lRow = Sheets("Feedback Sheets").Range("A1000").End(xlUp).Row - 2
    Blanks = 0
    i = 1
    Do While i <= lRow
        Set rRng = Worksheets("Feedback Sheets").Range("A" & i)
        If IsEmpty(rRng.Value) Then
            Blanks = Blanks + 1
            If Blanks = 1 Then First = i
            If Blanks = 2 Then Second = i
        End If
        i = i + 1
    Loop
    Set xlSht = ActiveSheet: lCol = 5
    With wdApp
        .Visible = True
        Set wdDoc = .Documents.Add
        Set wdTbl = wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol)
        PopulateTable wdTbl, xlSht, 1, First, lCol
        wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak
        Set wdTbl = wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol)
        PopulateTable wdTbl, xlSht, First + 1, Second, lCol
        wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak
        Set wdTbl = wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol)
        PopulateTable wdTbl, xlSht, Second + 1, lRow, lCol
    End With
End Sub
Sub PopulateTable(wdTbl As Word.Table, xlSht As Worksheet, startRow As Integer, endRow As Integer, lCol As Integer)
    Dim r As Integer, c As Integer
    With wdTbl
        .Rows(1).Range.Font.Bold = True
        .Rows(1).HeadingFormat = True
        .Cell(1, 1).Range.Text = "Header 1"
        If lCol > 1 Then .Cell(1, 2).Range.Text = "Header 2"
        If lCol > 2 Then .Cell(1, 3).Range.Text = "Header 3"
        For r = startRow To endRow
            If (r - startRow + 2) > .Rows.Count Then .Rows.Add
            For c = 1 To lCol
                .Cell(r - startRow + 2, c).Range.Text = xlSht.Cells(r, c).Text
            Next c
        Next r
    End With
    With wdTbl.Borders
        .InsideLineStyle = wdLineStyleSingle
        .OutsideLineStyle = wdLineStyleDouble
    End With
End Sub

Creating and Formatting Tables in Word with VBA

When automating the transfer of data from Excel to Word with VBA, it is critical to learn how to correctly manage and format tables. One critical issue is ensuring that data is transported accurately while retaining structure and readability. This necessitates a mastery of the VBA instructions that regulate table formation, formatting, and page break placement. For example, the command Set wdTbl = wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol) adds a new table to the Word document, specifying the number of rows and columns based on Excel data.

The table must also be formatted properly. Commands like .Rows(1).Range.Font.Bold = True make the first row bold, signifying headers, and wdTbl.Borders sets the border styles for both the inside and outside lines of the table. Furthermore, introducing page breaks is crucial for ensuring that each table appears on a distinct page. This is done with wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak. These commands ensure that the final document is well-organized and well formatted.

Frequently Asked Questions About VBA Macros in Word and Excel

  1. How do I launch a new Word application with VBA?
  2. To start a new Word instance, type Set wdApp = New Word.Application.
  3. How can I use VBA to insert a page break in a Word document?
  4. Add a page break at wdDoc.Characters.Last.InsertBreak Type:=wdPageBreak.
  5. How do I insert a table into a Word document using VBA?
  6. Create a table using wdDoc.Tables.Add(Range:=wdDoc.Range, NumRows:=2, NumColumns:=lCol).
  7. How can I format a table's first row as a header?
  8. Make the first row a header with .Rows(1).HeadingFormat = True and bold with .Rows(1).Range.Font.Bold = True.
  9. How can I use VBA in Word to set table borders?
  10. Set the borders with wdTbl.Borders and define styles for inside and outside lines.
  11. What command is used to add a new row to a table when the current row exceeds the number of rows already present?
  12. Create a new row containing If (r - startRow + 2) > .Rows.Count Then .Rows.Add.
  13. How can I use VBA to create a new Word document?
  14. Create a new document using Set wdDoc = .Documents.Add.

Final Thoughts

VBA allows you to combine many Excel tables into a single Word document, which speeds up data transfer and formatting. By automating table construction, formatting, and page breaks, the macro ensures that the finished document is well-organized and professionally presented. This method saves time and decreases the possibility of error, making it an effective alternative for managing and displaying data across multiple platforms.