Transferring Excel Array to Word table

[JoeS01]

Has anyone had any success in transferring a single dimensional array ( in Excel) to a specific column in Word?

 

[iliace]

I'm assuming you mean the column already exists. Also, the array is in VBA, I'm guessing. Also assuming you already have enough rows in the Word table

If so, you could do something like:

Dim arr() as String ' create the array
Dim obj As New Word.Application
Dim doc as Word.Document
Dim i as Long

' set the array dimensions
' have some code put some values into the array
' create a new document in obj and assign it to doc

' put values of the array into a word table
' in this case, starting in first table, first column, first cell
For i = lbound(arr) to ubound(arr)
  doc.Tables(1).Columns(1).Cells(i - lbound(arr) + 1).Select
  obj.Selection.TypeText(arr(i))
Next i

I'm not exceptional with Word VBA (still haven't finished that book), so there might be a better way, but this should work.

 

[JoeS01]

Thanks iliace, worked well for when there was only a Table located first in the Word document, and no other field entries. The array starts to fill the other entries first, outside the Table.

I was able to increase the number of rows to march the array size.

The problem arises when there are bookmarked fields above the Table.

Am working on it. All suggestions welcomed.

eg If I was to bookmark the first cell in the first column of the table, how would I direct the array to that bookmark?

 

[iliace]

doc.Bookmarks("name").Select

 

[JoeS01]

Thanks for all your help. I found that the following code worked. You will see that I tried to link it to a bookmark in the first cell of the Table, but that approach did not work.

In the end I resorted to the Table.Column.Cell approach for copying each element of the array

The big problem is finding which Table number I am in. Eventually , by trial and error, I found that I was in Table(4)

Is there any way of determining which Table a bookmark is located in?

With wrdDoc
    ' put values of the array into a word table
    ' in this case, starting in first table, first column, first cell
 
    ' go to the starting cell in the Table(4)
    .Bookmarks("TABLE_START").Select
 
    For I = LBound(DocNo) To UBound(DocNo)
 
       ' Add a row  to take  the array line of data
       .Tables(4).Rows.Add
 
      .Tables(4).Columns(1).Cells(I - LBound(DocNo) + 2).Select
      wrdApp.Selection.TypeText (DocNo(I))
 
      .Tables(4).Columns(2).Cells(I - LBound(DocNo) + 2).Select
      wrdApp.Selection.TypeText (VerNo(I))
 
      .Tables(4).Columns(3).Cells(I - LBound(DocNo) + 2).Select
      wrdApp.Selection.TypeText (DrgTitle(I))
    Next I
 
    If Dir(dirPath & "MyNewWordDoc.doc") <> "" Then
        Kill dirPath & "MyNewWordDoc.doc"
    End If
 
    .SaveAs (dirPath & "MyNewWordDoc.doc")
    .Close ' close the document
End With
    wrdApp.Quit ' close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing

 

[iliace]

Once again, you're stretching my Word VBA knowledge. Not that I mind

Try something like this:

Function BookmarkInTable(bkm As Word.Bookmark) As Word.Table
  Dim tbl As Word.Table
  
  For Each tbl In bkm.Parent.Tables
    If bkm.Range.InRange(tbl.Range) Then
      Set BookmarkInTable = tbl
      Exit For
    End If
  Next tbl
End Function

The function takes a bookmark object and returns a table object within which the bookmark is contained.

Note that, if the bookmark is not within any table, you'll get Nothing.

 

[iliace]

As a side comment - this If statement is always true:

If Dir(dirPath & "MyNewWordDoc.doc") <> "" Then
        Kill dirPath & "MyNewWordDoc.doc"
    End If

even if dirPath is an empty string, the "MyNewWordDoc.doc" is still appended, so it's never = "".

 

[JoeS01]

thanks for thsi iliace, it is certainly a trap for young players. Waht would you suggest as a test? IsNotEmpty?

regards,
Joe

 

[iliace]

If you're talking about the function I gave you to find the table containing a bookmark - what you would do is something like:

Dim tbl As Word.Table

On Error Resume Next
Set tbl = BookmarkInTable(wrdDoc.Bookmarks("TABLE_START"))
On Error GoTo 0

If tbl Is Nothing Then
  ' bookmark is not in table
Else
  ' run your code for putting array into the table
  ' use tbl as the table object
End If

If you're talking about the If statement, then I'm not sure what you're trying to do. If you're trying to check whether there is already a file that exists with that name, you could do the same thing as with the table bit above - try to open it bound to a separate Document object, and check whether it Is Nothing. All I was trying to say is that the code:

        Kill dirPath & "MyNewWordDoc.doc"

will always run, the way you wrote the If statement, so you might as well get rid of the If statement.

 

[JoeS01]

awesome! many thanks iliace.

just 2 more questions to push the boundaries of your Windows VBA, please?

1. At the end of this, do you know of a statement that will get me back to the start of the table?

2. I will need to put another array, same length, in an adjacent column in the table. How do I do this, without adding extra bookmarks. The tow arrays start on the same row number