Jump to content
xcaliber13

(SOLVED)Excel Pivot table help macro to AutoIt

Recommended Posts

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

Share this post


Link to post
Share on other sites
water

Please define

15 minutes ago, xcaliber13 said:

cannot get my AutoIt script to work

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
xcaliber13

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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
xcaliber13

Water,

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

Share this post


Link to post
Share on other sites
water

:) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
water

:) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×