ole problem

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

Hello, Can someone help me with the following problem?
I use this command to retrieve the devicecolumn of my excel sheet:


$devcolumn=getcolumn($sheets, 1,"/dev/disk*");


sub getcolumn    { 
    my ($sheet, $searchdir, $searchstr) = @_;
    $sheet = $Excel->Worksheets($sheet);
    my $LastCol = $sheet->UsedRange->Find({
        What=>$searchstr,
        LookAt=>2,
        LookIn=>-4123,
        MatchCase=>"False",
        SearchDirection=>$searchdir,
        SearchOrder=>xlByColumns})
      -{Column};
    return ${LastCol};
[download]

This works perfect.
But if there is no column with the earchstring "/dev/disk*" I get the following error:

Can't use an undefined value as a HASH reference at .....

I hope there is someone who deal with this problem before.

Greets,
Mieke

Comment on ole problem Download Code

Replies are listed 'Best First'.
Re: ole problem by tachyon (Chancellor) on Aug 09, 2004 at 14:34 UTC
Have you come across the concept of error checking? You are performing two operations in the statement that starts will my $LastCol. First you are getting a value (hash ref or undef on failure), then you are extracting data by derefing the return value. Something like this should work: `my $LastCol = $sheet->UsedRange->Find({ What => $searchstr, LookAt => 2, LookIn => -4123, MatchCase => "False", SearchDirection => $searchdir, SearchOrder => xlByColumns }); return $LastCol ? $LastCol->{Column} : undef;` [download] You also have a typo in the post as it should be ->{Column}. And -4123 looks like a constant to me so you should not be hard coding it. Same for the LookAt => 2 probably. `use Win32::OLE::Const 'Microsoft Excel'; my $x = Win32::OLE::Const->Load("Microsoft Excel 9\\.0 Object Library" +); for my $key ( sort { $x->{$a} <=> $x->{$b} } keys %$x) { printf "$key = %s\n", $x->{$key}; }` [download] cheers tachyon	[reply] [d/l] [select]
Re: ole problem by jdporter (Paladin) on Aug 09, 2004 at 14:35 UTC
The problem is that `UsedRange->Find()` returns a hashref which you immediately dereference with `->{'Column'}`. But it only does that if the search finds something. If it doesn't, it returns undef, so the deref breaks. You should test the value returned from `UsedRange->Find()` before you try to dereference it.	[reply] [d/l] [select]