Filling word table with Excel array 1

[PeterJ01]

I am writng an Excel VBA procedure which creates a single dimension array with, say, 100 elements.Is there any simple VBA code to put this array in to the column of an existing Word table?

All suggestions and ideas , or even code, are most welcome.

regards,
Peter

 

[macropod]

Hi Peter,

Which part of the process are you having trouble with:
. populating the Excel array
. instantiating Word from Excel
. opening a Word document from Excel
. finding a specific Word table
. populating a Word table
. something else?

What have you coded so far?


Cheers
[MS MVP - Word]

 

[fumei]

Is there any simple VBA code to put this array in to the column of an existing Word table?"

"Simple" is a relative term, but basically....yes, it is fairly simple.

However, I will echo macropod. Where exactly are you with this? You state "existing Word table", but is that document open...or not? Do you need to....<insert multiple questions>.

Just out of curiosity, from Excel I just:

1. made a single dimension array of 100 elements
2. created an instance of Word
3. created a new document
4. created a table of 100 rows
5. put the elements of the array into incrementing rows

with 27 lines of code. It takes the same amount of code to:

1. make a single dimension array of 100 elements
2. created an instance of Word
3. open document with the table
4. put the elements of the array into incrementing rows

That is fairly simple I would say. We can help, but not without more details. Start with posting what code you have got already.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[PeterJ01]

thanks for these responses. I was able to find a simple code on another forum that I will share with you

It may be helpful to other members of this forum. I am sure the 27 lines mentioned above would alos be helpful. Any chance of sharing this , please Gerry /fumei?

> For x = 1 to ubound(yourarray)
> Cell(x, columnNumber) = array(x)
> next


regards,
Peter

 

[PeterJ01]

In relation to my reply above, it all depends on knowing the exact location of the cell in the Word table. That is the basis of another post, where I ask if it is possible to get the table number n, the column number and the cell number that a bookmarks is located in?

Any help gratefully received,

regards,
Peter

 

[macropod]

Hi Peter,

If you've bookmarked the table, you really don't need to know what the table # might be. For example:

Sub Demo()
Dim oCel As Cell, i As Integer
With ActiveDocument.Bookmarks("MyTable").Range.Tables(1).Range
  For Each oCel In .Cells
    oCel.Range.Text = i
    i = i + 1
  Next
End With
End Sub

will populate all the cells in the table bookmarked 'MyTable'. If the bookmark is in a particular cell, you can likewise update the information in that cell without knowing its address. Knowing the cell address might be useful, though, if you need that as the starting point for populating multiple cells. For this you can use code like:

Sub Demo()
Dim BkMk As String
BkMk = "MyTable"
With ActiveDocument.Bookmarks(BkMk).Range.Tables(1).Range
  With .Bookmarks(BkMk).Range.Cells(1)
    MsgBox "Column: " & .ColumnIndex & ", Row: " & .RowIndex
  End With
End With
End Sub

Cheers
[MS MVP - Word]

 

[PeterJ01]

thanks for this, macropod. I appreciate your efforts in posting these code gems. In fact, the last piece of code is exactly what I was after, as I will be populating multiple columns.

My problem is the lack of knowledge of Word VBA code. I never knew the term " columnIndex" existed.

Can you please recommend any specific texts or online tutorials that provide a good introduction to Word VBA?

regards,
Peter

 

[PHV]

When in VBE play with the F2 and F1 keys.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

My problem is the lack of knowledge of Word VBA code

It'd not the VBA. It's the Word Object Model that has some uniqueness.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Poor Skip.

Peter: "It may be helpful to other members of this forum. I am sure the 27 lines mentioned above would alos be helpful. Any chance of sharing this , please Gerry /fumei?"

Yes I could, but you have to answer what has already been asked of you. Some more details. I do not even know a basic starting point for you. Are you working in Excel, or are you working in Word? In other words....

a) In Excel: getting stuff from the open Excel file and putting into an existing Word doc (which may - or may not - be open);

b) In Word: GOING and getting stuff from Excel (which may - or may not - be open), and putting it into the open Word document.

The code below uses example a) - from Excel, with the document NOT open. So it has to be opened.

Option Explicit

Sub Yadda()
Dim appWord As Word.Application
Dim aDoc As Word.Document
Dim oTable As Word.Table
Dim MyArray()
Dim j As Long
Dim var
For var = 0 To 99   ' makes the array
   ReDim Preserve MyArray(var)
   MyArray(var) = j
   j = j + 1
Next

Set appWord = CreateObject("Word.Application")
Set aDoc = appWord.Documents.Open _
      (Filename:="c:\testarray3.doc")
Set oTable = aDoc.Bookmarks("MyTable").Range.Tables(1)
j = 1
For var = 0 To UBound(MyArray())
   oTable.Range.Cells(j).Range.Text = MyArray(var)
   j = j + 1
Next
aDoc.Save
aDoc.Close
Set aDoc = Nothing
appWord.Quit
Set appWord = Nothing
End Sub

It is VERY important that you properly close and destroy instances of applications and their files...and in the proper order.

In the example above, the Word instance is created, the existing document opened, the existing table filled with the array, the document saved, the document closed, the document object destroyed, the Word instance Quit, and the Word instance destroyed...all without ever seeing Word. Everything happens in the background.

Who knows if this is close to what you want to do. Note also that the code above uses early-binding, so you need a Reference.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry