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');

[download]
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

In reply to Re: 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.