Win32::OLE(0.1702) error 0x80020011

Nalina has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks I get an error when trying to get a windows id of excel(HWND) the error is


Win32::OLE(0.1702) error 0x80020011: "Does not support a collection"
    in METHOD/PROPERTYGET "Hwnd" at c:/AsgReportScripts/dailyscripts/d
+ailyreport
.pl line xxx
[download]

The part of the script where the error comes is as follows.


use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use Win32::OLE;
use Win32::OLE::Const;
$Win32::OLE::Warn = 3; # die on errors...


$file = "C:\\testw.txt";
open (TTT, ">$file") || die "error";

$filename = "C:\\book1.xls";
$filter = 'gif'; # can be GIF, JPG, JPEG or PNG    
$pngnm = "C:\\test";

eval{
my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); # use the Ex
+cel application if it's open, otherwise open new
$hwnd = $Excel->{"Hwnd"};
print TTT $hwnd;
my $Book = $Excel->Workbooks->Open( $filename ); # open the file
foreach my $Sheet (in $Book->Sheets) 
{
    # loop through all sheets
    foreach my $ChartObj (in $Sheet->ChartObjects) 
    {
        # loop through all chartobjects in the sheet
        
        #$datewk = join("".split(/\s+/, $Date));
                
        $savename =  "$pngnm" . ".$filter";
        #$savename = "$dirpath\\$pngnm" . ".$filter";
        
        # Write image to PNG file
        
    $t = $ChartObj->Chart->Export({
                        FileName => $savename,
                        FilterName => $filter,
                        Interactive => 0
                    });
        
    }
}
        
$Book->Close({SaveChanges=>0});
};
 $rrr = Win32::FormatMessage(Win32::GetLastError());
$err = $@;
print TTT $err;
print TTT "\n $t=> $rrr";
[download]

What could be the problem?
Thanks in Advance

Comment on Win32::OLE(0.1702) error 0x80020011 Select or Download Code

Replies are listed 'Best First'.
Re: Win32::OLE(0.1702) error 0x80020011 by InfiniteSilence (Curate) on Aug 29, 2005 at 19:45 UTC
Hey, except for the cryptic error this code is really neat. I didn't realize you could export embedded charts to .gifs like that. I suppose I will hang onto this. At any rate, your code bombs out because you are trying to use the Windows handle when you really do not need to. Here is a slightly modified version of your code: ... $file = "C:\\testw.txt"; open (TTT, ">$file") \|\| die "error"; $filename = "C:\\temp\\frufru.xls"; $filter = 'gif'; # can be GIF, JPG, JPEG or PNG $pngnm = "C:\\test"; eval{ my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); # use the Ex +cel application if it's open, otherwise open new #why in the hell would you want to do this? #$hwnd = $Excel->{"Hwnd"}; #Hell no print TTT $hwnd; die "Cannot find $filename" unless (-e $filename); my $Book = $Excel->Workbooks->Open( $filename ); # open the file foreach my $Sheet (in $Book->Sheets) { ... [download] I created a single workbook with three spreadsheets and two charts (on the first and second sheet). I debugged all the way until it created the first chart and got bored. Looks like it will work. What's different? Well, notice I commented out the following line: `#$hwnd = $Excel->{"Hwnd"};` [download] Grabbing the Windows handle to Excel is unnecessary since you already have a working one. I suspect you are using some deprecated example code. I also noticed some people made a few style points about this code. I will add a few also: You have lots of hard coded paths in this. You might try using the GetOpt::Long module to make is reusable. You didn't check to see if the .xls file actually existed before trying to open and work with it. Are you certain you want one big eval() around this whole thing or wouldn't you rather have a few evals around different areas that are likely to fail so that you can recover? For instance, you may want to look for a given file in an alternative location? And yes, you REALLY must use strict. Really. Celebrate Intellectual Diversity	[reply] [d/l] [select]
Re: Win32::OLE(0.1702) error 0x80020011 by wfsp (Abbot) on Aug 29, 2005 at 13:31 UTC
Hi, It would be easier for you and those prepared to help if you used strict and warnings and declared all your variables. I was able to run your script without error (although I didn't have an excel file handy with any chart objects, so the inner for loop wasn't touched). I didn't get the error you show above with excel 2003. testw.txt contained: `983882 => The operation completed successfully.` [download] update: corrected typo	[reply] [d/l]
Re: Win32::OLE(0.1702) error 0x80020011 by Roger (Parson) on Aug 29, 2005 at 14:03 UTC
I suspect that you had this script working in the past, but after you have upgraded the version of Windows (possibly to XP?) and also the version of office, it stopped working. Is that so? The old version of office products and the XP versions are different. Old versions have a single main window, and workbooks open in the same window in MDI (multiple document interface) mode. The new version of office products have multiple windows, one window for each active workbook. They group into a collection of windows for the same application. You will need to first determine the active document (or active workbook), and then operate on the active document. I haven't run your code, so this is all guess work.	[reply]
Re: Win32::OLE(0.1702) error 0x80020011 by mooch (Initiate) on May 19, 2008 at 18:15 UTC
I had the same thing happen. After checking my code a few times, I finally noticed that I had written: under $ex; rather than: undef $ex; Corrected the code and am no longer getting the error, so I think that was the problem.	[reply]


Perl-Sensitive Sunglasses
	PerlMonks