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:

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>

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]

Try replacing...

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

with

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]

Try replacing...

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

with

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]

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

 

[KeepOnWheels]

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

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]

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]

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...

Name if client: Hello 987888 Street Canada

<tbody>
</tbody>

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

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]

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]

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...

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

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

 

[KeepOnWheels]

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