ms-accessoffice-interopexport-to-word

Export data from Access table to Word table


I have Access data I'm trying to export to a Word table. The table has 3 columns, the first row and first column are all headers.

I'm trying to loop through the recordset and populate columns 2 & 3 with data. I'm able to start at row 2 and populate columns 2 and 3, but I cannot figure out how to move to the next row.

 iTbl = 1
 irow = 2
 iCol = 1

 Do Until recSet2.EOF
    If irow > wDoc.Tables(iTbl).Rows.Count Then
        wDoc.Tables(iTbl).Rows.Add
    End If

    For Each fld In recSet2.Fields
        On Error Resume Next
        iCol = iCol + 1
        wDoc.Tables(iTbl).Cell(irow, iCol).Range.Text = Nz(fld.Value)
    Next fld
    recSet2.MoveNext
    irow = irow + 1
    iCol = 1
 Loop

Solution

  • Thanks for all the help guys. I managed to figure it out and works very well. It wouldn't move down to the next row and was attempting to write data to column(4) which doesn't exist, then throwing an error. Here is the code I used:

    iTbl = 1
    iRow = 2
    iCol = 1
    
    For Each fld In recSet2.Fields
    iCol = iCol + 1
        If iCol < 4 Then
            wDoc.Tables(iTbl).Cell(iRow, iCol).Range.Text = Nz(fld.value)
        Else
            If iCol > 3 Then
                iCol = iCol - 2
                iRow = iRow + 1
                wDoc.Tables(iTbl).Cell(iRow, iCol).Range.Text = Nz(fld.value)
            End If
        End If
    Next fld