CODEWITHSUNDEEP
vbaexcelms-word
Markus
Markus

Reputation: 31

VBA Copy multiline text from Word paste into Excel cell

My goal is to copy a multiline formatted text from Word to an Excel worksheet into one single cell using a VBA macro.

Now I've got a multiline text which needs two cells.

This is my current code:

With oWB.Worksheets("EPICS")           
    ' Insert DESCRIPTION - todo
    '
    ' HEADING xyz is selected, move one down and go to Pos1
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine

    ' Save current line number (BEGIN)
    BeginText = Selection.Range.Information(wdFirstCharacterLineNumber)

    ' Go to the first table and one move up
    Selection.GoTo What:=wdGoToTable, Which:=wdGoToNext, Count:=1, Name:=""
    Selection.MoveUp Unit:=wdLine, Count:=1

    ' Save current line number (END)
    EndText = Selection.Range.Information(wdFirstCharacterLineNumber)
    RangeToSelect = EndText - BeginText

    Selection.MoveUp Unit:=wdLine, Count:=RangeToSelect, Extend:=wdExtend
    Selection.Copy

    .Cells(1, 1).PasteSpecial xlPasteValues
End With

This creates the following: enter image description here

I would like to have the following: enter image description here

Any ideas how I can handle this or any input?

Upvotes: 0

Views: 2868

Answers (1)

Siyon DP
Siyon DP

Reputation: 514

Instead of
...
Selection.Copy
.Cells(1, 1).PasteSpecial xlPasteValues
...
Code
.Cells(1, 1).Value=Selection.text

Upvotes: 1

Related Questions