
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pivot table export to excel
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
- Tags:
- new_to_qlikview
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
After some tweaks you can use code from this post:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
