VBA Copy multiline text from Word paste into Excel cell

0
votes

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?

1
vbaexcelms-word
The final line of code will be ActiveSheet.Cells(1, 1).Value = Txt1 & " " & Txt2. But everything you have before that point is very far from what you need. Start by deciding whether you want to run the code from the Excel worksheet or from the Word document. The google for something like, "How to open a Word document in Excel" (or the other way around. You will find basic code on which you can build. – Variatus
No, no, no! I have a word file. I have a word macro. This macro copies the text between a heading and the first table after this heading. My problem is, that the text between these two is multine. My goal: To get this text into one excel cell. My current status. Each line in word, is in one cell in excel.... – Markus
@Variatus if you want I'll send you the code via message, it's pretty long... – Markus

1 Answers

1
votes

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