Excel VBA Macro: Create Word Document & Add Table (Dynamic Range)

Excel VBA Macro: Create Word Document & Add Table (Dynamic Range). In this video, we go over how to add a table to a word document with a dynamic range from excel. We also briefly review enabling borders in the table and changing the background color of the table with our code. We finally change the size of the range to show that the macro will include all rows and columns regardless of the size of the range coming from excel.

Code:

Sub add_table_2_word_dynamic_range()

Dim objWord
Dim objDoc
Dim objSelection
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet
Dim row_count As Integer
Dim col_count As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Activate
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
Set objSelection = objWord.Selection

objWord.Visible = True
objWord.Activate

Set CountryTable = objDoc.Tables.Add(objSelection.Range, row_count, col_count)
With CountryTable

With .Borders
.enable = True
.outsidecolor = RGB(0, 0, 0)
.insidecolor = RGB(0, 0, 0)

End With

.Rows(1).shading.backgroundpatterncolor = RGB(0, 153, 255)

For i = 1 To row_count

For j = 1 To col_count

.cell(i, j).Range.InsertAfter ws.Cells(i, j).Text

Next j

Next i

End With

End Sub

#ExcelVBA #ExcelMacro