VBA-Excel: Add Table and fill data to the Word document

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

adjust the size of the table and i want to insert different data in each row：
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

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