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
Great!! the Best!!!
Hello,
could you please help me how to change Function in Data Field into "average" instead of "sum". objField.Function = -4157 'xlAvg do not work. I suppose it must be the number, but I don't know where to find it.
Hi Joanna,
To find this information you need to go into Excel -Developer and select visual basic. This will bring up the Visual Basic for Applications interface where you can do search under View - Object Browser.
Here are the codes for you:
Brian
Hi, I have been trying to create a pivot table in EXCEL without subtotals and it is driving me crazy. What works on the sample database comes up with a "Unable to set the subtotals property of the pivotfield class"
All I added was these lines
Set objField = objTable.PivotFields("EMP_ID")
objField.Subtotals = Array (False, False,False,False,False,False,False,False,False,False,False,False)
I just don't understand why it would work on one project and not the other. Any ideas?
Thanks,
Matt
Hi mattiusb,
I am not sure if I follow what you are trying to do. Can you post an image or part of the excel spreadsheet showing the spreadsheet and what you are trying to do.
Thanks
Brian
Here is an expansion of the GL code removing the totals and sub-totals. Unfortunately this doesn't seem to work on all the files and I am not sure why.
Brian:
Thank you so much for this kind of solutions! They are plenty helpful.
I've got two questions: This example opens an excel file and create the pivot table using its data, isn't it? I export an *.IMD file to excel and I'm interested in create a pivot table in a different sheet in the same exported file. How could I do it?
I ran it and the output was simply beautiful !!!! With this, final reports can be made available to users even in excel format. Thanks Brian.
Lois
Hi Brian,
The above code does not work for me. Is it posibble to use pivotcache instead of using PivotWizard.
e.g insead of
Set objTable = oSheet.PivotTableWizard
how can I use
Set objTable =excel.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "Subsidiaries_Status_0902!R1C1:R13582C20", Version:=xlPivotTableVersion15).CreatePivotTable (TableDestination:="sSheetName!R3C1", TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15)