Copy and Paste - Excel to Word 3

[richardmorris]

I've had this problem before and I don't think I solved it then. I'm trying to copy a table from Excel to a Word document using paste special. If I choose to paste either as a 'picture' or as an 'object' the final few rows don't appear. If I insert an additional row into the middle of the table then it truncates the next row at the bottom when pasted, so it seems to be limiting the size of the paste area in some way, although it doesn't fill the page by a long way.
If I paste as a 'bitmap' it produces the complete table. Does any body know the reason for this, as it's only now and again that it seems to occur?


Cheers.

 

[GTstartup]

Why don't you just highlight the table in excel (left click - drag) hit Ctrl C, go to Word and hit Ctrl V?

Am I missing something?

 

[orenda1168]

GIstartup:

You're not missing a thing.....Ctrl C, then Ctrl V works like a charm!


Orenda

 

[electricpete]

There is a difference.

Paste-special / worksheet object preserves the formula's. You can double click to open this object as excel spreadsheet within word and make any future changes as needed.

Ctrl-v (normal paste) pastes only the results.

If all you need is the results, use ctrl-v.

If you want the spreadsheet, continue using paste special. Then you may need to double-click the pasted spreadsheet to open from within word. Drag the corner to make sure the number of rows/columns you want are displayed.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[orenda1168]

Electricpete:

Right you are! Thanks for the tip/correction.


Orenda

 

[richardmorris]

Thanks for the replies.

The problem I have with using the simple cut and paste (Ctrl C and V) is that it simply reproduces the Excel cells in Word table format. When the table is to big to fit in side the page borders you have to scale it down by adjusting the individual row and column width/heights, which is a lot of extra work when you have to produce fifty odd tables. There doesn't appear to be a way of scaling down the full table by a single reduction factor as you can with say a 'picture' or 'bitmap'.

Pasting it as an 'object' is ordinarily fine apart from what seems to be in this case. If I single click on the object I can adjust the dimensions of the 'border', but this simply shows the same data only larger or smaller. If I actually open up the object inside Word, horizontally I can expand/reduce the viewing area to see more or less data. When I try this vertically, I can expand it to see more data, but when I let go of the drag button it shoots back to its original position I start from. Basically there just seems to be like an invisible buffer preventing the vertical height increasing and staying there. I've taken off the 'lock aspect ratio' prior to opening up the object bur it didn't make any difference.

I don't see this as being a problem easily replicated as this is only the second time I've come across this in years of writing reports.


Cheers

 

[electricpete]

"If I actually open up the object inside Word, horizontally I can expand/reduce the viewing area to see more or less data. When I try this vertically, I can expand it to see more data, but when I let go of the drag button it shoots back to its original position I start from."

I have never experienced the problem of going back to original size. But sometimes I do lose one row off the bottom. I think it may have something to do with a difference between how word and excel treat the font size. If I lose a row off the bottom I just reopen in excel and drag down to get one more row. Like you I have also been working with excel objects in word and powerpoint for years...I have never had one that I couldn't fix this way.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[Failed]

Hi tipers,

Maybe I have not understand the question, but if the problem is space (final rows missing), and as say richardmorris:

'When the table is to big to fit in side the page borders you have to scale it down by adjusting the individual row and column width/heights, which is a lot of extra work when you have to produce fifty odd tables.'

In the speadsheet you can select the rows and columns that you wish to paste and change there the size of row/column in just a couple of clicks.

Then just copy-paste as you want

Hope this will help

 

[richardmorris]

Just read that there is a restriction maximum limit for the amount of information an Excel object can display. They thought it was about 70 rows, which ties up exactly with where my table was being truncated. However, I think this is based on a standard row height of 12.75, so by reducing the row height I can get more rows visible now. This is only applicable for word versions before 2002, apparently for word 2002 there is no set limit, as it will 'adjust the zoom to try and get everything to fit on the page'.

Should have mentioned I was using word 2000, my fault.

 

[electricpete]

Well that's interesting. I didn't read your comments fully before to understand that you had tried everything I suggested and more.

I use Word 2000 as well but I have never put that big of a spreadsheet into it. It's good to know the nature of problems I can expect if I try it.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

One word of caution when pasting as an object: you will paste the whole workbook into Word, even though you may not see everything. We have experienced some embarassment when someone was not aware of this, and sent confidential information contained on other sheets of the workbook to a vendor. So I usually paste as a Word table (simple Ctrl-V, which equals "paste as HTML" I think), or else make sure that the Excel Object that I'm pasting only contains the info that I want to transmit. It is probably a good thing to open up the Excel object from inside Word after pasting, and see if anything is there that shouldn't be.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[trainguy]

Here's a really cool unknown function:

In Excel, highlight the portion you want, then holding down the shift key, open up the Edit menu and select "Copy Picture...". You even get to choose if you want the screen version or the print version!

Then in Word, just Paste, and voila, a picture of the spreadsheet that can be stretched, cropped etc. and prints really well!

I don't know why Micro$oft hides this powerful feature.

tg

 

[JEB66]

Thansk Trainguy, That helped me

 

[Failed]

Hi again tipers,

Thinking in how to paste... This is a litle long or complicated, and even not usefull, but well.

In the preview of the spreadsheet, you have a configuration button where you can select the size or zoom (%) of the spreadsheet to print (maybe in file -> page format you can find the size), so if you print it in adobe format (you need an adobe printer) you can select the table in the adobe document (so, you need to have the no-reader adobe) and paste it in the word file.

Long and strange.

Anyway, hope this will help



trainguy:
I have not tried your tip yet, but it looks very interesting. Thanks.

 

[jmw]

Richardmorris:
You might look at your Word Paste Settings.
I don't know if this will help, an experiment may show whether this makes any difference.

In the tools menu: "options" and then select the Edit tab.
Go to "settings":
Go to "Adjust format when pasting from Microsoft Excel."

The context help advises:

"Specifies the default format for pasting data from Microsoft Excel is the table format in the document to which you are pasting. By using the Paste Opitons Button, you can change the format of data as you paste it."

JMW

http://www.ViscoAnalyser.com

 

[richardmorris]

JMW

Thanks for the suggestion, but I'm using Word 2000 and 'settings' doesn't appear on the edit tab. I aasume this option is given on 2002 onwards.

Cheers.