Import Word table into excel with line breaks

[KeepOnWheels]

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:

[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]

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

So did you try replacing Chr(11) with Chr(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

Those are just the codes for subsequent characters.

 

[KeepOnWheels]

Fantastic! It works! Thank you very much for all your help!

 

[mojamoja]

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?

Name if client: Hello	987888 Street Canada	Customer No. 8855	Type: None
Name if client: The second	985888 Toronto	Customer No. 8889	Type: None
Name if client: The third customer	98848788 Berlington	Customer No. 7785	Type: Something

<tbody>
</tbody>

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

Dim sa As Variant

Then try...

            [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

What if there's another row to split?