Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Win32::Ole excel external data range

by anti-monk (Initiate)
on Aug 06, 2010 at 15:47 UTC ( [id://853430]=perlquestion: print w/replies, xml ) Need Help??

anti-monk has asked for the wisdom of the Perl Monks concerning the following question:

I have searched for an answer to this problem and have not found a solution. I want to read data from a range of cells within one excel workbook and use this as an autofilter range drop down list within a cell of another workbook's worksheet.

use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; my $exceloutputfile = 'c:\workbook_out.xls'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts}=0; $Excel->{Visible} = 1; my $Bookout = $Excel->Workbooks->Add(); $Bookout->SaveAs($exceloutputfile); my $excelinputfile = 'c:\list.xls'; my $Bookin = $Excel->Workbooks->Open($excelinputfile); my $Sheet = $Bookout->Worksheets("Sheet1"); $Sheet->Activate(); $Sheet->{Name} = "Sheet1"; $Sheet->Range("[list.xls]ListSheet1!a1:a99")->AutoFilter; #close and save
The external data reference within the range does not work and I could not find any explicit examples of external data references within the Win32::Ole documentation. Any ideas? Thanks!

Replies are listed 'Best First'.
Re: Win32::Ole excel external data range
by davies (Prior) on Aug 06, 2010 at 18:09 UTC
    Unless this is a feature added in Excel 2007 or later (in which case I can't help you), it can't be done. I can't see why the feature would be implemented, anyway. What would happen if the file containing the criterion range were changed while the file containing the data was closed? Links between simple cells are flaky enough. Besides, Autofilter doesn't take cell references for the criteria. If this is what you want, you need advanced filter, but even then the criterion range must be in the same book (but not necessarily the same sheet) as the data. Here, though, you could get away with links to another file, but you would have to take care that no-one did anything unpatriotic to the second file. To consider your code line by line:

    use Win32::OLE qw(in with); What does the qw(in with) clause do? I don't really understand this aspect of Perl, and have never used it myself except by copy/paste, but I've never seen this variant used on Excel before.

    my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
    I wouldn't do this. I always have any "controlling" software open its own instance of Excel. What would happen if an open instance were running a long macro (some of mine have run for DAYS - literally. Yes, I hated it too)?

    $Excel->{DisplayAlerts}=0; Great for production code, but when debugging, I always want to know what Excel is saying to me. BTW, I assume you're using strict and all the other fruit.

    $Bookout->SaveAs($exceloutputfile); Why save the file now? You are going to save it later (per your comment), so you are just putting strain on the server/disc unnecessarily. There might be good reasons for this that you have cut out of your code.

    $Sheet->Activate(); The VBA equivalent of this litters recorded macros, but should only very rarely appear in production code. Why do you need it activated? Why can't you refer to it as $Sheet?

    $Sheet->{Name} = "Sheet1"; But you know its name already - that's how you set up the reference to $Sheet. Renaming it to the same name can't be what you intend.

    $Sheet->Range("[list.xls]ListSheet1!a1:a99")->AutoFilter; Whatever you intend, this won't do it. It's just possible that autofilter will look for a range named [list.xls]ListSheet1!a1:a99, although that's an invalid name so it can't exist. Another possibility is that it will try to read the name of the range to filter from the given range, but that can't work either as it's a multi-cell range. Most probably, this just can't work.

    As you may gather, I can't be sure what it is you are trying to do, but I don't think it's possible with Autofilter. Your ultimate objective is almost certainly possible, but you will need a different technique. I think what you need is advanced filters, as I said earlier, but you might want to look into dynamic named ranges as a way of managing both the data and the code. They are VERY powerful. I would also suggest recording a VBA macro that actually does what you want and then translating that to Perl.

    Regards,

    John Davies

      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;
      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;
        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;
        Regards,

        John Davies
Re: Win32::Ole excel external data range
by dasgar (Priest) on Aug 07, 2010 at 03:45 UTC

    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://853430]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2024-03-28 17:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found