Create tables to copy from Word to Excel
So, I needed to make some data in MS Word tables and manage it in excel. I decided to get it from the word to get on through a VBA routine to "save time". My original document containing twentysomething tables.
I took my original document tables, extracted my data and made a new document with a new table, I only needed to copy and paste it into excel.
However, while the summary table in front of the copy looks good word. When I copy it to excel, it breaks cells containing whole paragraphs into separate cells.
As most pros would know, even if the solution looks like it is in excel, doing merge and center - it only keeps the content in the topmost cell in the selection!
So any advice, be it the best fusion and center, or the best "time saver", will be fine.
Here's some sample code:
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
a source to share
Excel uses a different line delimiter than Word. To avoid the problem with text from a Word spreadsheet split into multiple Excel cells, you need to handle the line terminator conversion yourself.
'Word > Excel
newText = Replace(wordText, vbCrLf, vbLf)
I'm posting this from memory, but this is the root of this problem.
a source to share