Pivot Table (excel)

picea892 · March 30, 2010

Hi all

Wondering if someone could show me an example of creating an excel pivot table using autoit? Just something very simple

There are very few examples on this forum as far as I can tell and I can't get any of them working.

Link 1

Link 2

Link 3

Thanks in advance

Juvigy · March 30, 2010

This works for me:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\somefile.xls")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"C3:G8",1) ;the pivot data is located here :"C3:G8"
$oExcel.Sheets.Add
$test2 = $oExcel.Range("G3")
$oExcel.ActiveSheet.PivotTables.Add($test,$test2,"some name")

picea892 · March 30, 2010

Thank you Juvigy for the response.

I'm curious to know if others can get your script to work. I continue to get an object error whenever I try to create pivotcaches. Your example is very good and I'm sure it works, it's just too simple and easy to follow to have an error.

I guess I should explore other possibilities for my error. Maybe my security is set too high in excel, I don't know.

Thanks again

Picea892

Juvigy · March 30, 2010

Which version of excel do you have?

Macro security should be set to low.

Programmatic access for the object model should be allowed.

picea892 · March 30, 2010

Excel 2003

Security set to medium, it's my work computer and there are lots of hoops to get it set lower.

I'm not sure it matters what the contents of the excel spreadsheet is. But just for the record, I filled in C3:G8 with random numbers.

I'll keep playing. I probably will try to use VBA to create a pivot table and see if I am successful via that route, perhaps it will provide a clue for my autoit.

Juvigy · March 30, 2010

I dont have Excel 2003 to test but i think that the macro security needs to be low for this to work.

Can you set it to low just temporary for the test?

picea892 · March 30, 2010

Hi there

Thanks again for giving me an example I knew should work. When I have more time I will need to figure out why I couldn't get it working in my case. In the interim I ended up doing the sort and display the hard way using various arrays. I am very impressed with the work you can do in excel using autoit. It worked out well and probably is a better result anyways.

Picea892

kalvin · June 13, 2012

This works for me:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:somefile.xls")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"C3:G8",1) ;the pivot data is located here :"C3:G8"
$oExcel.Sheets.Add
$test2 = $oExcel.Range("G3")
$oExcel.ActiveSheet.PivotTables.Add($test,$test2,"some name")

It does work for me, I'm stuck at the next step.

How do I go about adding the fields I need to the row and colums, and then selecting a data field.

I've been searching the web and find lots of examples. Most are based on visual basic and I'm not having a lot of luck converting them to work. I also tried recording a macro and looking at the code to see if it would give me any clues.

cheers, Kalvin

Juvigy · June 13, 2012

This is how you add row or columns:

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open($Filenname)

$oExcel.Application.ActiveSheet.PivotTables("PivotTable5").PivotFields("Survey Notes").Orientation = 3 ; 3 for "Report filter" 1 for "raw labels"

$oExcel.Application.ActiveSheet.PivotTables("PivotTable5").PivotFields("Survey Notes").Position = 3

And this is how you select a value:

$oExcel.Application.ActiveSheet.PivotTables("PivotTable5").PivotFields("Survey Notes").CurrentPage = "somevalue"

kalvin · June 13, 2012

Juvigy,

Thank you for your fast reply.

I'm still playing with this and didn't have much luck.

I keep getting an error.....

$oExcel.Application.ActiveSheet.PivotTables("some name").PivotFields("Category").Orientation = 3

$oExcel.Application.ActiveSheet.PivotTables("some name").PivotFields("Category")^ ERROR

>Exit code: 1 Time: 1.213

The excel opens up, the newsheet opens up with a pivot table template with nothing in the row,column, or data fields.

When I close excel I see the above error.

"Category" is the name of one of my columns in my execel spreadsheet. I'm hoping to use it in the Column axis and the data axis.

Thank you for the email also. I tried to locate the Excel Pivot Chart UDF with no luck. I'm taking a guess that you might have been refering to the Excel chart UDF which I did look at with no references to the pivot table.

best regards, Kalvin

kalvin · June 13, 2012

I should have added this.....

==> the requested action with this object has failed.:

Juvigy · June 14, 2012

It works on my Vista Excel2007 setup.

Did you try only my code or did you insert it on larger script?

The active sheet on the file should be the one with the pivot.

For example if the file is saved with sheet1 as active and the pivot is located on sheet2 it wont work.

(you will have to activate sheet2)

kalvin · June 16, 2012

Hi, sorry for the slow reply.

It is finally the weekend and I have some time to play.

To answer your question. I tried just the code snippets first.....changing some of the names to fix my test data.

I managed to get it to work. I replaced the field name in my test spreadsheet(and code) from "Category" to "action" and then it worked. I wasn't sure why so as a test I changed it back to "Category" and now it is working. I don't believe I had any spelling errors in it and I know I had checked that before, but can not rule out 100% that there may have been a typo in there causing a problem.

I'm using Windows 7 and Excel 2010. As part of the trouble shooting I tried the test script on another maching which has Windows XP and Excel 2003. It didn't even make it to the original error I was having as it failed at .....

>"C:Program FilesAutoIt3SciTE..autoit3.exe" /ErrorStdOut "C:scripttesting.au3"

C:scripttesting.au3 (6) : ==> The requested action with this object has failed.:

$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:I20", 1)

$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:I20", 1)^ ERROR

>Exit code: 1 Time: 0.811

Haven't figured this one out yet.

On another note, I've been reading ....lots.......

Some of the examples I find on making pivot tables are based on the OWC. I'm assuming here (remember I'm just learning here) that your code is not based on OWC but another method. What is the correct terminology for the method that your example is based on so I could reseach/read some more.

Thankyou again for the help, Kalvin

Off to play some more.......great way to spend a rainny weekend.

Juvigy · June 18, 2012

Look around here:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivottable%28v=office.11%29.aspx

kalvin · June 19, 2012

Ok, another hoop cleared.

For anyone that finds this and wonders what the answer to my above problem.....

>"C:Program FilesAutoIt3SciTE..autoit3.exe" /ErrorStdOut "C:scripttesting.au3"

C:scripttesting.au3 (6) : ==> The requested action with this object has failed.:

$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:I20", 1)

$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:I20", 1)^ ERROR

>Exit code: 1 Time: 0.811

The Excel 2002 (typo above - not 2003) does not recognize the PivotCaches.Create. (2010 does ) The PivotCaches.Add is recognized in both. Note it only takes two parameters and not the three.

ref.... http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivotcaches.add%28v=office.11%29.aspx

Ok.....moving along......more to do , but I've got most of my big problems fixed.

Thankyou again Juvigy.

artislp · November 16, 2013

hi, im newbie here... my very first post...

was trying autoit for a couple of days & find it very useful.

I am trying to automate a script for my wife to generate pivot tables... I was actually successful in doing so.

however, i am not able to dynamically set the pivot data range.

so if i set a fixed range, & the range is outside the valid data, the pivot makes a COUNT instead of SUM.

Need your help how to:

1. Dynamically find the valid range & set it to the pivot caches

2. set the value field setting to SUM instead of COUNT even if the data range is outside the valid table (empty cells).

Below is my existing code that is currently working. It creates 2 pivot tables in the added sheet "YourPivot".

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****

#AutoIt3Wrapper_Outfile_type=a3x

#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#include <Excel.au3>

$myfile = FileOpenDialog("CAVIUM FG-vs-BACKLOG Pivot Application", @WindowsDir & "", "Excel (*.xls;*.xlsx)", 1 + 4)

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open ($myfile)

$test1 = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"'FRESH FGI INVENTORY REPORT'!D2:K10000",1) ;for now, this is fixed range

$test3 = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"'BACKLOG'!F1:K10000",1) ;for now, this is fixed range

_ExcelSheetAddNew($oExcel, "YourPivot")

_ExcelSheetMove($oExcel, "YourPivot", "BACKLOG", False)

$test2 = $oExcel.Range("B2")

$oExcel.ActiveSheet.PivotTables.Add($test1,$test2,"FG")

$test4 = $oExcel.Range("G2")

$oExcel.ActiveSheet.PivotTables.Add($test3,$test4,"BL")

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ITEM").Orientation = 1

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("CRD").Orientation = 1

$oExcel.Application.ActiveSheet.PivotTables("FG").PivotFields("Part Number").Orientation = 1

$oExcel.Application.ActiveSheet.PivotTables("FG").PivotFields("Balance Qty").Orientation = 4

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("REQ QTY").Orientation = 4

$oExcel.Application.ActiveSheet.PivotTables("FG").PivotFields("Part Number").Position = 1

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ITEM").Position = 1

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("CRD").Position = 2

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("REQ QTY").Position = 3

$oExcel.Application.ActiveSheet.PivotTables("FG").PivotFields("Balance Qty").Position = 2

Looking forward to your expert advise... Thanks in advance...

Edited November 16, 2013 by artislp

artislp · November 16, 2013

Hi,

Quick update to my earlier post. I managed to get it working with the data range:

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****

#AutoIt3Wrapper_Outfile=I:AutoItPivot.exe

#AutoIt3Wrapper_UseX64=y

#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#include <excel.au3>

$myfile = FileOpenDialog("CAVIUM FG-vs-BACKLOG Pivot Application: Choose Excel File to Analyze", @WindowsDir & "", "Excel (*.xls;*.xlsx)", 1 + 4)

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open ($myfile)

$table1 = $oExcel.Application.ActiveSheet.UsedRange.Rows.Count

$range1 = $table1-1

;MsgBox(1, "Output", $range1)

_ExcelSheetActivate($oExcel, 2)

$table2 = $oExcel.Application.ActiveSheet.UsedRange.Rows.Count

$range2 = $table2-1

;MsgBox(1, "Output", $range2)

$test1 = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"'FRESH FGI INVENTORY REPORT'!D2:K"& $range1,1)

$test3 = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"'BACKLOG'!F1:K"& $range2,1)