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
