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