0 Replies Latest reply: Jan 13, 2016 11:04 AM by Marius Peters RSS

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

      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:

       

      table1wrong.jpg

       

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

       

      What I want to have is the following:

       

      table1right.jpg

       

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

       

       

      Thanks in advance,

       

      Marius