topic Re: Macro to copy table to excel transposed in QlikView

Macro to copy table to excel transposed

Wed, 05 Jun 2013 08:23:32 GMT

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

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 08:49:19 GMT

can't you do a pastespecial with transpose ?

pastespecial transpose:=true

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 09:33:01 GMT

I tried that but does not work

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 09:46:33 GMT

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

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 11:03:01 GMT

Hi,

The macro keeps failing at the following statement

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

Any ideas?

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 11:42:43 GMT

What's the error message ?

Re: Macro to copy table to excel transposed

rphpacheco — Wed, 05 Jun 2013 12:12:28 GMT

The example of yadurand worked perfectly.

You have enabled access to the system macros?

Re: Macro to copy table to excel transposed

Wed, 05 Jun 2013 12:14:25 GMT

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

Re: Macro to copy table to excel transposed

Tue, 18 Mar 2014 13:32:38 GMT

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

Re: Macro to copy table to excel transposed

Fri, 11 Jul 2014 12:37:19 GMT

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

Re: Macro to copy table to excel transposed

Fri, 11 Jul 2014 13:02:20 GMT

-4104 : paste all

-4142 : replace values in destination (no calcul)

false : paste blank values also

true : transpose

the first 3 are the default values, but you have to write it to be able to write the fourth one

Re: Macro to copy table to excel transposed

Fri, 11 Jul 2014 13:19:12 GMT

Where do I get these codes? I always use the functions of excel in qlick view.

Like now I'm looking for the code to paste only values

Re: Macro to copy table to excel transposed

Fri, 11 Jul 2014 14:36:19 GMT

that's microsoft macro language

look there for pastespecial function Range.PasteSpecial Method (Excel)