Pasting Excel tables – formatting, position & rows

Topic

New Reply

I often paste tables from Excel into word, and don’t really understand what’s going on, and would like someone to point me towards a good source of information. I have three issues in particular:

1) Cell margins and borders: Whilst word picks up most of the border formatting from Excel, there are some inconsistencies, with the odd cell different from Excel. Is there any reason for this? There are some other formats, such as text orientation, which don’t transfer: should I be looking out for anything else? One particular gripe is that the rightmost character in right justified cells is usually too close to the cell border, often touching when printed. Does word use a default table when pasting such data, or use the “cell margins” from Excel? If the former, I might be able to manipulate it.

2) The position of the pasted table wrt the left margin is not consistent – varying by a few millimetres with no discernable differences in paragraph settings. I’m assuming pasted tables will have consistent properties wrt positioning, as no information on this should come across from Excel.

3) When copying a filtered list, I can’t work out when the hidden rows get copied across from Excel and when they don’t. Once again, there does not appear to be any consistency – even using “Visible cells only” to copy with.

Any information/pointers gratefully received.

Thanks

Reply | Quote

Replies

Hi Mark

I have been looking at this on and off through the day and think I have identified a few things. It appears that when a user paste an Excel sheet into a Word document, word converts it to a word table format and hence all the formatting goes awry from the original. To retain some of this formatting you should use Paste Special.

Edit|Paste Special and Select Microsoft Excel Object

This will retain cell width, font and font orientation, sadly I cannot get font colour to come across….yet. This will effectively embed the Excel sheet as an object in the Word document. This method also does not copy over hidden columns.

Have a go and tell me if this is what yo are looking for.

Reply | Quote

Thanks Jerry,

I should have been a little clearer.

I’m quite happy for word to convert the tables in most instances, as I’ve had problems in the past where I’ve been bitten by links when pasting as an object (especially with charts). I don’t know if a table pasted as an Excel object will be a linked or standalone table. The other reason I’m happy for the conversion is that I’m a “blue background, white text” person, and excel objects don’t “obey” this.

The missing border phenomenon is an annoyance, but I think the above issues would be worse.

The right margin cell padding issue is more irritating though, so I decided to compare the results of pasting as pasting special as excel object – only to receive the error message “Insufficient memory. Save the document now.” This despite having a gig of RAM, nothing else running, and the source workbook being 1.6MB. So closed everything down, still getting problem, restarted, still getting problem, tried creating new document and only pasting test cells in it, same problem, and only got a result by moving the source sheet to a fresh workbook, which kind of defeats the point anyway.

Now things start to get really odd. I’d only selected 6 cells to paste, but double clicking on the object brings up the whole sheet despite the fact I’d deleted the temporary book which I guess might answer my question about the link, but must lead to massive document bloat. This seems to be the case, as the document I’ve pasted 10 charts into is a whopping 31MB.

The cell padding was much better in the pasted object, though, so that issue might be amenable to sorting out in word, though I can’t find anywhere to change the default table settings.

Still wish I knew what was actually going on!

Thanks anyway

Reply | Quote

Thanks for getting back, embedding is not the best way I know as it can cause all types of problems, I think we have just found out the reason for caution, I was unaware of the size of the WB’s. Is it possible for you to link to the workbooks from Word or are they specifically required for hard copy publication…sorry going off track now as it does not solve your original question/problem!

Another idea that was passing through my mind this afternoon was, are there any ‘invisible’ characters in your excel wb, I sometimes get them when I download from legacy systems and make my extractions go squiffy? The reason I ask is I saw something about it whilst researching for you.

Reply | Quote

You may want to take a look at the thread that starts at post 508160

Reply | Quote

Thanks both of you.

I have to say, Jerry, I don’t think that 1.6MB’s terribly huge for a wb to be causing problems, but I agree with you that having been bitten…

I don’t really have a big issue with the charts, or with document bloat per se, as I always convert them to pictures before releasing the document, having been previously been bitten by the “not a link, but linked” problem. I was just noting the document size as an indication that something’s going on behind the scenes, and I’m not sure what.

I would, however, like to understand what goes on when pasting from Excel to Word, and can’t seem to find this info anywhere.

Thanks for all your help

Reply | Quote

Hi Mark,

When you paste a range from Excel into Word as a table, Word uses cell margins of 0.05cm istead of the normal 0.19cm. That would account for the text wrapping close to the borders. I don’t know of any way to change this behaviour, though you can always re-format the cells margins once the table has been pasted.

As far as I know, tables pasted from Excel normally align themselves to the left, regardless of the position of the insertion point or any paragraph styling in the target document. Usually, everything is pasted in ‘Normal’ style, so that style’s paragraph and font attributes will be applied to the cell contents – character formatting (eg bold) remains unaffected.

If you simply copy and paste an Excel range into Word as a table, hidden rows in Excel are pasted too, and become visible. However, if you paste the table as ‘formatted text (RTF)’, the hidden rows are not pasted (or at least remain hidden) – plus, the ‘Normal’ style paragraph and font attributes are not applied.

If you paste a range as an Excel object, the whole workbook is pasted; only the selected range is made visible. This is repeated for every instance of a range from the workbook being pasted, so you can see where the massive increases in file sizes might come from! You can get around this by inserting an Excel object into the Word file in each case, then pasting into that only the portion of the original workbook that you’re interested in. Obviously, you’ll need to allow for referencing issues, so pasting formats and values (ie no formulae) would resolve that. If you paste the object as a link, though, only the link to the selected range is pasted.

With Excel charts, the same issue regarding the whole workbook coming across is repeated. You can avoid this by pasting the chart as a picture.

The other thing to be careful of when pasting Excel objects into word is that Excel and Word don’t have the same interpretation of where line wraps, etc occur, especially when viewing the embedded object on screen. I’ve got numerous such objects that look quite different when embedded in Word to how they look in Excel. Come print time, the results can be different again! So much for wysiwyg.

Cheers
PS: Apart from the issue of maintaining link paths, I’ve never had any significant problems with using linked embedded objects.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks very much, that’s extremely useful information.

The whole workbook, eh?! Not even just the whole worksheet, but the whole workbook. Well that’s something.

Thanks very much for the rtf tip – the cell padding is better doing it that way, but it pastes as black text rather than automatic – still easier to change than selecting offending cells for repadding. Will be using this.

I think my problems with links before may have been that I’ve pasted some as links and some not, and when I return to the document after a period of time, I’m not sure which is which – until there’s a problem with the path and the link breaks. I know I’ve read something recently about how to tell a link from an unliked object – something to do with the border and resizing handle shape and colour – so I’m off to find that again.

Thanks once more – most helpful.

Reply | Quote