Macro to copy table to excel transposed

[#1]

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

[#2]

can't you do a pastespecial with transpose ?

pastespecial transpose:=true

[#3]

I tried that but does not work

[#4]

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

[#5]

Hi,

The macro keeps failing at the following statement

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

Any ideas?

[#6]

What's the error message ?

[rphpacheco]

The example of yadurand worked perfectly.

You have enabled access to the system macros?

[#8]

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

[#9]

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

[#10]

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