Creating an Excel Pivot Table using IDEAScript

10 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
Creating an Excel Pivot Table using IDEAScript

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

 

gennypjoya
Offline
Joined: 01/28/2015 - 15:55

Great!! the Best!!!

FIDEA
Offline
Joined: 08/08/2016 - 02:35

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.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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:

  • xlAverage = -4106
  • xlCount = -4112
  • xlCountNums = -4113
  • xlDistinctCount = 11
  • xlMax = -4136
  • xlMin = -4139
  • xlProduct = -4149
  • xlStDev = -4155
  • xlStDevP = -4156
  • xlSum = -4157
  • xlUnknown = 1000
  • xlVar = -4164
  • xlVarP = -4165

Brian

 

mattiusb
Offline
Joined: 10/25/2017 - 19:38

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

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.


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\user\Documents\My IDEA Documents\IDEA Projects\Samples\Source Files.ILB\General Ledger.xlsx") '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("DATE")
				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 objSheet = oBook.Worksheets.Item("Sheet2")
			
				objSheet.PivotTables("PivotTable1").RowGrand = False
				objSheet.PivotTables("PivotTable1").ColumnGrand = False
				objSheet.PivotTables("PivotTable1").PivotFields("ACCOUNT_DESC").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
													
			Set objSheet = Nothing
			
	Set oBook = Nothing
	
	

End Sub
idemnos's picture
idemnos
Offline
Joined: 06/17/2016 - 13:25

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?

Lois's picture
Lois
Offline
Joined: 02/14/2013 - 09:00

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

Baricha Saddam Ali
Offline
Joined: 04/13/2017 - 03:47

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)