Pasting Excel to Word Table (Word XP SP2)

Topic

New Reply

Under Word/Excel 2000, when I pasted group of Excel cells into a Word table (using Paste Special|Formatted Text (RTF), the colors I used in Excel came through to Word. But in Word XP, using the same exact procedure, all the colors are gone and everything is black & white. I have tried other forms of paste special (HTML, etc.) and all are the same. Additionally, my Arial fonts in Excel get changed to Times Roman in Word. Form what I can find in the help file, it implies or says that formatting SHOULD be retained.

I do a lot of these Excel to Word pastes as we use this process for creating contracts in my work. I really don’t want to have to waste a lot of time recreating the coloring/shading in Word. Anyone have any solutions for this?

Reply | Quote

Replies

You could try pasting the Excel data as an Excel Object, instead of as a Word table (ie Edit|Paste Special|Microsoft Excel Worksheet Object). If you use the ‘Paste Link’ option with this, you’ll also be able to have your document update directly from the original workbook without having to do a new paste. Another advantage is that an embedded Excel object can be stretched/shrunk to fit the space available with much more flexibility than a Word table. The main disadvantage is that it’ll probably make your Word document bigger and that refreshing the links sometimes causes the embedded object to change size.

Hope this helps

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

One, I don’t even know if pasted objects will have the same problems.
Two, I can’t do this anyway since the Word document is sent to a customer. I can’t allow them to see the spreadsheet (internal pricing, etc.).

Reply | Quote

RE: One, I don’t even know if pasted objects will have the same problems.
A: There’s one sure way to find out – try it!

RE: Two, I can’t do this anyway since the Word document is sent to a customer. I can’t allow them to see the spreadsheet (internal pricing, etc.).
A: After pasting (forget the ‘paste link’), open the Excel object, select cand copy the whole worksheet, then do an Edit|Paste Special|Values to remove all formulae. Follow this by deleitng any unwanted ranges & worksheets. Alternatively, if the worksheet is formatted so that all cells are locked and hidden, with password protection, it is unlikely that your customers will be able to get at the underlying data or formulae.

I don’t have XP to play with so, if you don’t like the above, you’ll have to wait and see if anyone else has an answer or figure out something for yourself.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Update: I’m working with Microsoft on this problem. We’ve found that if I reset the Word options to the defaults, Word2002 will act the same as Word2000 (and both still lose merged cells and alignment from Excel to Word). The question now is what particular option being enabled causes the problem. This will take extensive testing, turning one option on at a time (there are many that I customize or enable) and repeating the test each time.

Reply | Quote

Thanks for the update.
If you find what’s causing this, I’m sure there’ll be others here who’d appreciate knowing how to solve this problem. So please let us know how you get on.
Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

There is a setting in Tools/Options/Edit/Settings called “adjust formatting when pasting from Excel”. If you turn this on it seems to then strip out colours/fonts etc from your tables when you paste them. Maybe this is the setting causing you problems….

Hope this helps.

Reply | Quote

Looking back through the thread, it looks like you (B.Olds) had the solution 6 weeks ago. I don’t know how I missed your post. Would have saved me a lot of time. Oh well….

Reply | Quote

The dweeb who was assigned my problem on MS support was seemingly IQ challenged and had a problem understanding English. If you’ve ever submitted a problem to MS via the free email support you know that MS surrounds their email communications with all sorts of platitudes, things like:

“Delighting our customers is our top priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please e-mail us at managers@Microsoft.com?”

I complained to the email address above about the problems I was having. This generated 2 responses:

1. 9/18/2002

“This is Paul, I am Maynard’s manager. Your issue was raised to my attention for review. I was able to go over your case notes and I see that you seem to have been receiving duplicate mails from Maynard. I do apologize for any inconvenience that this may have caused. I will definitely work on this issue with our IT team and will ensure that this will be addressed at the earliest possible time. For the meantime, should you have any issues responding to us via web support, you may write to me directly at v-1paulc@mssupport.microsoft.com. Please indicate the case number on the subject line.

Regards,

Paul Jason Cu
Product Support Manager
Microsoft Office Support”

2. 9/20/2002

“Thank you for letting us know that your problem has not been resolved. I am forwarding this message to the Support Professional’s manager for immediate attention.

Your feedback is very important to us. Microsoft is committed to customer satisfaction and it is only with the help of our valued customers that we can achieve this goal.

Please let me know if there is anything else I can do for you relating to this case.

Sincerely,

Tim Teeter
Consumer Support Team Manager”

From the above messages, one might be deluded into expecting that MS WANTED to resolve this problem and would get right on it. You would be wrong. I have sent 2 subsequent messages to each of the above and as of today, 10/2/2002, have yet to receive any follow-up response. Neither has the online incident been updated. Meanwhile, the problem is still festering, sigh…

What was that about “delighting our customers” [roflol]? This is another clear example of Microsoft “talking the walk” instead of “walking the walk”.

Reply | Quote

As per my previous message, I wasn’t getting any support from Microsoft on this open problem (to refresh: pasting from Excel into a Word table while retaining the formating, including colors) and I was losing my patience. There is little more frustrating in the online support world than having your emails ignored!

So I decided to try to escalate the problem. I called Redmond directly and asked for Steve Ballmer’s office . Of course, the operator told me she couldn’t pass me over there. However, she said that the procedure for escalation was for her to take down the problem details and then pass them to the proper department, which I would get a call from. To my surprise, I actually got a call from someone who had been assigned to my problem. He was a level 2 support person. We communicated via email except for the last conversation.

Over 4 days or so, working together, we figured out what the problem (and solution) was:
In WordXP on the Options|Edit tab is a parameter called “Smart cut and paste”. Downlevel of this parm is another full set of “Settings”. One of these – “Adjust formatting when pasting from Microsoft Excel” was the root of the problem. When I turned this off everything worked nicely. See http://support.microsoft.com/default.aspx?…b;en-us;Q297680

I also discovered that using a paste method (for the Excel spreadsheet to a Word table) of HTML, rather than RTF, retains alignment and merged cells.

Reply | Quote

Mac,
I’m having a problem resizing the spreadsheet once it has been pasted-not all of the spreadsheet is pasted. Any pointers?
Thanks,
Jeff

Reply | Quote

Hi Jeff,

When you insert an Excel worksheet object and paste material into it, you may need to increase the number of visible rows/columns to see all of what you’ve pasted. To do this, double-click on the embedded object to open it – you’ll know it’s open by the appearance of the Excel menu and row/column tags. Now, if you look at the outer edges/corners of the object, you’ll see small black ‘handles’ (different to the squares you’d see if you clicked on a text box, or if you’d clicked on the embedded object only once). By dragging these you can change the number of visible rows/columns. I think the upper limit is about 20 columns and 73 rows but, even to get that, the object will have to be scaled small enough to fit on one page.

If you need to display multiple pages worth of the object, you’ll need to have multiple copies of it, each displaying the relevant portion. You can do this by just pasting one page’s worth of data at a time into each new object, or you can paste the lot at once and just scroll so the upper left corner of what you want to display is in the upper left corner of the visible part of the worksheet. The second option will probably produce a larger file size, though.

Hope this helps.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Macropod,
I can’t seem to get it to work. I have attached a file that I want to inset the area Q1:AE35 into a landscape mode Word document. Would you mind working your magic?
Also, if I have a multiple page Excel worksdheet, what do I do?
Thamks,
Jeff

Reply | Quote

Hi Jeff,

I’ve embedded two copies of your spreadsheet in a Word doc in the attached zip archive. The first copy displays the range A1:K35, and the second displays the range A:36:K64. I had to re-format some of your column widths to persuade the embedded worksheets to display all of the columns. If you double click on either of them, you’ll see that you can scroll up/down to change the top-left cell of the displayed range. You control the bottom-right extent of the displayed range by dragging the object handles while the object is open.

When I scrolled across to the Q1:AE35 range you asked for, there was nothing there to display, so I didn’t do anything with that. Still, the copies I set up for you do show what can be done.

Note too how large the Word document is: embedding the whole worksheet each time can use up a lot of memory and file space, so it may be better to embed on the portions you need each time, positioning each to start at A1.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

(Edited by Phil Rabichow on 23-Sep-02 13:10. )

Hi Jeff:
I haven’t used Word 2002. However, in Word 2000, even though Excel says it will truncate the table (depending upon how many columns & width), you can see the “cut off” columns in normal view & can then autofit to window.

Edited to addTo get everything to fit in Word 2000, I forgot to mention that you must go to Table Properties/Options & uncheck “resize columns to fit text” (or something similar, I don’t have access to Word 2000 at the moment).end edit

Hope this helps,

Reply | Quote

I sent this to someone via email not too long ago. Tho their problem was pasting into PowerPoint, many of the same issues occur with Word:

Generally, this behavior is caused by a number of things. Though I’m not able to determine specifically from your examples, here are some reasons and workarounds–some of which aren’t fun but are effective:
–The page setup in the Excel file is set to be centered on the page horizontally or vertically, or to *Fit to*
–You are trying to copy too many columns (I believe seven is the limitation that MS uses, but that’s ridiculous)
–SR1 or SR2 in PowerPoint 97 fixed this issue SOMEWHAT
–I recall that Windows NT4 machines had this problem
–Even your printer driver can affect this outcome
–Instead of Paste, choose Paste Special-As a picture
–Select your Excel data, choose the Zoom dropdown and choose Selection, save the Excel file, THEN copy and paste (supposed NT4 fix)
–Paste it into a landscaped Word document first (as a picture or not) and then copy from there and paste into PowerPoint
–Instead of copy and paste, choose Insert-Object, then Create from file, and choose your Excel file, which must have been saved with YOUR cells on the top worksheetOf interest: I had no problem in PowerPoint 97 (SR1) or PowerPoint 2000 (SR1), and I am on Windows XP Pro operating system.I wish I had THE answer, but to my knowledge, no one does. While I KNOW for a fact that MS Technet used to offer more information, it seems that they’ve reduced it to the following article, which regards PowerPoint XP:
http://support.microsoft.com/default.aspx?…b;en-us;Q285303%5B/url%5D
Hmm…I just found this page, which proved interesting and you may want toreview:
http://www.rdpslides.com/pptfaq/FAQ00068.htm%5B/url%5D

Hope it helps.

Reply | Quote