From Excel --> create table in Word and add text to cells via VBA

[MalcolmReynolds]

Is it possible to create a table in Word from Excel, and then use code to add text in particular cells in the new table?

I have code to create a new word doc, but am not sure how to go about adding the table or adding text in particular cells. I'm OK with Excel/Access VBA, but haven't had to use it in Word.

    Dim wApp As Object

    Set wApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Err.Clear
        Set wApp = CreateObject("Word.Application")
    End If

    wApp.Visible = True
    wApp.Documents.Add DocumentType:=0

    'Add table
    'Set values of table cells

Is what I'm after possible?

Thanks in advance!

 

[Trevor G]

Welcome to the forum, why not look to record a macro in Word to create the table and then add some text or create BookMarks as you record that will give you the code you need to add your data from Excel, into the correct place.

 

[MalcolmReynolds]

TrevorG,

Thank for your response!

I did try to record a macro and only got as far as creating a table. But while recording the macro I was only able to press TAB to go to the next cell in the table. Wasn't able to do anything else with the table, e.g. insert new row.

    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4, NumColumns:=3, _
                              DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    Selection.TypeText Text:="a"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="b"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="c"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="1A"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="1B"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="1C"

A quick search and I found these two links which I think should help me do what I want. Google is my friend.
https://msdn.microsoft.com/en-us/library/tkf9d64e.aspx
https://msdn.microsoft.com/en-us/library/office/ff838946.aspx

Again, I appreciate your help!

 

[Macropod]

The macro recorder is hopelessly inadequate for producing the code required for this.

1. Where do you want to run the macro from - Word or Excel?
2. If the macro is being run from Excel:
a) are you populating: an existing Word document (including a new Word document based on a template containing the required base content - which includes a table that can be updated); a new or existing Word document that lacks both the table and any other content; or something else?
b) if its an existing Word document, is the filename & path always the same at the time the macro runs? This could include a new document that the macro creates from a template you've created for the purpose. If not, what rules can be applied to identifying the document, or do you want the user to select it somehow?
3. If the macro is being run from Word:
a) are you always extracting the data from the same workbook & worksheet? If not, what rules can be applied to identifying them, or do you want the user to select them somehow?
c) how is the macro to identify what ranges to extract the data from?

 

[MalcolmReynolds]

Macropod,

I'm wanting to run this code from Excel.

Here's the steps I'm envisioning:

1. Create a new word document from Excel.
2. Insert a new table into Word document with predetermined number of columns, and start with 2 rows
3. Add header text in first row
4. In second row, fill out word table cells with values (which will be retrieved from Excel VBA from various places in the Excel workbook)
5. Add new row in Word table if Excel code determine a new row is required and fill out new row. Basically, I have a loop in Excel that will be looking for data that needs to be added to the word document.
6. Repeat step 5 as needed.
7. Save Word document with a file name determined in Excel VBA.

Now, I'm not stuck to the idea of having to create a new Word document. If it's easier, I can see having an existing Word document that already has a table with headers and an empty first row, and having Excel open that file. That would replace steps 2 & 3.

I hope that was detailed enough to give you an idea of what I'm after.

Some dummy code that might be helpful

    Dim wApp As Object, iRow As Long, iCol As Long

    Set wApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Err.Clear
        Set wApp = CreateObject("Word.Application")
    End If

    wApp.Visible = True
    wApp.Documents.Add DocumentType:=0


    'Add table in word documen(e.g. 2 row X 3 columns)

    'Set values of table cells (Pseudo-code)
    WordTable.Cell(1, 1) = "Header 1"
    WordTable.Cell(1, 2) = "Header 2"
    WordTable.Cell(1, 3) = "Header 3"


    'Alternatively, open an existing Word document with existing header row & empty row beneath that

    For iRow = 2 To 4
        For iCol = 1 To 3
            WordTable.Cell(iRow, iCol) = "(" & iRow & ", " & iCol & ")"
        Next iCol
        If iRow <> 4 Then WordTable.Row.Add (AfterLastRow)    'Pseudocode
    Next iRow

    'Save word document as "NewWordDocument.docx"

Again, I appreciate you taking the time to reply. Any help or direction that you can provide would be appreciated. Thank you!

 

[Norie]

Where does the data in Excel come from and where does it go?

Is the data in Excel already in a tabular format?

Here's some simple code to create a new Word document and add a table with headers and one row of data, both of which have hard-coded values.

Sub CreateTableInWord()
Dim objWord As Object
Dim objDoc As Object
Dim objTbl As Object
Dim objRow As Object
Dim objCol As Object
Dim lngCols As Long
Dim lngRows As Long
Dim I As Long

    lngCols = 3
    lngRows = 1

    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True

    Set objDoc = objWord.Documents.Add(DocumentType:=0)

    Set objTbl = objDoc.tables.Add(Range:=objDoc.Paragraphs(1).Range, NumRows:=lngRows, NumColumns:=lngCols)

    Set objRow = objTbl.Rows(1)

    For I = 1 To lngCols
        Set objCol = objRow.Cells(I)
        objCol.Range.Text = "Header" & I
    Next I

    Set objRow = objTbl.Rows.Add

    For I = 1 To lngCols
        Set objCol = objRow.Cells(I)
        objCol.Range.Text = "Data" & I
    Next I

    Set objCol = Nothing
    
    Set objRow = Nothing
    
    Set objDoc = Nothing
    
    Set objWord = Nothing
    
End Sub

 

[Macropod]

I'm wanting to run this code from Excel.

Here's the steps I'm envisioning:

1. Create a new word document from Excel.
2. Insert a new table into Word document with predetermined number of columns, and start with 2 rows
3. Add header text in first row
4. In second row, fill out word table cells with values (which will be retrieved from Excel VBA from various places in the Excel workbook)
5. Add new row in Word table if Excel code determine a new row is required and fill out new row. Basically, I have a loop in Excel that will be looking for data that needs to be added to the word document.
6. Repeat step 5 as needed.
7. Save Word document with a file name determined in Excel VBA.

It's a bit hard to know how to progressively add rows without knowing how your loop works. Here's one possible implementation. In reality, if I knew the extent of the loop (as LRow in the code below implies), I'd just use that + 1 to create a table with that many rows.

Sub PopulateWordTable()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdTbl As Word.Table
Dim xlSht As Worksheet, lRow As Long, lCol As Long, r As Long, c As Long
Set xlSht = ActiveSheet: lCol = 3
With xlSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
  lRow = .Row
End With
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc
    Set wdTbl = .Tables.Add(Range:=.Range, NumRows:=2, NumColumns:=lCol)
    With wdTbl
      .Rows(1).Range.Font.Bold = True
      .Rows(1).HeadingFormat = True
      .Cell(1, 1).Range.Text = "Header 1"
      If lCol > 1 Then .Cell(1, 2).Range.Text = "Header 2"
      If lCol > 2 Then .Cell(1, 3).Range.Text = "Header 3"
    End With
    With xlSht
      For r = 1 To lRow
        If (r + 1) > wdTbl.Rows.Count Then wdTbl.Rows.Add
        For c = 1 To lCol
          wdTbl.Cell(r + 1, c).Range.Text = xlSht.Cells(r, c).Text
        Next c
      Next r
    End With
  End With
End With
Set wdTbl = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End Sub

Note: The code uses early binding, so you need to set a VBA Reference to Word.

 

[MalcolmReynolds]

Norie and Macropod,

Thank you so much for your replies! I haven't had a chance to try them out but I think they are what I want. I'll post back one way or anything once I get the time to try it out.

Insofar as knowing ahead of time how many rows I need, I'm just looking for data as a go along in the loop. I supposed I could do that first just to count the number or rows I'd need, and then run basically the same loop again after creating the table to actually fill in the data but I'm not sure that would be efficient.

Thank you all for your help so far! Much appreciated!!!