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

In reply to Re^3: win32::ole pivot sort descending by davies
in thread win32::ole pivot sort descending by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.