Macro to export to excel, how to paste multiple rows of text into one cell?

[#1]

Hi all,

I can't seem to find a way to solve the following: I want to extract a certain text from a textbox using CopyTextToClipboard in a macro function, and then paste it to a predetermined cell in an excel file.

Instead of placing this text into the cell, it pastes all the lines of the text to seperate rows (overwriting all the text in the rows under the predetermined cell...).

Here is my macro code (so far), with the described part in bold text:

'***************************************************

sub export123toexcel

set oXL=CreateObject("Excel.Application")

oXL.visible=True

oXL.Workbooks.Add

Set oSH = oXL.ActiveSheet

'load table objects:

'load table object 1:

           Set obj = ActiveDocument.GetSheetObject("CH_table1export")

           obj.CopyTableToClipboard True

    'location in excel sheet:

           oSH.Range("A1:A5").Select 

           oSH.Paste

           set obj=Nothing  

'load table object 2:

           Set obj = ActiveDocument.GetSheetObject("CH_table2export")

           obj.CopyTableToClipboard True

    'location in excel sheet:

           oSH.Range("A6:A10").Select

           oSH.Paste

           set obj=Nothing  

'load table object 3:

           Set obj = ActiveDocument.GetSheetObject("CH_table3export")

           obj.CopyTableToClipboard True

    'location in excel sheet:

           oSH.Range("A11:A15").Select

           oSH.Paste

           set obj=Nothing  

'end load table objects

'delete superfluous title rows in excel export:

             oSH.Rows("5:5").Delete

             oSH.Rows("5:5").Delete

            oSH.Rows("8:8").Delete

            oSH.Rows("8:8").Delete

            oSH.Rows("11:11").Delete

'replace certain cells with text

              Set obj = ActiveDocument.GetSheetObject("TX26")

              obj.CopyTextToClipboard

              oSH.Range("P2:P2").Select

           oSH.Pastespecial

           oSH.Columns("P").ColumnWidth=40

           set obj=Nothing 

           oSH.Cells.EntireColumn.AutoFit

           'oSH.Cells.EntireRow.AutoFit

'selection in completed excel export:

            oSH.Range("A2:P10").Select

'cleanup:      

set oSH=Nothing

set oXL=Nothing

end sub

'*************************************************************

this results in:

As seen in the macro code, it concerns the text in column P, cell P2.

What I want to have is the following:

How can I get the macro to paste the text in this way?

Thanks in advance,

Marius