Putting Excel data into Table cells?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Hartsough - 20 Oct 2006 21:33 GMT

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

Reply to this Message

Helmut Weber - 20 Oct 2006 22:52 GMT

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

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Greg Maxey - 20 Oct 2006 22:59 GMT

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

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> I've been doing this by hand for a couple years. I'm tired of it.
> Would like to automate it.
[quoted text clipped - 15 lines]
> Thanks,
> Mike

Reply to this Message

Mike Hartsough - 23 Oct 2006 19:15 GMT

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

> Sub Scratchmacro()
> MsgBox "You've made it to step 3 on your own."
[quoted text clipped - 23 lines]
> > Thanks,
> > Mike

Reply to this Message

Greg Maxey - 23 Oct 2006 20:45 GMT

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 ;-)

> Greg:
>
[quoted text clipped - 45 lines]
> > > Thanks,
> > > Mike

Reply to this Message

Mike Hartsough - 24 Oct 2006 14:20 GMT

Your help has been very useful. ;^)

Thanks a bunch!

> Mike,
>
[quoted text clipped - 68 lines]
> > > > Thanks,
> > > > Mike

Reply to this Message