Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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("P2Smiley Tongue2").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("A2Smiley Tongue10").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

Tags (2)
Community Browser