topic Re: How to transposed paste and past as data like Excel from GetSheetObject in Qlikview Macro in QlikView

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

Fri, 06 May 2016 13:08:36 GMT

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.

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

tamilarasu — Sat, 07 May 2016 05:34:17 GMT

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.

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

Tue, 17 May 2016 13:45:50 GMT

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,

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

tamilarasu — Tue, 17 May 2016 18:22:01 GMT

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.

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

Tue, 17 May 2016 21:09:20 GMT

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