(SOLVED)Excel Pivot table help macro to AutoIt

[xcaliber13]

Hi All,

        I have read and reread all the forum topics on Pivot tables and still cannot get my AutoIt script to work.  So I have it working correctly up to this:

$xlPageField = 3
$xlRowField = 1
$xlCount = 0
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("X:\Rachael B\Tickler Reports\Temp\temp.xlsx")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:K64000",1)

$oExcel.Sheets.Add
$test2 = $oExcel.Range("A1")
$oExcel.ActiveSheet.PivotTables.Add($test, $test2, "BL")
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("Client").Orientation = 1
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("DenialCode").Orientation = 1
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("DenialMsg").Orientation = 1
With $oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ActionDesc")
    .Orientation = $xlPageField
    .Position = 1
EndWith
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ActionDesc").CurrentPage = "(blank)"

From here I recorded this macro

ActiveSheet.PivotTables("BL").AddDataField ActiveSheet.PivotTables("BL"). _
        PivotFields("Tickler"), "Count of Tickler", xlCount
    ActiveSheet.PivotTables("BL").AddDataField ActiveSheet.PivotTables("BL"). _
        PivotFields("Balance"), "Count of Balance", xlCount
    With ActiveSheet.PivotTables("BL").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("BL").PivotFields("Count of Balance")
        .Caption = "Sum of Balance"
        .Function = xlSum
    End With

This gets me to where I need to be in the Excel spreadsheet.   But I am not understanding on the correct syntax for the recorded macro to AutoIt.  I have tried most of the examples I could find in the forums but still unable to get this to work.   Can anyone show me what it should be?

Thank you

[water]

Please define

  On 6/18/2018 at 6:34 PM, xcaliber13 said:

cannot get my AutoIt script to work

Expand  

Do you get an error message in SciTE, does your script return unexpected results ...?

[xcaliber13]

Water,

        Thank you for the reply.  Here is one way I tried:

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("Tickler").Orientation = 3

$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("Tickler").CurrentPage = $xlCount

Within Excel  I am trying to get this field to be a value in a column.    No errors.  Just does not populate the spreadsheet.

[xcaliber13]

Water sorry it does populate but it is adding a filter and not a value

[water]

I would translate the recorded VBA part to:

$oExcel.ActiveSheet.PivotTables("BL").AddDataField($oExcel.ActiveSheet.PivotTables("BL").PivotFields("Tickler"), "Count of Tickler", $xlCount)
$oExcel.ActiveSheet.PivotTables("BL").AddDataField($oExcel.ActiveSheet.PivotTables("BL").PivotFields("Balance"), "Count of Balance", $xlCount)
With $oExcel.ActiveSheet.PivotTables("BL").DataPivotField
    .Orientation = $xlColumnField
    .Position = 1
EndWith
With $oExcel.ActiveSheet.PivotTables("BL").PivotFields("Count of Balance")
    .Caption = "Sum of Balance"
    .Function = $xlSum
EndWith

If you get error messages caused by missing constants then we need to add them.

[xcaliber13]

I am getting this error:

(65) : ==> The requested action with this object has failed.:
$oExcel.ActiveSheet.PivotTables("BL").AddDataField($oExcel.ActiveSheet.PivotTables("BL").PivotFields("Tickler"), "Count of Tickler", $xlCount)
$oExcel.ActiveSheet.PivotTables("BL")^ ERROR

I do believe it is the constant $xlCount.  What should that be set to?

I have it   Set like this

$xlCount = 0

[xcaliber13]

Water,

           Found what I needed.  Thank you for your help.  Got it working

[water]

 

[xcaliber13]

Here is the finished code for Pivot Tables.  Does exactly what I needed it to do.  Thank you Water

$xlPageField = 3
$xlRowField = 1
$xlColumnField = 4
$xlCount = -4112
$xlSum = -4157
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\temp.xlsx")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:K64000",1)

$oExcel.Sheets.Add
$test2 = $oExcel.Range("A1")
$oExcel.ActiveSheet.PivotTables.Add($test, $test2, "BL")
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("Client").Orientation = 1
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("DenialCode").Orientation = 1
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("DenialMsg").Orientation = 1
With $oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ActionDesc")
    .Orientation = $xlPageField
    .Position = 1
EndWith
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("ActionDesc").CurrentPage = "(blank)"

$oExcel.ActiveSheet.PivotTables("BL").AddDataField($oExcel.ActiveSheet.PivotTables("BL").PivotFields("Tickler"), "Count of Tickler", $xlCount)
$oExcel.ActiveSheet.PivotTables("BL").AddDataField($oExcel.ActiveSheet.PivotTables("BL").PivotFields("Balance"), "Count of Balance", $xlCount)
With $oExcel.ActiveSheet.PivotTables("BL").DataPivotField
    .Orientation = $xlColumnField
    .Position =0
EndWith
With $oExcel.ActiveSheet.PivotTables("BL").PivotFields("Count of Balance")
    .Caption = "Sum of Balance"
    .Function = $xlSum
EndWith
$oExcel.ActiveSheet.Range("B:B").EntireColumn.Select
$oExcel.Application.ActiveSheet.PivotTables("BL").PivotFields("DenialCode").ShowDetail = False
$oExcel.ActiveSheet.Range("A:E").Columns.AutoFit

 

[water]

 

[sudeepjd]

I seemed to be having the same problem.. But ended up writing a library for it. 

You can use it to create and update Pivot tables and Pivot Charts.