my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
[download]

Thanks for the detailed response. To answer your first question, my understanding is that the  qw (in with) is necessary to allow use of the in and with methods without qualifying them with the full modules name as I'm guessing that Win32::Ole doesn't export these methods automatically? I am using the with to set zoom and other page setup properties

As to your other comments they are much appreciated, however many of those lines were lifted right out of the Win32::Ole tutorial. Points taken...

My intention is to have drop down lists in a worksheet which are populated with data from another workbook. Maybe the only answer is to import all of the data from the other workbook into worksheets which I can set to hidden and then use advanced filters as you suggest with references to ranges within the hidden worksheets? I have been trying to use named ranges as you suggest such as $sheet->Names->Add({Name => 'IO', RefersTo => $sheet->Range("A1:A13")});

However I cannot figure out how to actually use the named range to assign data. Everytime I do something like

$sheet->Range->Name->{'IO'} = \@data;
[download]

I get strange errors...

     However I cannot figure out how to actually use the named range to assign data. Everytime I do something like ... I get strange errors

Two real quick points. The first deals with the usage of named ranges. Let's forget Perl for a second. In Excel, you use a named range for lookups, such as a data validation on a cell. I can't think of a scenario where it makes since to "write" data to a named range. In other words, manual data entry is done with in a single cell (or merged cells), not a named range.

Secondly, are you sure you want to use \@data as the value being written into Excel? Ok, I admit that I'm not as good as I should be about reference syntax in Perl, but I believe that will return either the array address for @data or a list of it's contents. In either case, that's not what Excel would be expecting. It's expecting scalar values (strings, integers, etc) and not memory addresses or lists.

Hopefully this helps you understand part of the reasons that you might be seeing some of the errors.

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

Here is part 2! In this code, I demonstrate the two types of drop down lists, adding a combobox to one of the sheets on the fly. I haven't created a form on the fly, as I imagine any form you would want would be static. You don't need to link the combobox to a cell, but it can be useful so I have demonstrated it. A1 won't reveal itself to be a dropdown unless it is the active cell. But both pull their list from the dynamic range on the other sheet. Positioning the combobox can be done using the same techniques I developed in response to 819178.


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;

#Set up the sheets
my $nSheets = $wb->Sheets->Count;
if ($nSheets == 1) {
    $wb->Sheets->Add({After=>$wb->Sheets(1)});
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtDrop = $wb->Sheets(1);
my $shtList = $wb->Sheets(2);
$shtDrop->{Name} = "DropBoxes";
$shtList->{Name} = "List";

#Create named ranges
$wb->Names->Add({Name=>'zTopList', RefersTo=>'=List!$A$1'});
$wb->Names->Add({Name=>'zEndList', RefersTo=>'=List!$A$2'});
$wb->Names->Add({Name=>'zList', RefersTo=>'=OFFSET(zTopList,1,0,ROW(zE
+ndList)-ROW(zTopList)-1,COLUMN(zEndList)-COLUMN(zTopList)+1)'});

#Create a list of valid options for the dropdown
$shtList->Range('zTopList')->{Value}='A';
$shtList->Range('zEndList')->{Value} = "-";
$shtList->Range('zEndList')->{HorizontalAlignment} = 5; #xlFill
for (1 .. 9) { #Insert some data
    $shtList->Range('zEndList')->EntireRow->Insert;
    $shtList->Cells($_ + 1, 1)->{Value} = $_;
}

#Data Validation DropDown
$shtDrop->Range('A1')->Validation->Add({Type      => 3, #xlValidateLis
+t
                                        AlertStyle=> 1, #xlValidAlertS
+top
                                        Operator  => 1, #xlBetween
                                        Formula1  =>'=zList'});
$shtDrop->Range('A1')->Validation->{IgnoreBlank}    = 0;
$shtDrop->Range('A1')->Validation->{InCellDropdown} = 1;
$shtDrop->Range('A1')->Validation->{ShowInput}      = 1;
$shtDrop->Range('A1')->Validation->{ShowError}      = 1;

#ComboBox
my $cbo = $shtDrop->OLEObjects->Add({ClassType    => "Forms.ComboBox.1
+",
                                     DisplayAsIcon=> 0,
                                     Left         => 48,
                                     Top          => 26.25,
                                     Width        => 96.75,
                                     Height       => 25.5});
$cbo->{LinkedCell}    = 'A2';
$cbo->{ListFillRange} = 'zList';

$shtDrop->Activate;
$xl->{EnableEvents}   = 1;
[download]

Regards,

John Davies

First, I would make sure that you can do this manually in Excel. If you can't, I'm not sure how to help you get Win32::OLE to do it. (By the way, I believe that I have seen this before Excel spreadsheets, so I think it is possible.)

Secondly, I would try to look at the OLE browser that is part of the Win32::OLE module and look specifically at the Excel library. Given that I don't have a good Basic/VB background, I admit that it's about like reading Greek for me.

Third, try to record a macros to do what you want and then look at its source code. I don't translate the VB to Perl line by line, but it should give you an idea what needs to be done. (For example, I usually ignore the select and activation lines.)

I'm out of town right now without a system that has both Excel and Perl, so I can't try testing/creating codes to help you right now. If I don't get swamped with other things, I'll try to look at this again in a few days and post some suggested code, but I make no promises. In the mean time, I hope that this information might help you down the right path to finding a solution.