14 replies to this topic

[picea892]

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]

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]

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]

Which version of excel do you have?
Macro security should be set to low.
Programmatic access for the object model should be allowed.

[picea892]

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]

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]

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]

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]

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]

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]

I should have added this.....

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

[Juvigy]

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]

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]

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

[kalvin]

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.