Help with Excel table pasted in Word?

[Ed]

I regularly copy tables created in Excel and paste them into Word. I have a
macro which creates a text box and pastes in the Excel bit using
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
Placement:=wdInLine, DisplayAsIcon:=False

But it seems the link back to the original Excel file still exists. If I
double-click the Excel table (usually by accident), it opens in Excel. I
realize this is by design, although I've specified Link:=False. But when I
click out of it, the look and format of the table as originally pasted is
gone, and I have to delete that and re-paste in the table.

All I want is to have the table created in Excel pasted into Word,
preserving all text and cell formatting. I don't need a link back to the
program, and I don't need the potential for my formatting to disappear by
accidental double-click, leaving my report reader to wonder about what they
were supposed to be seeing in that table.

What would be a better way to do this?

Ed

 

[Stephanie Krieger]

Hi, Ed,

With the code you provided, you're pasting the table as
an embedded object -- that's why it's opening in Excel
when clicked on.

If you want to paste it as a Word table, but preserve
formatting (note that, depending on the formatting, it
might not look 100% the same -- Word table and Excel
worksheet formatting options aren't identical) ... you
can use either of two methods that do the same thing:

To use PasteSpecial, as you did before -- here's the code
you need:

Selection.PasteSpecial DataType:=wdPasteRTF

(Note that the other variants are optional -- the default
for link is false, so you don't need to include it above)

The other option you have is the PasteExcelTable method.
The variants for this one are required. The following
line of code pastes without a link (first false), without
taking on Word formatting (second false), as Rich Text
(set to True below -- this is what preserves the
formatting)

Selection.PasteExcelTable False, False, True

(Note that the PasteExcelTable method was new in 2002, so
not available if your Word version is earlier than that).

Hope that helps!

Stephanie Krieger
author of Microsoft Office Document Designer (from
Microsoft Learning)
blog: arouet.net

 

[Ed]

Thanks, Stephanie. Unfortunately, I've got Word 2000, so the
PasteExcelTable is not available to me. But I think I can play with some of
these others and get something going.

I appreciate the boost!
Ed

 

[Cindy M -WordMVP-]

Hi Ed,

I regularly copy tables created in Excel and paste them into Word. I have a
macro which creates a text box and pastes in the Excel bit using
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
Placement:=wdInLine, DisplayAsIcon:=False

But it seems the link back to the original Excel file still exists.

As Stephanie explains, you're creating an OLE object, which is why Excel is
opening. She's assuming you still want to work with the table; I get the
impression all you want is to show the table. In the latter case, you should
try pasting in a picture format (you may also want to do the copying in Excel
in picture format, which you can do by holding down SHIFT when you click on
the Edit menu, then you can select "copy as picture")

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Ed]

Cindy, the "Copy as Picture" in Excel worked great! In fact, I think I'm
going to put that on my toolbar!

Thanks so much.
Ed