steve8tch Posted November 11, 2007 Share Posted November 11, 2007 This code has been created after creating a pivot table whilst recording a macro - but it doesn't work with autoit Has anyone been able to create pivot tables using Autoit ? Thanks. $xls = @TempDir & "\Reports.csv" $sheet = "Reports" Opt("WinTitleMatchMode", 2) $oExcel = _ExcelBookOpen($xls) If IsObj($oExcel) Then $oExcel.ActiveWorkbook.PivotCaches.Add ('SourceType:=xlDatabase', 'SourceData:="Reports!R1C1:R719C21"').CreatePivotTable ('TableDestination:=""', 'TableName:="PivotTable1"', 'DefaultVersion:=xlPivotTableVersion10') $oExcel.ActiveSheet.PivotTableWizard ('TableDestination:=ActiveSheet.Cells(3, 1)') $oExcel.ActiveSheet.Cells (3, 1).Select With $oExcel.ActiveSheet.PivotTables ("PivotTable1").PivotFields ("Tool") .Orientation = "xlRowField" .Position = 1 EndWith With $oExcel.ActiveSheet.PivotTables ("PivotTable1").PivotFields ("Sensor") .Orientation = "xlColumnField" .Position = 1 EndWith $oExcel.ActiveSheet.PivotTables ("PivotTable1").AddDataField ($oExcel.ActiveSheet.PivotTables ("PivotTable1").PivotFields ("Sensor"), "Count of Sensor", "xlCount") With $oExcel.ActiveSheet.PivotTables ("PivotTable1").PivotFields ("WeekNo") .Orientation = "xlPageField" .Position = 1 EndWith $oExcel.ActiveChart.Location ('Where:=xlLocationAsNewSheet') EndIf Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False) $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 With $oExcel .Visible = $fVisible .WorkBooks.Open ($sFilePath, Default, $fReadOnly) .ActiveWorkbook.Sheets (1).Select () EndWith Return $oExcel EndFunc ;==>_ExcelBookOpen 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