CODEWITHSUNDEEP
vbams-word
gurtej
gurtej

Reputation: 1

VBA to Loop through Table inserted in MS Word

I am new with Word VBA and trying to insert values in the table in word document.

Issue which I am facing is that when i loop through the table its cutting the text before pasting the values copied from excel.

Appreciate any help


For i = 2 To 10
        res = xlApp.VLookup(srch, xlWb.Worksheets("Sheet1").Range("AO:CR"), i, False)
        Selection.TypeText Text:=" " & res
        Selection.MoveRight Unit:=wdCell
    
    '---------------------------------------------------
    
Next i

Expected Outcome: Loop through the excel and paste continue placing the value after the written text in each box and move to next box.

Upvotes: 0

Views: 52

Answers (2)

taller
taller

Reputation: 18943

MoveRight moves the selection to the right and returns the number of units it has been moved. That is, the next cell is selected. Then TypeText overwrites the text.

Microsoft documentation:

Selection.Move method (Word)

Selection.MoveRight method (Word)

Sub AppendToCell()
    ' Check if the selection is in a table
    If Selection.Information(wdWithInTable) Then
        Dim currentCell As Cell, i As Long, res
        For i = 2 To 10
        ' Get the current cell
            Set currentCell = Selection.Cells(1)
            res = xlApp.VLookup(srch, xlWb.Worksheets("Sheet1").Range("AO:CR"), i, False)
            If Not VBA.IsError(res) Then
                With currentCell.Range
                    .Text = Left(.Text, Len(.Text)-2) & " " & res
                End With
            End If
            Selection.Move wdCell, 1
        Next
    Else
        MsgBox "The selection is not in a table cell."
    End If
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166755

This will let you select any cell on the required row, and will fill starting at a specific column on that row:

Sub Tester()
    Dim c As Cell, tbl As Table, n As Long, col As Long, txt
    
    'check we're in a table
    If Not Selection.Information(wdWithInTable) Then
        MsgBox "First select a cell on the row to be filled", vbExclamation
        Exit Sub
    End If
    
    Set c = Selection.Cells(1)    'selected cell
    Set tbl = Selection.Tables(1) 'parent table
    Debug.Print c.RowIndex, c.ColumnIndex
    
    For col = 1 To 5           'fill (eg) the first five cells in the selected row
        With tbl.Cell(c.RowIndex, col)
            txt = .Range.Text  'get the existing text
            txt = Left(txt, Len(txt) - 2) 'remove "end of cell" marker
            'append (with separator if there's any existing text)
            .Range.Text = txt & IIf(Len(txt) > 0, ",", "") & _
                          "more" ' swap your lookup value for "more"
        End With
    Next col

End Sub

Upvotes: 0

Related Questions