Create and append Word document tables from VBA

If you ever needed to export information from Excel into a Word table, this piece of code might help.

I originally intended this to be used with the Random Data Generator addin, but it isn't practical to paste what could potentially be thousands of table cells into Word.

According to Exporting to Microsoft Word, Word tables support a maximum of 32767 rows and 63 columns. Actually, I'm not sure where I saw the 32k rows limit, but 63 seems to be what everybody says is the column limit. If anyone could verify that I'd be glad to hear it.

So here's a procedure that takes a variable amount of columns and rows and either creates or opens an existing Word document, appending the table to the end of the document. If a new document is created, the "end" of the document is obviously the same as the start, so the table becomes the only content in the file. If an existing document is passed, it is opened and the table is appended to the end of the existing content. The code is based on code found at Automating Word Tables for Data Insertion and Extraction.

This code has been tested in Word 2003, please let me know if it works in other versions.

Sub AddTableToWordDoc(entries As Variant, filePath As String, _
    Optional styleToApply As Variant = "Table Simple 3")
' specify existing file to add table to
' existing Word document
  On Error GoTo ErrorHandler

  Dim wordApp As Object ' Word.Application
  Dim wordDoc As Object ' Word.Document
  Dim wordRange As Object ' Word.Range
  Dim wordTable As Object ' Word.Table
  Dim numRows As Long, numCols As Long
  Dim nrRows As Long, nrCols As Long
  Dim myRange As Object ' Word.Range
  ' late bound constants
  Const wdWindowStateMinimize As Long = 2
  Const wdNormalView As Long = 1
  Const wdDoNotSaveChanges As Long = 0
  Const wdWord9TableBehavior As Long = 1
  Const wdAutoFitContent As Long = 1

  ' create word document
  On Error Resume Next
  Set wordApp = CreateObject("Word.Application")
  If wordApp Is Nothing Then
    MsgBox "Could not start Microsoft Word"
    GoTo ProgramExit
  End If
  ' resume normal error handling
  On Error GoTo ErrorHandler

  ' if an existing file is specified, open it, else create new file
  If Len(Dir(filePath)) > 0 Then
    Set wordDoc = wordApp.documents.Open(filePath)
    Set wordDoc = wordApp.documents.Add
  End If
  ' maximize execution speed
  With wordDoc.Windows(1)
    .WindowState = wdWindowStateMinimize
    .View = wdNormalView
  End With
  With wordApp
    .Options.Pagination = False
    .ScreenUpdating = False
  End With

  ' create table w/ appropriate # of rows & cols
  ' append to existing file, or simply add (if new file)
  numRows = UBound(entries)
  numCols = UBound(entries, 2)
  Set wordRange = wordDoc.Range(wordDoc.Range.Characters.Count - 1)
  Set wordTable = wordRange.Tables.Add(wordRange, numRows, _
    numCols, wdWord9TableBehavior, wdAutoFitContent)

  ' loop through array and populate table cells
  For nrRows = 1 To numRows
    For nrCols = 1 To numCols
      wordTable.cell(nrRows, nrCols).Range.text = _
          entries(nrRows, nrCols)
    Next nrCols
  Next nrRows

  ' format table
  With wordTable
    .style = styleToApply  ' ex: "Table Simple 3"
    .ApplyStyleHeadingRows = True
    .ApplyStyleLastRow = True
    .ApplyStyleFirstColumn = True
    .ApplyStyleLastColumn = True
  End With

  ' save file and close Word
  With wordDoc
    .SaveAs filePath
    .Close wdDoNotSaveChanges
  End With

  Exit Sub
  MsgBox Err.number & " - " & Err.Description
  Resume ProgramExit
End Sub

First we create a new instance of Word.Application, then check if the filepath being passed is for an existing file. If the file exists, we open it, otherwise we create a new document. After a few speed optimizations, we create a new table and append it to the end of the document.

Document.Range.Characters.Count is used to determine the end of the file. This value represents the number of characters in the file, and the table is placed after this space. Regardless of whether the file is new or not, the table will be placed after the last character. The fact that a new file has no content is irrelevant; the end of the file just happens to also be the beginning. We are telling VBA to put the table at the "end" of the file, regardless of where it is.

Afterwards, we loop through the array and paste values into the table cells and apply some formatting. You can remove or change these values (or parameterize them). Finally we save and close the file.

Because I am not familiar with the Word Object Model, a lot of assumptions are made in the code. For example, the Tables.Add Method assumes you want to autofit the cells. I also assumed a default style and applied it to the table.

If there is a better way to do this, I don't know it.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Random Data Generator