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
Best Answer
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.
I'm posting this by memory, but that's the root of that problem.