Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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 doc­u­ment using Microsoft Excel, you need to fol­low the steps below:

  • Cre­ate the object of Microsoft Word
  • Cre­ate a doc­u­ment object and add doc­u­ments to it
  • Make the MS Word visible
  • Cre­ate a Range object.
  • Cre­ate Table using Range object and define no of rows and columns.
  • Get the Table object
  • Enable the bor­ders of table using table object.
  • Fill the data in table
  • Save the document


 

Cre­ate the object of Microsoft Word

Set obj­Word = CreateObject(“Word.Application”)

Cre­ate a doc­u­ment object and add doc­u­ments to it

Set obj­Doc = objWord.Documents.Add

Make the MS Word Visible

objWord.Visible = True

Cre­ate a Range object.

Set objRange = objDoc.Range

Cre­ate Table using Range object and define no of rows and columns.

objDoc.Tables.Add objRange, int­NoOfRows, intNoOfColumns

Get the Table object

Set objTable = objDoc.Tables(1)

Enable the bor­ders 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”)

 

Com­plete 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
Add Table and fill data to the Word document

Add Table and fill data to the Word document

 


 

You may also like...

17 Responses

  1. Camilo says:

    Thank you for this great post, it helped me a lot!

  2. Rajesh says:

    Thank you very much„ really helped alot.

  3. Jordan says:

    How do you paste a table to an exist­ing word document?

  4. AShok says:

    Hi i want to adjust the size of the table and i want to insert dif­fer­ent data in each row please help me for that

    • Sumit Jain says:

      objDoc.Tables.Add objRange, int­NoOfRows, int­NoOf­Columns „, this way you can define your table size.
      And for fill­ing dif­fer­ent data in each row, while iter­at­ing 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

  5. Luis says:

    I have a list of colon sep­a­rated strings ( I.e. Abc: def, ghi: jkl) that I want to loop through, set the text to the left of the colon as col­umn head­ing ( I.e. abc, ghi), set text to the right of the colon as col­umn value (def,jkl). Any ideas?

    • Sumit Jain says:

      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 fur­ther queries.

      Sumit Jain

  6. Akshay says:

    How to change the font of the con­text of the table. For exam­ple, 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

  7. SuzanDL says:

    For hor­i­zon­tal align­ment in spe­cific cells I have found many online vari­a­tions how­ever none of them seem to work.
    Does any­one know how to hor­i­zon­tally align the text in “objTable.cell(1, 1)” to the right?

    The same counts for a bor­der around a spe­cific range of cells.
    Does any­one know how to add a bor­der around the first five cells in the first column?

  8. SuzanDL says:

    Sub Jus­tARan­domTable ()
    Dim int­NoOfRows
    Dim int­NoOf­Columns
    Dim obj­Word
    Dim obj­Doc
    Dim objRange
    Dim objTable

    int­NoOfRows = 3
    int­NoOf­Columns = 2

    Set obj­Word = CreateObject(“Word.Application”)
    objWord.Visible = True
    Set obj­Doc = objWord.Documents.Add
    Set objRange = objDoc.Range
    objDoc.Tables.Add objRange, int­NoOfRows, int­NoOf­Columns
    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 (hor­i­zon­tally ) to the right“
    En With
    End Sub

    • SuzanDL says:

      This is what I cur­rently have, does any­one know what to add in order to change the hor­i­zon­tal alignment?

      • SuzanDL says:

        Found it!:

        ‘Hor­i­zon­ta­lAlign­ment
        .cell(4, 1).Range.ParagraphFormat.Alignment = 2 ‘is to the right
        .cell(5, 1).Range.ParagraphFormat.Alignment = 1 ‘is to center

  9. Cyrus says:

    How to insert 2 dif­fer­ent tables? I’m hav­ing a prob­lem cre­at­ing the 2nd table and it seems like it over­writes the 1st table. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: