Topic
No replies
emily.butt
emily.butt
6 Posts
ACCEPTED ANSWER

Pinned topic Create Excel Pivot Table with DXL

‏2013-11-21T15:08:27Z |

Hello,

I've been experimenting with using DXL to do more with excel than just exporting, and right now I'm working on making a pivot table with data exported to excel from DOORS. Using a combination of recorded macros and this http://msdn.microsoft.com/en-us/library/hh243933(v=office.14).aspx for inspiration I've come up with this:

 

OleAutoObj objTable = null
OleAutoObj objField = null

OleAutoArgs objArgs = create

const int xlRowField = 1
const int xlColumnField = 2
const int xlPageField = 3
const int xlDataField = 4
const int xlCompactRow = 0

void createPivot () {

// create the pivot table
clear objArgs
oleMethod ( objExcelDataExportSheet, "PivotTableWizard", objArgs, objTable )

// add "Column1" column to pivot table
put ( objArgs, "Column1" )
oleMethod ( objTable, "PivotFields", objArgs, objField )
olePut ( objField, "Orientation", xlRowField ) // move field to "Row Labels"
olePut ( objField, "Position", 1 ) // first in "Row Labels" list
clear objArgs

// add "Column2" column to pivot table
put ( objArgs, "Column2" )
oleMethod ( objTable, "PivotFields", objArgs, objField )
olePut ( objField, "Orientation", xlRowField ) // move field to "Row Labels"
olePut ( objField, "Position", 2 ) // second in "Row Labels" list
clear objArgs

// add "Column3" column to pivot table
put ( objArgs, "Column3" )
oleMethod ( objTable, "PivotFields", objArgs, objField )
olePut ( objField, "Orientation", xlRowField ) // move field to "Row Labels"
olePut ( objField, "Position", 3 ) // third in "Row Labels" list
clear objArgs

// add "Column4" column to pivot table
put ( objArgs, "Column4" )
oleMethod ( objTable, "PivotFields", objArgs, objField )
olePut ( objField, "Orientation", xlDataField ) // move field to "Values"
olePut ( objField, "Position", 1 ) // first in "Values" list
clear objArgs

// get rid of grand total
olePut ( objTable, "ColumnGrand", "False" )
// "Show in Compact Form"
olePut ( objTable, "RowAxisLayout", xlCompactRow )
// PivotTable Options... | Display | "Display item labels when no fields are in the values area"
olePut ( objTable, "DisplayImmediateItems", "True" )

}

 

For the most part it works nicely, except for one small thing.  The "RowAxisLayout" part of my dxl just doesn't do anything.  It's not really critical that the pivot table be shown "in compact form", but it would be nice to get it working.

 

Does anyone have an idea what my mistake here is?  There doesn't seem to be an alternative excel vba function to change this property.

 

And I suppose I have a larger question, too.  Is there any limitation on what excel vba functionality can be executed with DXL?

 

 

 

Thanks!!

 

-------

Edited to fix the font to a readable size

 

Updated on 2013-11-21T15:12:55Z at 2013-11-21T15:12:55Z by emily.butt