Putting Excel data into Table cells?

[Mike Hartsough]

I've been doing this by hand for a couple years. I'm tired of it. Would like
to automate it.

I would like to write a Word macro that will:
1). Open a spreadsheet;
2). Look for specific text in Column A ("New");
3). Create a new table in Word.
4). Copy the content of certain cells of that row into a Word table;

I think I can handle 1, 2 and part of 4. What I'm uncertain of is how to
create that table using Word VBA, and how to insert data into specific cells
of the table.

I've done some Excel and Outlook VBA, no Word VBA.

Any help/pointers will be appreciated.

Thanks,
Mike

 

[Helmut Weber]

Hi Mike,

there must be more to it than meets the eyes.

Recording and modifying the insertion of a table
can't be a problem.

For getting the values from Excel-cells
into Word-table-cells:

Pseudocode:
Activedocument.tables(1).cell(4,1).range.text =
activesheet.cells(4,1).value

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg Maxey]

Sub Scratchmacro()
MsgBox "You've made it to step 3 on your own."
'Create a 5x5 table at the IP
ActiveDocument.Tables.Add Selection.Range, 5, 5
'Stick your Excel test in the center cell
Selection.Tables(1).Cell(3, 3).Range.Text = "Your found Excel Text"
End Sub

 

[Mike Hartsough]

Greg:

Thanks for the info.

"IP" I take it means "insertion point"?

I have already manually created the "style" of table I'd like to use - it
has several merged cells. Is there any way I could save this table somewhere
and reference it to create a new instance of it? Or perhaps a way I can take
its measurements so that I can re-create it in VBA?

Any caveats if the Excel cell contains a hyperlink?

Thanks again,
Mike

 

[Greg Maxey]

Mike,

Yes IP means insertion point.

Perhaps you should use a template with your custom table included as
the destination for your excel data.

Sub FillTable()
Dim myTbl As Word.Table
Set myTbl = Documents.Add("C:\Table.Dot").Tables(1)
myTbl.Cell(3, 3).Range.Text = "Your found Excel Text"
End Sub

Or you could create an autotext entry of your table and insert the
autotext at the IP.


There may be problems introduced by your merging of cells. The above
code work with a very simple test.

Any caveats if the Excel cell contains a hyperlink?

My usefullness has run out ;-)

 

[Mike Hartsough]

Your help has been very useful. ;^)

Thanks a bunch!