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

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{ReferenceStyle} = 1;    #xlA1. xlR1C1 = -4150?!?!
$xl->{ScreenUpdating} = 0;    #ScreenUpdating is false because the use
+r would not normally need to see the Excel instance churning
$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 == 1) {
    $wb->Sheets->Add({After=>$wb->Sheets(1)});
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtData = $wb->Sheets(1);
my $shtCri  = $wb->Sheets(2);
$shtData->{Name} = "Data";
$shtCri->{Name}  = "Criteria";

for my $sht (1..2) {
    $wb->Sheets($sht)->Cells(1, 1)->{Value} = "A";
    $wb->Sheets($sht)->Cells(1, 2)->{Value} = "B";
    $wb->Sheets($sht)->Cells(2, 1)->{Value} = "-";
    $wb->Sheets($sht)->Cells(2, 2)->{Value} = "-";
    $wb->Sheets($sht)->Range("A2:B2")->{HorizontalAlignment} = 5 #xlFi
+ll
}

$wb->Names->Add({Name=>'zTopData', RefersTo=>'=Data!$A$1'});
$wb->Names->Add({Name=>'zEndData', RefersTo=>'=Data!$B$2'});
$wb->Names->Add({Name=>'zData', RefersTo=>'=OFFSET(zTopData,0,0,ROW(zE
+ndData)-ROW(zTopData),COLUMN(zEndData)-COLUMN(zTopData)+1)'});
$wb->Names->Add({Name=>'zTopCriterion', RefersTo=>'=Criteria!$A$1'});
$wb->Names->Add({Name=>'zEndCriterion', RefersTo=>'=Criteria!$B$2'});
$wb->Names->Add({Name=>'zCriterion', RefersTo=>'=OFFSET(zTopCriterion,
+0,0,ROW(zEndCriterion)-ROW(zTopCriterion),COLUMN(zEndCriterion)-COLUM
+N(zTopCriterion)+1)'});

my $nRows = 98; #Your example has 99 rows in the rnge. This smells lik
+e a deliberate overrun to me, but just in case...
for (1 .. $nRows) { #Insert some random data
    $shtData->Range('zEndData')->EntireRow->Insert;
    $shtData->Cells($_ + 1, 1)->{Value} = int(rand(10)+1);
    $shtData->Cells($_ + 1, 2)->{Value} = int(rand(10)+1);
}

$shtCri->Range('zEndCriterion')->EntireRow->Insert;
$shtCri->Cells(2, 1)->{Value} = int(rand(10)+1);
$shtData->Range('zData')->AdvancedFilter({Action=>1, CriteriaRange=>$s
+htCri->Range('zCriterion')}); #xlFilterInPlace = 1
$shtData->Activate; #To get to the filtered list

$xl->{EnableEvents}   = 1;
$xl->{ScreenUpdating} = 1;

[download]
I feared that the "with" clause was doing what you described. I don't know about other languages, but while it will compile in VB6, it won't execute twice - the second call causes a weird crash. This is documented, but I didn't find the documentation before suffering the wounds. I'm therefore very reluctant to use With except in VBA, where it works.

The code I have written includes no "drop down list". This is a point on which I am unclear what you want. You could have a combobox - the sort of control you might put on a form or on the face of the spreadsheet - or you could use Data Validation of a cell. The techniques are quite different when it comes to populating the list. But since I don't know how you want the list populated anyway, I've left it out.

The two techniques I have used are advanced filter and dynamic ranges. Provided users insert rows between the header and the dotted lines, the data will be handled correctly. But read up on advanced filters. Two criteria on the same line are treated as "and", while on separate lines they are "or". A blank cell matches everything, therefore a blank line will mean that nothing is filtered out. Changing the criteria will not automatically re-work the filter. In 123, this could be done by hitting {F7}, but Bill Gates knows you don't want or need this. Instead, you have to write a macro using Worksheet_Change to do all the work.

Regards,

John Davies

In reply to Re^3: Win32::Ole excel external data range by davies
in thread Win32::Ole excel external data range by anti-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.