I had a member ask for some help in creating an Excel pivot table using IDEAScript. I did some research and found information on the microsoft web site that helped out. The example code uses the General Ledger.xls that is supplied in the samples project folder for IDEA. This creates a basic pivot table in Excel with the Account Description being the row, the Source field being the column and the Amount field being the data. You can also see that for the data field we are formatting it with a $ and no decimals and showing the sum. You can easily add on to this to create more sophisticated pivot tables in Excel.
'pivot table based on instructions from MS site 'https://msdn.microsoft.com/en-us/library/office/hh243933(v=office.14).aspx#CreatingPivotTable_CreatingPivotTableReport Sub Main Dim excel As Object Dim oBook As Object Dim oSheet As Object Dim objTable As Object Dim objField As Object Set excel = CreateObject("Excel.Application") '1 excel.Visible = True '1 Set oBook = excel.Workbooks.Open("C:\Users\belement\Documents\My IDEA Documents\IDEA Projects\Samples\Source Files.ILB\General Ledger.xls") '1 Set oSheet = oBook.Worksheets.Item(1) Set objTable = oSheet.PivotTableWizard Set objField = objTable.PivotFields("ACCOUNT_DESC") objField.Orientation = 1' xlRowField Set objField = objTable.PivotFields("SOURCE") objField.Orientation = 2'xlColumnField ' Specify a data field with its summary ' function and format. Set objField = objTable.PivotFields("AMOUNT") objField.Orientation = 4'xlDataField objField.Function = -4157 'xlSum objField.NumberFormat = "$ #,##0" Set objField = Nothing Set objTable = Nothing Set oSheet = Nothing Set oBook = Nothing End Sub