Copy Microsoft Word table cell content including line breaks into Excel cell via VBA

[abdulhaque]

Hi all,

I have the below VBA code that will copy the contents of a Microsoft Word table cell and paste it into an Excel cell.

Cells(x) = WorksheetFunction.Clean(.Cell(x).Range.Text)

It works apart from picking up line breaks. So if the Word table cell reads

Username: CH_CLERICAL1
Password: CH_CLERICAL1

This will be copied and pasted into Excel as

Username: CH_CLERICAL1Password: CH_CLERICAL1

Can the above code be improved so it copies/pastes line breaks too?

Thanks

 

[Macropod]

Are you sure there are line breaks in the Word cell and that what you're seeing is not just text wrapping, perhaps accompanied by tabs (i.e. →)? If there are breaks, are they paragraph breaks (i.e. ¶) or line breaks (i.e. ↵)? Do the Word cells contain formfields or content controls?

 

[abdulhaque]

Sorry they are paragraph breaks. I have other issues too, the code doesn't pick up table numbering or cross referencing or document property fields. Ideally the data that can't be copied and pasted as the source data in Excel should be copied and pasted in a basic text format..

 

[Macropod]

Perhaps then you could start off by telling us - in detail - what these cells do contain and how you want that content processed.

 

[abdulhaque]

Each cell in the Word table can contain any of the following.

1. Text sentences, some words in bold, italic, bold and italic.
2. The header row cells are filled with a light grey shade.
3. Every cell has wordwrap enabled.
4. Inline small image, size of small icon, in text sentence.
5. Table numbering in first column of each row apart from header row.
6. Crossreferencing of table numbering in text sentence.
7. Crossreferencing of another table numbering in text sentence. The script pulls data from each table with each execution of script.
8. Document property fields.

I would like Excel to process data from each Word table cell as is, so when pasted in Excel cell, it holds the formats etc. If there are limitations, then at the very least the copied data should be pasted as raw text format in Excel. Currently the document property fields are pasted as text in Excel. But it ignores paragraph breaks, numbering, text formats, cell formats, inline images.

 

[Macropod]

So, if you want the entire table, with formatting, why are you trying to work with individual cells instead of copying & pasting the entire table?

 

[abdulhaque]

I'm not very versed with VBA yet, so have been picking examples from here and there and gluing them together. Does a simple copy and paste function exist for tables across Word and Excel?

 

[Macropod]

Sure you can do that. Simply turning on Excel's macro recorder before pasting a table copied from Word will provide the basic code.

Do bear in mind though that, whatever method you use, Word tables with:
1. Columns with varying cell widths
2. Split cells
cannot be replicated in Excel. Similarly, Word fields, such as those used for document properties and cross-references, as well as auto paragraph numbering cannot be replicated in an Excel worksheet - the most you'll get for these is the corresponding textual content and formatting. Where a Word table cell has multiple paragraphs, or paragraphs with line-break characters, the paste process will create extra rows in Excel for these. While there are workarounds, they result in most other text formatting being lost.

If none of those outcomes is acceptable, you have two other options - paste the Word table as a:
1. Word object; or
2. picture (enhanced metafile)
both of which retain the original table's look, but don't otherwise interact with anything in Excel.

 

[abdulhaque]

The only code I get is

Sub Macro8()
ActiveSheet.Paste
End Sub

I understand the limitations, I'll probably try and improve the display via VBA post pasting the table.