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
Пікірлер: 10
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 awesome, exactly what I was hunting for... Cheers! 👍
@greggowaffles
Жыл бұрын
Awesome! Glad to hear that!!
Thank you so much
@greggowaffles
Жыл бұрын
You’re very welcome
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 😊
Hi, Can you code that in Word alone? I am interested in learning coding. Where should I start? This was great.
Hello, how can i do Excel VBA to Export Range as Image ? Can you help me please.
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
How to insert text after the table ??