Excel data to Word table

Jaymond Flurrie · #1 02-27-2011, 12:21 PM

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:

Code:

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.

Thanks for any help!

macropod · #2 02-27-2011, 07:37 PM

Hi Jaymond,

What is the error message? and what do you get if you use the line:
MsgBox vPalautteet(j, 1, i)
just before the error line? If this gives the same error, that suggests there is a problem with the way you're referencing vPalautteet

Jaymond Flurrie · #3 02-27-2011, 07:42 PM

It was actually about using the cells wrong. So yes, this is solved.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use changeable table data in doc text?	rtlight	Word Tables	4	02-25-2011 07:29 PM
inserting a string of data into an MS Word table???	matto	Word VBA	0	07-16-2010 09:35 AM
pivot table source data	hannu	Excel	0	07-03-2010 04:54 AM
Excel pivot table into an MS Word Doc "office 2007"	wmarsh3561	Excel	1	01-09-2010 08:03 PM
Automate Data from Excel into Word DropDown...?	sigraves	Word VBA	0	08-03-2009 06:54 AM