Jump to content

(SOLVED)Excel Pivot table help macro to AutoIt


Recommended Posts

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

Link to post
Share on other sites

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 (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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

Link to post
Share on other sites

:) 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • xcaliber13 changed the title to (SOLVED)Excel Pivot table help macro to AutoIt

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

 

Link to post
Share on other sites

:) 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • 2 years later...

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...