Import Word table into excel with line breaks

KeepOnWheels · May 14, 2015

Hello Guys!

This is my first post here. Glad to be here.

I'm trying to import tables from Word file to my excel sheet with VBA macro:

Code:

[COLOR=#405A04][FONT=Helvetica Neue]With wdDoc
    TableNo = wdDoc.tables.Count
    If TableNo = 0 Then
        MsgBox "This document contains no tables", _
        vbExclamation, "Import Word Table"
        Exit Sub
    'ElseIf TableNo > 1 Then
    '    TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
    '    "Enter table number of table to import", "Import Word Table", "1")
    End If
    
    
    For gettable = 1 To TableNo
    
        finalrow = Cells(Rows.Count, 1).End(xlUp).Row
        
        With .tables(gettable)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 2 To .Rows.Count
                For iCol = 1 To .Columns.Count
                    Cells(iRow + finalrow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                Next iCol
                Application.StatusBar = "Importing table no. " & gettable & " / " & TableNo & ", line: " & iRow & " / " & .Rows.Count
            Next iRow
        End With
    Next gettable
End With
[/FONT][/COLOR]

My problem is that in the word table line breaks are used. Something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name if client: Hello
987888 Street Canada[/TD]
[TD]Customer No. 8855
Type: None[/TD]
[/TR]
[TR]
[TD]Name if client: The second
985888 Toronto[/TD]
[TD]Customer No. 8889
Type: None[/TD]
[/TR]
[TR]
[TD]Name if client: The third customer
98848788 Berlington[/TD]
[TD]Customer No. 7785
Type: Something[/TD]
[/TR]
</tbody>[/TABLE]

When I'm importing the data with the above method, the line break information get disappeared. I need to know where is the data separated because I need to past them to separate column.
Some of the information I can separate by finding different words and creating some rules but there are some data where no rule can be established.

How would you solve this?

Thanks for all your help!

Domenic · May 14, 2015

Try replacing...

Code:

 Cells(iRow + finalrow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)

with

Code:

Cells(iRow + finalrow, iCol * 2 - 2 + 1).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(0))
Cells(iRow + finalrow, iCol * 2).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(1))

Hope this helps!

KeepOnWheels · May 14, 2015

Domenic said:

Try replacing...

Code:

 Cells(iRow + finalrow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)

with

Code:

Cells(iRow + finalrow, iCol * 2 - 2 + 1).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(0))
Cells(iRow + finalrow, iCol * 2).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(1))

Hope this helps!

Thanks for the help!
Unfortunately I'm getting "subscript out of range" error.

Domenic · May 14, 2015

That's probably because one or more cells in the table do not contain line breaks. I should have made allowances for it. I'll do so now. However, just to be clear, is this the result you expect?

[TABLE="width: 950"]
<tbody>[TR]
[TD="class: xl63, width: 74"]Name if client: Hello
[/TD]
[TD="class: xl63, width: 64"]987888 Street Canada[/TD]
[TD="class: xl63, width: 64"]Customer No. 8855
[/TD]
[TD="class: xl63, width: 64"]Type: None[/TD]
[/TR]
[TR]
[TD="class: xl63"]Name if client: The second[/TD]
[TD="class: xl63"]985888 Toronto[/TD]
[TD="class: xl63"]Customer No. 8889[/TD]
[TD="class: xl63"]Type: None[/TD]
[/TR]
[TR]
[TD="class: xl63"]Name if client: The third customer[/TD]
[TD="class: xl63"]98848788 Berlington[/TD]
[TD="class: xl63"]Customer No. 7785[/TD]
[TD="class: xl63"]Type: Something[/TD]
[/TR]
</tbody>[/TABLE]

If not, can you please clarify? If so, first add the following declaration...

Code:

Dim sa As Variant

Then try...

Code:

            [color=darkblue]For[/color] iRow = 2 [color=darkblue]To[/color] .Rows.Count
                [color=darkblue]For[/color] iCol = 1 [color=darkblue]To[/color] .Columns.Count
                    sa = Split(.cell(iRow, iCol).Range.Text, Chr(11))
                    Cells(iRow + finalrow, iCol * 2 - 2 + 1).Value = WorksheetFunction.Clean(Trim(sa(0)))
                    [color=darkblue]If[/color] [color=darkblue]UBound[/color](sa) > 0 [color=darkblue]Then[/color]
                        Cells(iRow + finalrow, iCol * 2).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(1))
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Next[/color] iCol
                Application.StatusBar = "Importing table no. " & gettable & " / " & TableNo & ", line: " & iRow & " / " & .Rows.Count
            [color=darkblue]Next[/color] iRow

KeepOnWheels · May 15, 2015

Domenic said:
That's probably because one or more cells in the table do not contain line breaks. I should have made allowances for it. I'll do so now. However, just to be clear, is this the result you expect?

[TABLE="width: 950"]
<tbody>[TR]
[TD="class: xl63, width: 74"]Name if client: Hello[/TD]
[TD="class: xl63, width: 64"]987888 Street Canada[/TD]
[TD="class: xl63, width: 64"]Customer No. 8855[/TD]
[TD="class: xl63, width: 64"]Type: None[/TD]
[/TR]
[TR]
[TD="class: xl63"]Name if client: The second[/TD]
[TD="class: xl63"]985888 Toronto[/TD]
[TD="class: xl63"]Customer No. 8889[/TD]
[TD="class: xl63"]Type: None[/TD]
[/TR]
[TR]
[TD="class: xl63"]Name if client: The third customer[/TD]
[TD="class: xl63"]98848788 Berlington[/TD]
[TD="class: xl63"]Customer No. 7785[/TD]
[TD="class: xl63"]Type: Something[/TD]
[/TR]
</tbody>[/TABLE]

If not, can you please clarify? If so, first add the following declaration...

Code:

Dim sa As Variant

Then try...

Code:

[COLOR=darkblue]For[/COLOR] iRow = 2 [COLOR=darkblue]To[/COLOR] .Rows.Count [COLOR=darkblue]For[/COLOR] iCol = 1 [COLOR=darkblue]To[/COLOR] .Columns.Count sa = Split(.cell(iRow, iCol).Range.Text, Chr(11)) Cells(iRow + finalrow, iCol * 2 - 2 + 1).Value = WorksheetFunction.Clean(Trim(sa(0))) [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](sa) > 0 [COLOR=darkblue]Then[/COLOR] Cells(iRow + finalrow, iCol * 2).Value = WorksheetFunction.Clean(Split(.cell(iRow, iCol).Range.Text, Chr(11))(1)) [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Next[/COLOR] iCol Application.StatusBar = "Importing table no. " & gettable & " / " & TableNo & ", line: " & iRow & " / " & .Rows.Count [COLOR=darkblue]Next[/COLOR] iRow

Yes exactly this is my goal.
Unfortunately the content is not splitted. I have a hunch that no Chr(11) is found in the string. How could I find out which character is used to split the lines?

KeepOnWheels · May 15, 2015

I have checked. Character 10 is used, but this information gets lost when imported, so even changing the text, Chr(11) to 10 do not help.

Domenic · May 15, 2015

Let's confirm which character is being used as a line break. First, make sure that the Word document containing your table is the active document. Then, make sure that the first cell in your first table contains text in the following format...

[TABLE="width: 250"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Name if client: Hello
987888 Street Canada[/TD]
[/TR]
</tbody>[/TABLE]

Then, place the following code in a regular module within Word and run it. What message is displayed, if any?

Code:

Sub test()

s = ActiveDocument.Tables(1).Cell(1, 1).Range.Text

For i = 1 To Len(s)
    If Mid(s, i, 1) Like "[0-9]" Then
        MsgBox Asc(Mid(s, i - 1, 1))
        Exit Sub
    End If
Next i

End Sub

KeepOnWheels · May 15, 2015

I think character(10) is used. Manually I copied the content to excel and checked the line break character with CODE() formula.

The word document is active (however I must admit that if I want to run the macro the excel will be activated), and the first cell contains already line break (basically every cell has line breaks). Unfortunately I get 'Object required' message.

If it is easier I can send to you the word file.

Domenic · May 15, 2015

KeepOnWheels said:
I think character(10) is used. Manually I copied the content to excel and checked the line break character with CODE() formula.

You can't check it that way. The line break character in Word -- Char(11) -- gets converted to Char(10) in Excel.

The word document is active (however I must admit that if I want to run the macro the excel will be activated)...

Place the code in a regular module within Word. So with the relevant document as your active document...

Code:

Alt+F11 > Insert Module > Copy/Paste code > Alt+Q

...and run the macro. What does the message box display?

KeepOnWheels · May 15, 2015

Oh yeah, I'm dumb, It did not come into my mind to paste it to word as module.
The character is 13.

Strange is, that if I comment out the Exit sub and let run trough the whole cell I get this result: 13 than 49, 32, 52, 51, 52

Import Word table into excel with line breaks

KeepOnWheels

New Member

Excel Facts

Domenic

MrExcel MVP

KeepOnWheels

New Member

Domenic

MrExcel MVP

KeepOnWheels

New Member

KeepOnWheels

New Member

Domenic

MrExcel MVP

KeepOnWheels

New Member

Domenic

MrExcel MVP

KeepOnWheels

New Member

Similar threads

Forum statistics

Share this page

Import Word table into excel with line breaks

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock