Using excel to add a table in Word document

Question

I'm creating a tool in Excel Which is going to read in some data and the create a word document based on that data.

So far I've got excel to create the word document and add a few lines of text without any issue. The next bit though to add a table is causing issues. I can add the table in fine, but for some reason it deletes the lines of text that I added in the first place.

This is my code:

Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim objSelection As Object
Dim objRange As Object
Dim objTable As Object
Dim ctr as long
            
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
            
Set objDoc = objWord.documents.Add
Set objSelection = objWord.Selection
Set objRange = objDoc.Range

'Adding some heading Text   
objSelection.Style = objDoc.Styles("Heading 1")
objSelection.Font.Bold = True    
objSelection.TypeText ("Heading Text")
objSelection.TypeParagraph

'Adding some normal Text
objSelection.Style = objDoc.Styles("Normal")
objSelection.Font.Bold = False    
objSelection.TypeText ("Normal Text")
objSelection.TypeParagraph

Stop 

'Adding the table    
objDoc.Tables.Add objRange, 10, 2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitWindow

objWord.Quit SaveChanges:=False
Set objWord = Nothing

I put in a stop points after my heading and normal text are added and they appear in the word document fine.(screenshot below)

But as soon as the code reaches the Tables.Add bit, all my text disappears and the document has nothing but the table. (also screenshot below)

I looked around online and tried putting objSelection.Collapse WdCollapseDirection.wdCollapseEnd

before the Tables.Add line of code, but that didn't help.

Timothy Rylatt Timothy Rylatt · Accepted Answer · 2020-09-23T15:48:38

Your code to add a table fails because you are adding the table into objRange which you defined as the entire document.

You should also get into the habit of avoiding use of the Selection object, both in Word and Excel. Not only is it ineffecient (the screen has to be redrawn constantly) it is also error prone as the selection could be changed by the user to something you're not expecting.

The code below should work for you.

   Dim objWord As Word.Application
   Dim objDoc As Word.Document
   Dim objRange As Word.Range
   Dim objTable As Word.Table
   Dim ctr As Long
            
   Set objWord = CreateObject("Word.Application")
   objWord.Visible = True
            
   Set objDoc = objWord.documents.Add

   'Adding some heading Text
   With objDoc.Paragraphs(1).Range
      .Style = objDoc.Styles(wdStyleHeading1)
      .Font.Bold = True
      .Text = "Heading Text"
      .InsertParagraphAfter
   End With

   'Adding some normal Text
   With objDoc.Paragraphs(2).Range
      .Style = objDoc.Styles(wdStyleNormal)
      .Font.Bold = False
      .Text = "Normal Text"
      .InsertParagraphAfter
   End With

   Set objRange = objDoc.Paragraphs.Last.Range
   'Adding the table
   Set objTable = objDoc.Tables.Add(objRange, 10, 2, _
      DefaultTableBehavior:=wdWord9TableBehavior, _
      AutoFitBehavior:=wdAutoFitWindow)

   objWord.Quit SaveChanges:=False
   Set objWord = Nothing

Using excel to add a table in Word document

2 Answers