Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Macro to copy table to excel transposed

Hi,

I have a staight table in QV with the Horizontal property set to true, when I copy this table to excel it loses that property. Is there a way to copy the table to excel then transpose it? thanks

12 Replies
Not applicable

Re: Macro to copy table to excel transposed

can't you do a pastespecial with transpose ?

pastespecial transpose:=true

Not applicable

Re: Macro to copy table to excel transposed

I tried that but does not work

Not applicable

Re: Macro to copy table to excel transposed

indeed it dosnt work I don't know why

But you can do it that way :

Set XLApp = CreateObject("Excel.Application")
SET XLDoc = XLApp.Workbooks.Add
SET XLSheet = XLDoc.Worksheets(1)

XLApp.Visible = True


XLApp.Range("B5").Select




ActiveDocument.GetSheetObject("CH01" ).CopyTableToClipboard true

XLSheet.Paste

XLApp.Range("B5:C9").Select
XLApp.Selection.Copy

XLApp.Range("G7").Select
XLApp.Selection.PasteSpecial -4104,-4142,false,true

XLSheet.Range("B5:C9").Select
XLApp.Selection.Delete

Not applicable

Re: Macro to copy table to excel transposed

Hi,

The macro keeps failing at the following statement

XLApp.Selection.PasteSpecial -4104,-4142,false,true

Any ideas?

Not applicable

Re: Macro to copy table to excel transposed

What's the error message ?

rphpacheco
Contributor III

Re: Macro to copy table to excel transposed

The example of yadurand worked perfectly.

You have enabled access to the system macros?

Not applicable

Re: Macro to copy table to excel transposed

I was thinking, my test are with a small table.

Maybe your table is too big, and the two areas are mixed up.

try :

XLApp.Range("Z7").Select

XLApp.Selection.PasteSpecial -4104,-4142,false,true

Not applicable

Re: Macro to copy table to excel transposed

Hi,

Try substituting

XLApp.Range("B5:C9").Select

XLApp.Selection.Copy

XLApp.Range("G7").Select

XLApp.Selection.PasteSpecial -4104,-4142,false,true

simply with

XLApp.Range("B5:C9").Copy

XLApp.Range.Cells("G7").PasteSpecial -4104,-4142,false,true

Something similar worked for me

C

Not applicable

Re: Macro to copy table to excel transposed

What is the command -4104,-4142,false,true?

Community Browser