Pivot table export to excel

[priyarane]

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

[robert_mika]

After some tweaks you can use code from this post:

Same Formating of qlikview pivot table in Excel

[priyarane]

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.

[robert_mika]

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

[priyarane]

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

[robert_mika · Accepted solution]

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.