Using the win32::ole (on MS) I'd like to create a pivottable (which succeeds btw). And the issue here is that I'm unable to have the perl-created pivottable sort the days in descending order.

So instead of 20,21,22,...,29,30,31 I'd want the pivottable to show the days in reverse order 31,30,29,....,21,20

These are su actions performed on systems. I'd like to show the number of occurences per action/combination per day.

- never ignore root

- "Sum of Aantal" = "Sum of Count" (failed translation...)

I'm still far from sure what you want. I didn't ask about root, but users OTHER THAN root. Even assuming counts rather than sums, I can't see how your numbers come from your data. I don't know how close my code comes to what you want, but I'd be amazed if it didn't need you to do some work.

I've used a data table as I suggested as these are far more customisable than pivot tables. I haven't tidied up my code, so I'm sure that there are lots of improvements that I could make. But I want to watch TV, so you can have it now or you can have it right next month. :-)

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $nSheets = $wb->Sheets->Count; #I want 2 sheets - no more, no less.
+ No matter what $luser's default is.
if ($nSheets < 2) {
    for ($nSheets+1..2) {
        $wb->Sheets->Add({After=>$wb->Sheets($nSheets)});
    }
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtData = $wb->Sheets(1);
my $shtTbl  = $wb->Sheets(2);
$shtData->{Name} = "Data";
$shtTbl ->{Name} = "Table";

$shtData->Cells(1, 1)->{Value} = "Day";
$shtData->Cells(1, 2)->{Value} = "User";
$shtData->Cells(1, 3)->{Value} = "Qty"; #Perhaps, but I have no idea
$shtData->Cells(2, 1)->{Value} = "-";
$shtData->Cells(2, 1)->{HorizontalAlignment} = 5; #xlFill
$shtData->Range("A2")   ->Copy($shtData->Range("B2:C2"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A4"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A7"));

$wb->Names->Add({Name=>'zTopData'      , RefersTo => '=Data!$A$1'});
$wb->Names->Add({Name=>'zEndData'      , RefersTo => '=Data!$C$2'});
$wb->Names->Add({Name=>'zData'         , RefersTo => '=OFFSET(zTopData
+,0,0,ROW(zEndData)-ROW(zTopData),COLUMN(zEndData)-COLUMN(zTopData)+1)
+'});
$wb->Names->Add({Name=>'zTopCriterion' , RefersTo => '=Data!$A$4'});
$wb->Names->Add({Name=>'zEndCriterion' , RefersTo => '=Data!$B$5'});
$wb->Names->Add({Name=>'zCriterion'    , RefersTo => '=OFFSET(zTopCrit
+erion,0,0,ROW(zEndCriterion)-ROW(zTopCriterion)+1,COLUMN(zEndCriterio
+n)-COLUMN(zTopCriterion)+1)'});
$wb->Names->Add({Name=>'zDayData'      , RefersTo => '=OFFSET(zTopData
+,0,0,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zUserData'     , RefersTo => '=OFFSET(zTopData
+,0,1,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zDayCriterion' , RefersTo => '=Data!$A$4:$A$5'
+});
$wb->Names->Add({Name=>'zUserCriterion', RefersTo => '=Data!$B$4:$B$5'
+});
$wb->Names->Add({Name=>'zDayOutput'    , RefersTo => '=Data!$A$7'});
$wb->Names->Add({Name=>'zUserOutput'   , RefersTo => '=Data!$B$7'});

my $nRows = 98;
for (1 .. $nRows) { #Insert some random data
    $shtData->Range('zEndData')->EntireRow->Insert;
    $shtData->Cells($_ + 1, 1)->{Value} = int(rand(11) + 21); #Dates f
+rom 21 to 31
    $shtData->Cells($_ + 1, 2)->{Value} = int(rand(4)  +  1); #4 users
    $shtData->Cells($_ + 1, 3)->{Value} = int(rand(32) +  1); #Somethi
+ng from 1 to 31
}

$shtData->Range('zDayData')->AdvancedFilter({
    Action        => 2,
    CriteriaRange => $shtData->Range('zDayCriterion'), 
    CopyToRange   => $shtData->Range('zDayOutput'), 
    Unique        => 1});

$shtData->Range('zUserData')->AdvancedFilter({
    Action        => 2,
    CriteriaRange => $shtData->Range('zUserCriterion'), 
    CopyToRange   => $shtData->Range('zUserOutput'), 
    Unique        => 1});

my $nLastRowData = $shtData->UsedRange->Rows->{Count};
$shtData->Range($shtData->Range('zUserOutput')->{Address} . ":B" . $nL
+astRowData)->Copy($shtTbl->Range('A1'));
my $nLastRowTbl = $shtTbl->UsedRange->Rows->{Count};
$shtTbl->Range("A2:A" . $nLastRowTbl)->Sort({
    Key1        => $shtTbl->Range("A2"), 
    Order1      => 1, 
    Header      => 0,
    Orientation => 1,
    DataOption1 => 0});

my $rngDays = $shtData->Range($shtData->Range('zDayOutput')->{Address}
+ . ":A" . $nLastRowData);
my $nMin = $xl->Min($rngDays);
my $nMax = $xl->Max($rngDays);
my $col = 2;
for (my $day = $nMax; $day >= $nMin; $day--) {
    $shtTbl->Cells(1, $col++)->{Value} = $day;
}

$shtTbl->Range("A1:A3")->EntireRow->Insert;
$shtData->Range('zCriterion')->Copy($shtTbl->Range("A1"));
$shtTbl->Range("A4")->{Formula} = "=DCOUNT(zData,Data!C1,Table!A1:B2)"
+;
my $lastrow = $shtTbl->UsedRange->Rows   ->{Count};
my $lastcol = $shtTbl->UsedRange->Columns->{Count};
my $lastcell = $shtTbl->Cells($lastrow+1, $lastcol+1)->{Address};
$shtTbl->Range("A4:" . $lastcell)->Table({
    RowInput    => $shtTbl->Range("A2"), 
    ColumnInput => $shtTbl->Range("B2")});
[download]

Regards,

John Davies