How to transposed paste and past as data like Excel from GetSheetObject in Qlikview Macro

[#1]

Hi,

Can anyone help on two problems I have now?

1. Transpose taste like Excel - In Qlikview tool export with Macro. I use GetSheetObject get the chart showing in horizontal, but when exporting it will paste as vertical not as it shows in the chart.

2. Same way to get the chart, how to paste as data only in Qlikview. Because Qlikview app generates Excel in a different format, which is not Excel and not csv.

Thanks a lot.

[tamilarasu]

Dave,

I am not clearly understand the second issue. Please attach your file and explain the issue along with the expected output. It will be easy for us to provide the solution.

[#3]

Hi,

I have the chart in QV.

Plain report lay out in QV chart
ID    1 2 3 4 5 Name a b c d e

And expected lay out will be like.

expected chart
ID	Name
1	a
2	b
3	c
4	d
5	e

I did use Horizontal selection in the straight table to transpose appearance. But when I use the export process like following it will go back to original un-transpose layout.

Doc.GetSheetObject("YTD").CopyTableToClipBoard TRUE

xlApp.ActiveSheet.Paste

That is my question. Or make it simple it is how to apply the Excel transpose function in Qlikview. There are some question in Qlikview community but none of them were answered. Maybe Qlikview cannot fix the problem in a easy way.

Thanks,

[tamilarasu · Accepted solution]

Hi,

      You can do this in two ways. I have chosen the second method and attached the sample file.

1. Create a transposed table somewhere in the sheet and hide it using the conditional show property. Later, you can enable the chart and export to excel and hide it again (Using Macros).

2. Export the chart to excel and do the transpose in excel itself by using Qlikview macros.

Sub Export
Dim XLApp,XLDoc

FileName = "Test.xlsx"
FilePath = ActiveDocument.GetVariable("vPath").GetContent.String

If Right(FilePath,1)<> "\" then
FilePath = FilePath & "\"
End If

File = FilePath & FileName

Set XLApp = CreateObject("Excel.Application") 
XLApp.Visible = False
Set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard True
XLDoc.Sheets(2).Activate
XLDoc.Sheets(2).Paste
XLApp.Selection.copy

LastRow = XLDoc.Sheets(2).UsedRange.Rows.Count

XLApp.Range("A" & LastRow +1).Select
XLApp.Selection.PasteSpecial -4104,-4142,false,true 

XLApp.Selection.copy
XLDoc.Sheets(1).Paste 

XLDoc.Sheets(1).Name = "Chart1" 
XLDoc.Sheets(1).Activate
XLApp.Range("A1").Select

XLApp.DisplayAlerts = False
XLDoc.Sheets(3).Delete
XLDoc.Sheets(2).Delete
XLDoc.SaveAs File
XLApp.DisplayAlerts = True

XLApp.Application.quit  

set XLApp = Nothing 
set XLDoc = Nothing   
Msgbox "Exported Sucessfully" 
End Sub

Final Result:

Dave W wrote:

..... There are some question in Qlikview community but none of them were answered. .....

Hope this will be a answered thread . Let me know.

[#5]

Tamil, Thank you very much for your fast response. It works for me.