Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

can't you do a pastespecial with transpose ?

pastespecial transpose:=true

Not applicable
Author

I tried that but does not work

Not applicable
Author

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
Author

Hi,

The macro keeps failing at the following statement

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

Any ideas?

Not applicable
Author

What's the error message ?

rphpacheco
Creator III
Creator III

The example of yadurand worked perfectly.

You have enabled access to the system macros?

Not applicable
Author

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
Author

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
Author

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