No replies to this topic

[steve8tch]

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.

AutoIt        expandcollapse  popup

$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