Creating tables for copy from Word to Excel

Question

So, I needed to take some data done in MS Word tables, and manipulate in excel. I decided to get it from word to excel via a VBA subroutine to "save time". My source word document contained like twentysomething tables.

I took my source document's tables, extracted my data and made a new document, with a new table, only needing me to copy and paste it into excel.

However, while the final table before copy looks good in word. When i copy it to excel, it breaks up the cells that contain whole paragraphs into separate cells.

As most excel peeps would know, even though a solution looks like in excel, doing a merge and center - that only preserves the content in the uppermost cell in the selection!

So, any advice, on either a better merge and center, or a better "time saver" alltogether, would be great.

Here's a sample of the code so far:

Sub First()

Dim tableLength, tableIndex
tableLength = ThisDocument.Tables.Count

Dim tblReport As Table
Dim docReport As Document

Set docReport = Documents.Add
Set tblReport = docReport.Tables.Add(Selection.Range, 1, 2)

With tblReport

Dim fieldOne, subvalueAription, subvalueA, subvalueB, subvalueC

For tableIndex = 1 To tableLength

    fieldOne = ThisDocument.Tables(tableIndex).Rows(2).Cells(2).Range.Text
    subvalueA = Trim(ThisDocument.Tables(tableIndex).Rows(4).Cells(2).Range.Text)
    subvalueB = "A: " & Trim(ThisDocument.Tables(tableIndex).Rows(5).Cells(2).Range.Text)
    subvalueC = "B: " & Trim(ThisDocument.Tables(tableIndex).Rows(6).Cells(2).Range.Text)
    subvalueAription = subvalueA & subvalueB & subvalueC & "C: "

    Dim rowNext As row
    Set rowNext = .Rows.Add
    rowNext.Cells(1).Range.Text = fieldOne
    rowNext.Cells(2).Range.Text = subvalueA & subvalueB & subvalueC


Next

End With


End Sub

Answer 1

Excel uses a different line terminator than Word. In order to avoid the problem with the text from the Word table being split over several Excel cells, you need to handle the line terminator conversion yourself.

'Word > Excel
 newText = Replace(wordText, vbCrLf, vbLf)

I'm posting this by memory, but that's the root of that problem.