Jump to content
Sign in to follow this  
steve8tch

Pivot tables

Recommended Posts

steve8tch

This code has been created after creating a pivot table whilst recording a macro - but it doesn't work with autoit :P

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

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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.