Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Hi,
I have the chart in QV.
Plain report lay out in QV chart | |||||||||||||||||
|
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,
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.
Tamil, Thank you very much for your fast response. It works for me.