Word table into excel cells

**hamilcar1** · 05-18-2010, 02:19 PM

I have a collaboration of word documents that are in tables and I just want to get them in excel. So that each word table cell (no matter what is in it numbers, bullets, etc..) is copied to one excel cell. I know the formatting will go crazy and that is ok... I just don't want every sentence, numbered item, bullet, to be treated in excel as another cell.

For example

Word Table

_______
Types of cars:
Ford
Honda
Toyota
________

**
When I try to import this into excel it comes out like this:
**

Excel WRKB

________
Types of cars:
________
Ford
________
Honda
________
Toyota
________

*
It combines all of the information into 4 cells rather than just one. Any help would be greatly appreciated.

**6StringJazzer** · 05-18-2010, 11:10 PM

I hate to post "I dunno" posts but in this case I'll tell you I've wrestled with the same issue for years without an elegant solution.

When Word copies paragraph breaks and manual line breaks to the clipboard, Excel interprets them as cell delineations, in addition to the actual table cells.

The only approach I can think of, but have never actually implemented, is to write VBA that converts the breaks to some other character first, copies the table, then converts them back to line breaks (Excel and Word use different characters for line breaks). The character code for an Excel line break is 10.

If I find myself with time on my hands in the next day or two I'll see if I can whip something up. It never seemed worth it just for me but if I can help someone else too it might pay off.

**6StringJazzer** · 05-19-2010, 11:20 AM

I wrote some code to take the characters out of the clipboard that seem to be causing the problem but I haven't been able to complete a test because I get this error on the last line:

Automation Error: CloseClipboard Failed

I have been unable to find any documentation about what this actually means or what to do about it.

Please Login or Register  to view this content.

**Jolly_Chollie** · 11-10-2012, 03:26 PM

Here's an Excel Macro that will Paste whatever is Copied, (Go to Word, Select your Table and Right-Click on the X on the Top Left of the Table, and Select Copy from the Drop Down Menu, Alternately, press the Ctrl-C Combination to Copy). Then go to Excel and run this Macro.

Please Login or Register  to view this content.

'Bonus, (Clear ScratchPad just brings up a Blank WorkSheet ~ You could Omit it if you already have a SpreadSheet open.

'I think you'll like the "Clear_ScratchPad" Macro, so I'm Including it below ~ You Will need to Create a SpreadSheet Called ScratchPad.xlsm located at "C:\Users\Public\Documents\Excel Documents\ScratchPad.xlsm".

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**arlu1201** · 11-11-2012, 06:09 AM

Jolly_Chollie,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.