Solved

Copying data from a table in Word 2010 to Excel 2010

Posted on 2013-11-05
12
3,429 Views
Last Modified: 2014-08-17
I have a table in Word 2010 that contains fairly verbose data in certain cells. Some cells have multi-line data containing bulleted lists, newlines / paragraphs, and all sorts of formatting. Let us refer to these cells as "Complex Cells"

My GOAL: copy this data from this Word 2010 table to Excel 2010, and have EACH cell from the table in Word end up as a cell in Excel.

However, when I copy the table and paste it into Excel, the data from a single Complex Cells ends up in MULTIPLE fields in Excel. For instance, a bulleted list from a table cell ends up spread across multiple rows in Excel.

How do I copy the Complex Cell data from Word to Excel and retain the original cell structure?
0
Comment
Question by:jmohsin
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39624795
It might help to post a small, non confidential sample.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 150 total points
ID: 39624816
Hi,

If you select & copy a single cell of "complex data" from the Word table & then "click into" a single cell of the destination MS-Excel worksheet, press [F2] & then paste ([CTRL]+[V]) can you see the desired result?

(Pressing the [F2] key before pasting is the key step here)

BFN,

fp.
0
 

Author Comment

by:jmohsin
ID: 39625291
Hi fanpages,

The F2 before paste does work; however it works for ONLY one cell of Complete Data at a time. If I have to copy and past one cell at a time, there are multiple ways of accomplishing the goal but it will take too long. Instead of F2, I can also double click before doing a paste.  However, this too works for only ONE cell at a time.

The table has hundreds of cells that contain Complex Data. How do I copy MULTIPLE Complex Cells from Word table and paste into Excel in one shot?
0
 

Author Comment

by:jmohsin
ID: 39625297
Grahamskan,
Sample file attached... It has ONE row from the table; the actual table has about 25000 rows.

The data from a SINGLE Word table cell (shown below) is placed in MULTIPLE rows in Excel when I copy and paste.  Please note that this is a HUGE table; I tried to copy and paste the ENTIRE table.

IN WORD 2010, IN A SINGLE TABLE CELL:
Benefits
•      Updates 99% of free disk space without harming drive.
•      It takes 2 passes before wipes.**
•      From the #1 anti-virus vendor in Finland. *

**In case of virus detection, the process aborts.

SHOWS UP IN SIX ROWS WHEN COPIED AND PASTED INTO EXCEL 2010
sample.docx
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 50 total points
ID: 39626082
Yes. The presentational differences are a reflection of the entirely different objectives of Excel (a calculation-based application) and Word which is intended for text publication.

Hard-coded new lines,  whether new (Word) paragraphs or not, are pasted as separate cells and hence separate rows in the spreadsheet.

It might be possible to create a different presentation using VBA coding, but the desired Word formatting might be difficult to represent. Do you have a specific objective, other than getting the whole paragraph in one cell, that differs from that of default copy/paste process?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39626096
To clarify, I'm thinking  that we could replace the paragraph marks with spaces before copying. This would mean that  line breaks would not necessarily appear as seen in the Word table cell, but at least all the text in the Word cell would appear in a single Excel cell
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 150 total points
ID: 39626728
The F2 before paste does work; however it works for ONLY one cell of Complete Data at a time. If I have to copy and past one cell at a time, there are multiple ways of accomplishing the goal but it will take too long. Instead of F2, I can also double click before doing a paste.  However, this too works for only ONE cell at a time.

Yes, I appreciate that I asked you to copy a single cell of data.  The point for asking was to establish if copy'n'paste was working as expected.  It appears it is.

The table has hundreds of cells that contain Complex Data. How do I copy MULTIPLE Complex Cells from Word table and paste into Excel in one shot?

After establishing that the copy'n'pasting function is performing as intended (by design), I was going to suggest something similar to what GrahamSkan mentioned:

a) Write a Visual Basic for Applications routine to retrieve the data from the MS-Word document & paste (as you intended) into an MS-Excel worksheet, or

b) Find/Replace all line & paragraph breaks to a unique combination of characters not found anywhere else within your MS-Word document text, for example, line-breaks to "[CR]" & paragraph breaks to "[CRLF]" (or similar), copy the resultant text to the Windows Clipboard, paste into MS-Excel, then Find/Replace the unique character string(s) back to their ASCII code equivalents (so that the original formatting is returned to the text once it is within the MS-Excel worksheet cells).
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 39627142
I believe more recent versions of Office are better than their predecessors at integrating information across applications. Have you tried embedding the Word document into your Excel sheet? This will give you the full versatility of Word formatting in the Excel environment.

Conversely, if you require mathematical data in your scenario would embedding an Excel workbook into the Word document be an option. Again this gives the full versatility of an Excel sheet within a Word document. The downside to this way round would be the size limitations of fitting your Excel sheet into the Word page. Excel allows you to scale/zoom the print Area to fit onto a specified page size.

If this is not a suitable suggestion, what is the purpose of showing the Word information in an Excel file? Are you needing to do any further calculations on the Word information.

Thanks
Rob H
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 250 total points
ID: 39631379
Her is a VBA solution using copy from cells in the Word tables, paste in Excel with all the lines in different cells, and then combining the lines into one cell, deleting the rest.

Activate a reference to Microsoft Word Object Library in VBA menu Tools-References.
And set the path and file name to the word file in the line
Set doc = Word.Documents.Open("Full path to Word file")

Option Explicit

Sub WordTableImport()
    Dim obWord As Object
    Dim doc As Word.Document
    Dim docTable As Word.Table
    Dim tblRow As Long, tblCol As Long
    
    Dim ws As Worksheet
    Dim xlsRow As Long, xlsRowMax As Long, xlsRow1 As Long
    Dim strDocContent As String, intPos As Integer
    
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    ws.Cells.Clear
    
    Set obWord = CreateObject("Word.Application")
    Set doc = Word.Documents.Open("Full path to Word file")
    
xlsRow = 0
    For Each docTable In doc.Tables
        
        For tblRow = 1 To docTable.Rows.Count
            xlsRow = xlsRow + 1
            For tblCol = 1 To docTable.Columns.Count
                docTable.Cell(tblRow, tblCol).Range.Copy
                ws.Range(Cells(xlsRow, tblCol), Cells(xlsRow, tblCol)).Select
                ws.Paste
                xlsRowMax = ws.UsedRange.Rows.Count
                If xlsRowMax > xlsRow Then
                    Do While Len(ws.Cells(xlsRowMax, tblCol)) = 0 And xlsRowMax > xlsRow
                        xlsRowMax = xlsRowMax - 1
                    Loop
                    If xlsRowMax > xlsRow Then
                        strDocContent = ws.Cells(xlsRow, tblCol)
                        For xlsRow1 = xlsRow + 1 To xlsRowMax
                            strDocContent = strDocContent + Chr(10) + ws.Cells(xlsRow1, tblCol)
                        Next xlsRow1
                        For intPos = 1 To Len(strDocContent)
                            If Asc(Mid(strDocContent, intPos, 1)) = 183 Then
                                Mid(strDocContent, intPos, 1) = Chr(149)
                            End If
                        Next intPos
                        ws.Cells(xlsRow, tblCol) = strDocContent
                        ws.Range(Cells(xlsRow + 1, tblCol), Cells(xlsRowMax, tblCol)).Clear
                    End If
                End If
            Next tblCol
        Next tblRow
    Next docTable

    Set obWord = Nothing
    
    With ws.Range(Cells(1, 1), Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count))
        .ColumnWidth = 50
        .Columns.AutoFit
        .Rows.AutoFit
        .VerticalAlignment = xlTop
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = -16777216
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        If ws.UsedRange.Columns.Count > 1 Then
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Color = -16777216
                .Weight = xlMedium
            End With
        End If
        If ws.UsedRange.Rows.Count > 1 Then
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Color = -16777216
                .Weight = xlMedium
            End With
        End If

    End With
    
    ws.Range("A1").Select
    Application.ScreenUpdating = True
    
End Sub

Open in new window

1
 

Author Closing Comment

by:jmohsin
ID: 39633945
Thanks, experts! hgholt, your solution works BEAUTIFULLY!
0
 

Expert Comment

by:Alexandra Manoli
ID: 40266012
I have the same problem - but sadly do not understand the answer here i.e. how to use VBA?  I also need to keep the colours of the text in the WORD table when transferred to EXCEL.  I am using Ofiice 2010 - I am able to copy past one cell at a time and keep all formating but not the text colours.  Is it possible? THank you in advance for a prompt reply
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40266391
The question is solved (closed), so you should ask a new question, instead of continuing this old one from last year.
You could make a reference to this question.

When a cell with more lines in a Word table are copied to Excel, it is divided into more than one row.
The VBA code assemble the rows into the first cell, making a line feed for each, so the character colours for all will be the colour in the first cell, = the first line in the Word cell.

It should be possible to read the character colours, and assign them individually.

Probably there are things in your Word document, that are different from the question here, so upload a sample Word document.

- Ejgil
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now