xcaliber13 Posted June 18, 2018 Share Posted June 18, 2018 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 comment Share on other sites More sharing options...
water Posted June 18, 2018 Share Posted June 18, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted June 18, 2018 Author Share Posted June 18, 2018 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 comment Share on other sites More sharing options...
xcaliber13 Posted June 18, 2018 Author Share Posted June 18, 2018 Water sorry it does populate but it is adding a filter and not a value Link to comment Share on other sites More sharing options...
water Posted June 18, 2018 Share Posted June 18, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted June 18, 2018 Author Share Posted June 18, 2018 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 comment Share on other sites More sharing options...
xcaliber13 Posted June 18, 2018 Author Share Posted June 18, 2018 Water, Found what I needed. Thank you for your help. Got it working Link to comment Share on other sites More sharing options...
water Posted June 18, 2018 Share Posted June 18, 2018 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted June 19, 2018 Author Share Posted June 19, 2018 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 comment Share on other sites More sharing options...
water Posted June 19, 2018 Share Posted June 19, 2018 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sudeepjd Posted October 4, 2020 Share Posted October 4, 2020 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now