How to insert two tables in Word via macro from Excel RRS feed

  • Question

  • Hello together,


    I briefly explain you what I want to achieve:


    I have an Excel-worksheet with a lot of information. Parts of this information (e.g. columns A and B and C) I want to insert into a Word table. This table should look like - think of - name / business cards. Thus, every row in Excel shall result in its own table in Word.


    Now, here is what I have done:

    Private wordApplication As Object
    Private wordDocument As Object
    Private wordTable As Object
    Sub ConvertToWordShort()
     Set wordApplication = GetObject(, "Word.Application")
     Set wordDocument = wordApplication.Documents.Add
     ' Read the number of rows
     Set reqWorksheet = Worksheets("Sheet")
     For rowNumberCounter = 3 To Worksheets("Sheet").Rows.Count
      If reqWorksheet.Cells(rowNumberCounter, 1).Value <> "" Then
       ' Create table in Word and merge cells as needed
       Set wordTable = wordDocument.Tables.Add(wordDocument.Range, 4, 2, wdWord9TableBehavior)
       wordTable.Cell(3, 1).Merge (wordTable.Cell(3, 2))
       wordTable.Cell(4, 1).Merge (wordTable.Cell(4, 2))
       wordTable.Cell(1, 1).Range.Text = reqWorksheet.Cells(rowNumberCounter, 1)
       wordTable.Cell(1, 2).Range.Text = reqWorksheet.Cells(rowNumberCounter, 2)
       wordTable.Cell(2, 1).Range.Text = Replace(reqWorksheet.Cells(rowNumberCounter, 5), Chr(10), Chr(11))
       wordTable.Cell(2, 2).Range.Text = reqWorksheet.Cells(rowNumberCounter, 3)
       wordTable.Cell(3, 1).Range.Text = Replace(reqWorksheet.Cells(rowNumberCounter, 4), Chr(10), Chr(11))
       wordTable.Cell(4, 1).Range.Text = Replace(reqWorksheet.Cells(rowNumberCounter, 6), Chr(10), Chr(11))
       wordDocument.Paragraphs.Add (wordDocument.Range)
       Exit For
      End If
     Next rowNumberCounter
    End Sub


    The problem that occurs is that I cannot insert the second table in Word. I expect that I have to move the cursor to the end of the document, but I do not know how I can achieve this. I have searched the Internet for nearly three days now, figured out approx. 20 ways like Selection, GoTo, EndKey, etc. but nothing worked.


    Do you have any idea?

    Wednesday, February 23, 2011 11:34 AM


  • Try

        Dim wordRange As Object
        Set wordRange = wordDocument.Content
        wordRange.Collapse Direction:=0 ' wdCollapseEnd
        Set wordTable = wordDocument.Tables.Add(wordRange, ..., ..., wdWord9TableBehavior)

    Regards, Hans Vogelaar
    Wednesday, February 23, 2011 7:27 PM