topic Re: Pivot table export to excel in QlikView

Pivot table export to excel

priyarane — Mon, 16 Mar 2015 15:04:52 GMT

Hi Experts,

How to export Pivot table as it looks like.

I have added Pivot and Excel reports as attachment.

If you look at dimensions ex - Main Group: Commercial Banking Repeted but in my Pivot table its not repeting. So my users wanted as Pivot table looks like

Re: Pivot table export to excel

robert_mika — Mon, 16 Mar 2015 15:09:24 GMT

After some tweaks you can use code from this post:

Same Formating of qlikview pivot table in Excel

Re: Pivot table export to excel

priyarane — Mon, 16 Mar 2015 15:55:07 GMT

Hi Robert Its perfect.

I have one more thing that Can we add data in excel.

Ex, I have attached.

My piont is, Can we add some text after downloading data from pivot table. It should be appear in Excel.

Re: Pivot table export to excel

robert_mika — Mon, 16 Mar 2015 16:07:18 GMT

Yes.

You need to play with VBA

Try this:

sub Export

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

XLSheet.Range("G8").Value ="Total Exposure Amount"

end sub

This will create string in Your Excel file

Re: Pivot table export to excel

priyarane — Tue, 17 Mar 2015 09:13:50 GMT

Hi Robert,

So Can we execute both the Macros at a time and Can we get Output as attached.

Macro 1 -

sub Export
set XLApp = CreateObject("Excel.Application") ' Define Object
XLApp.Visible = True 'Visible set as true
set XLDoc = XLApp.Workbooks.Add 'Open new workbook
set table = ActiveDocument.GetSheetObject("CH03")
rem set table = ActiveDocument.GetSheetObject("MB01")

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
table.CopyTableToClipboard true 'Copy data to Clipboard
XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell

end sub

Macro 2 -
sub Export1

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

XLSheet.Range("A1").Value ="Total Exposure Amount Total Test "

end sub

Re: Pivot table export to excel

robert_mika — Tue, 17 Mar 2015 09:43:37 GMT

Your question was about output to Excel not abut format.

If about output-Yes we can.

Using this Macro

sub Export

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

set table = ActiveDocument.GetSheetObject("CH03")

rem set table = ActiveDocument.GetSheetObject("MB01")

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell

XLSheet.Range("G8").Value ="Total Exposure Amount Total Test "

end sub

and data from the link I have provided we end up with

But the Macro must be modify to pick your objects.