VBA-Excel: Add Table and fill data to the Word document
VBA-Excel: Add Table and fill data to the Word document
To Add Table and fill data to the Word document using Microsoft Excel, you need to follow the steps below:
- Create the object of Microsoft Word
- Create a document object and add documents to it
- Make the MS Word visible
- Create a Range object.
- Create Table using Range object and define no of rows and columns.
- Get the Table object
- Enable the borders of table using table object.
- Fill the data in table
- Save the document
Create the object of Microsoft Word
Set objWord = CreateObject(“Word.Application”)
Create a document object and add documents to it
Set objDoc = objWord.Documents.Add
Make the MS Word Visible
objWord.Visible = True
Create a Range object.
Set objRange = objDoc.Range
Create Table using Range object and define no of rows and columns.
objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns
Get the Table object
Set objTable = objDoc.Tables(1)
Enable the borders of table using table object.
objTable.Borders.Enable = True
Fill the data in table
objTable.Cell(1, 1).Range.Text = “Sumit”
Save the Document
objDoc.SaveAs (“D:\MyFirstSave”)
Complete Code:
Function FnAddTableToWordDocument() Dim intNoOfRows Dim intNoOfColumns Dim objWord Dim objDoc Dim objRange Dim objTable intNoOfRows = 5 intNoOfColumns = 3 Set objWord = CreateObject("Word.Application") objWord.Visible = True Set objDoc = objWord.Documents.Add Set objRange = objDoc.Range objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns Set objTable = objDoc.Tables(1) objTable.Borders.Enable = True For i = 1 To intNoOfRows For j = 1 To intNoOfColumns objTable.Cell(i, j).Range.Text = "Sumit_" & i & j Next Next End Function
Thank you for this great post, it helped me a lot!
Thx for the motivation 🙂
Thank you very much,, really helped alot.
Glad that it was useful for you.
How do you paste a table to an existing word document?
Please read these along with this articles,,,, do let me know if you are still stuck..
http://excel-macro.tutorialhorizon.com/vba-excel-format-the-existing-table-in-a-word-document/
■http://excel-macro.tutorialhorizon.com/vba-excel-edit-and-save-an-existing-word-document/
http://excel-macro.tutorialhorizon.com/vba-excel-appending-text-to-an-existing-word-document-at-the-end/
http://excel-macro.tutorialhorizon.com/vba-excel-appending-text-to-existing-word-document-at-beginning/
Hi i want to adjust the size of the table and i want to insert different data in each row please help me for that
objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns ,,, this way you can define your table size.
And for filling different data in each row, while iterating through rows you can fill wat ever data you want.
For i = 1 To intNoOfRows
For j = 1 To intNoOfColumns
objTable.Cell(i, j).Range.Text = “Sumit_” & i & j
Next
Next
Hope this answers your query
I have a list of colon separated strings ( I.e. Abc: def, ghi: jkl) that I want to loop through, set the text to the left of the colon as column heading ( I.e. abc, ghi), set text to the right of the colon as column value (def,jkl). Any ideas?
Sub sumit()
Dim text
text = “ABC:abc, DEF:def, GHI:ghi, JKL:jkl”
cols = Split(text, “,”)
For i = 0 To UBound(cols)
rr = Split(cols(i), “:”)
For j = 0 To UBound(rr)
Range(Chr(65 + i) & j + 1).Value = rr(j)
Next
Next
End Sub
It will solve for you. Please let me know for further queries.
Sumit Jain
How to change the font of the context of the table. For example, I have a table of 2 rows and 5 columns. I want to bold the first row and change the color of 2nd row to red. Thanks in advance
Please refer this link :
http://excel-macro.tutorialhorizon.com/vba-excel-change-font-color-weight-of-table-data-in-the-word-document/
For horizontal alignment in specific cells I have found many online variations however none of them seem to work.
Does anyone know how to horizontally align the text in “objTable.cell(1, 1)” to the right?
The same counts for a border around a specific range of cells.
Does anyone know how to add a border around the first five cells in the first column?
Sub JustARandomTable ()
Dim intNoOfRows
Dim intNoOfColumns
Dim objWord
Dim objDoc
Dim objRange
Dim objTable
intNoOfRows = 3
intNoOfColumns = 2
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add
Set objRange = objDoc.Range
objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns
Set objTable = objDoc.Tables(1)
With objTable
.cell(1, 1).Merge Mergeto:=.cell(2, 1)
.cell(3, 1).Merge Mergeto:=.cell(3, 2)
.cell(1, 1).Range.Text = “This is one of the cells” & Chr(10) & “I would like to align (horizontally ) to the right”
En With
End Sub
This is what I currently have, does anyone know what to add in order to change the horizontal alignment?
Found it!:
‘HorizontalAlignment
.cell(4, 1).Range.ParagraphFormat.Alignment = 2 ‘is to the right
.cell(5, 1).Range.ParagraphFormat.Alignment = 1 ‘is to center
How to insert 2 different tables? I’m having a problem creating the 2nd table and it seems like it overwrites the 1st table. Thanks!