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

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


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

[pklocke]

Worked great for me, altho had a small problem I am still working on. The data I want to use starts in cell C1 of the excel table (and everything to right), so when I change the set of the row/col count variable to use "C1" (where you have A1), it still started with C1. I think I need to find the numerical offset of what C1 is for the setting of the i variable?? Thx again! very helpful and got me started.

[kpcrompton]

Thank you for the easy to follow tuition. Is it possible for the source data to start a cell other than "A1" and then the range start from there? Thanks again.

[亦涵张]

This is very useful, thank you! I have a question, what if the columns in each row of the table could be different, some row contains merged cell? Would like to know some contents on this 😊

[josephwilson]

This is awesome, exactly what I was hunting for... Cheers! 👍

[carlinecharles]

Hi, Can you code that in Word alone? I am interested in learning coding. Where should I start? This was great.

[salimkoc]

Hello, how can i do Excel VBA to Export Range as Image ? Can you help me please.

[kingsantiago]

hi sir. how about creating tables based on the column value. meaning it would create a different table each time the macro see a value on a specific column

example. Column A is for Test Case#, so for row 1 its test case 1, the next rows are the steps where it the column A is blank, then once the macro sees a value for Column A like Test Case 2, it would create another table on the next page of the word. HELP ME PLSS