The following code picks and places Excel data to the table in a Word file.
Sub test_table()
Dim objWord
Dim objDoc
Dim objSelection
Dim i As Integer
Dim j As Integer
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
Set objSelection = objWord.Selection
objWord.Visible = True
objWord.Activate
objSelection.Font.Size = 12
objSelection.Font.Name = "Arial"
objSelection.Font.Color = RGB(0, 0, 0)
objSelection.TypeText "Accordingly, the " & vbLf
Set country_table = objDoc.Tables.Add(objSelection.Range, 4, 5)
With country_table
With .Borders
.enable = True
.outsidecolor = RGB(0, 0, 0)
.insidecolor = RGB(0, 0, 0)
End With
.Rows(1).shading.backgroundpatterncolor = RGB(230, 230, 230)
.cell(1, 1).Range.InsertAfter "Part Number"
.cell(1, 2).Range.InsertAfter "Item Description"
.cell(1, 3).Range.InsertAfter "% of change in MRP"
.cell(1, 4).Range.InsertAfter "% of change in offered rate"
.cell(1, 5).Range.InsertAfter "Discount"
For i = 1 To 3
'For j = 1 To 3
.cell(i + 1, 1).Range.InsertAfter ThisWorkbook.Sheets("Overview").Cells(12 + i, 1).Text
.cell(i + 1, 2).Range.InsertAfter ThisWorkbook.Sheets("Overview").Cells(12 + i, 2).Text & ThisWorkbook.Sheets("Overview").Cells(12 + i, 4).Text
If IsError(ThisWorkbook.Sheets("Negotiation").Cells(16, (5 * i) - 2).Value) Then
If ThisWorkbook.Sheets("Negotiation").Cells(16, (5 * i) - 2).Value = CVErr(xlErrDiv0) Then
.cell(i + 1, 3).Range.InsertAfter "NA"
End If
Else
.cell(i + 1, 3).Range.InsertAfter ThisWorkbook.Sheets("Negotiation").Cells(16, (5 * i) - 2).Text
End If
'.cell(i + 1, 3).Range.InsertAfter ThisWorkbook.Sheets("Negotiation").Cells(16, (5 * i) - 2).Text
If IsError(ThisWorkbook.Sheets("Negotiation").Cells(17, (5 * i) - 2).Value) Then
If ThisWorkbook.Sheets("Negotiation").Cells(17, (5 * i) - 2).Value = CVErr(xlErrDiv0) Then
.cell(i + 1, 4).Range.InsertAfter "NA"
End If
Else
.cell(i + 1, 4).Range.InsertAfter ThisWorkbook.Sheets("Negotiation").Cells(17, (5 * i) - 2).Text
End If
'.cell(i + 1, 4).Range.InsertAfter ThisWorkbook.Sheets("Negotiation").Cells(17, (5 * i) - 2).Text
.cell(i + 1, 5).Range.InsertAfter ThisWorkbook.Sheets("Negotiation").Cells(18, (5 * i) - 2).Text
'Next j
Next i
End With
End Sub
At the end of the execution of the subroutine, the cursor is landed the beginning of cell (1,1)
. Whereas, I wanted to place the cursor just after the table in a new line.
The InsertAfter method
does NOT reposition the cursor in Word. To additionally move the cursor, you can use other approaches such as:
Option 1
country_table.Select
Selection.MoveDown Unit:=5, Count:=1
Option 2
objDoc.Content.MoveStart Unit:=1, Count:=country_table.Range.End
objDoc.Content.Collapse Direction:=1
objDoc.Content.Select
The provided code snippet should be integrated right before End Sub
.