Excel data to Word table

[Jaymond Flurrie]

I have a 3D array, which I create in Excel. The first dimension indicates the file number, the second indicates the column and the third indicates the row (should be vice versa, I know). I try to "print" each first dimension to a Word table, by creating a Word document, creating a table there and printing everything in the particular 2D array into the table.

So for example if I have (3,2,4) it means I have three Word documents, two columns X four rows sized table. The actual number of rows in each table will be much smaller than the maximum number, because I check whether a row has anything to do with this particular 2D array and if doesn't, I just skip over it. The second dimension is always 2.

I know the creation of the array works fine since I have tested it by making a new Excel workbook where I make a sheet for each 2D array.

I've got this up to the point where my lack of understanding of Word VBA becomes the limit. Here's what I have tried:

Sub ControlWord(vPalautteet As Variant)
    Dim appWD As Word.Application
    Dim lEsMiesKom As Long 'The actual row counter in the particular Word table
    Dim wdRngTable As Word.Range   'create a range variable
    Dim j As Integer
    Dim i As Integer
    
    ' Open and show Word
    Set appWD = CreateObject("Word.Application.14")
    appWD.Visible = True

    'Loop thru the files
    For j = LBound(vPalautteet, 1) To UBound(vPalautteet, 1) - 1
        lEsMiesKom = 1 'The number of row for this particular document
        
        ' Tell Word to create a new document
        appWD.Documents.Add
    
        'If table is at end of contents
        Set wdRngTable = ActiveDocument.Content
        wdRngTable.Collapse Direction:=wdCollapseEnd
        
        'Create The Table with one row and two columns
        ActiveDocument.Tables.Add wdRngTable, 1, 2
        ActiveDocument.Tables(1).PreferredWidth = InchesToPoints(10#)
        ActiveDocument.Tables(1).Range.Font.Size = 10
        ActiveDocument.Tables(1).Range.Font.Name = "Arial"
        
        'adjust the column width in inches
        ActiveDocument.Tables(1).Columns(1).Width = InchesToPoints(2#)
        ActiveDocument.Tables(1).Columns(2).Width = InchesToPoints(2#)
        
        'Loop thru the table rows
        For i = LBound(vPalautteet, 3) + 1 To UBound(vPalautteet, 3)
            If vPalautteet(j, 0, i) = vbNullString Then
                'Skip over
            Else
                'Write contents to the table
                lEsMiesKom = lEsMiesKom + 1
                
                ActiveDocument.Tables(1).Range.Cells(lEsMiesKom, 1).Range Text:=vPalautteet(j, 1, i) 'This gives an error
                ActiveDocument.Tables(1).Range.Cells(lEsMiesKom, 2).Range Text:=vPalautteet(j, 2, i)
            End If
        Next i
        
        ' Save the new document with a sequential file name
        appWD.ActiveDocument.SaveAs Filename:=vPalautteet(j, 0, 0)
        
        ' Close this new word document
        appWD.ActiveDocument.Close
    Next j
    
    ' Close the Word application
    appWD.Quit
End Sub

The error comes on the row I try to write to the table. What's wrong? I took the part almost directly from a recorded macro and it seems to work there. The error says "Compile error: Wrong number of arguments or invalid property assignment" and points to that ".Cells" part. Shouldn't I be able to give there (Row, Column) parameters?

Thanks for any help!

 

[Jaymond Flurrie]

I was able to solve it. It should be:


ActiveDocument.Tables(1).Cell(lEsMiesKom, 1).Range.Text = vPalautteet(j, 1, i)
ActiveDocument.Tables(1).Cell(lEsMiesKom, 2).Range.Text = vPalautteet(j, 2, i)