We help IT Professionals succeed at work.

Can't call method "Range" on an undefined value

KFFrench
KFFrench asked
on
Medium Priority
1,247 Views
Last Modified: 2012-06-22
I can't seem to get past this point in my code. For some reason Perl is unable to read some cells of the data in an Excel spreadsheet and returns the error: Can't call method "Range" on an undefined value. Here is the code I'm using:

my $Sheet = $Book->Worksheets ("Data");
my @CF = ($ID, $SO, $LI, $QT, $CR->Date(DATE_SHORTDATE), $CR->Time,
          $MOD->Date(DATE_SHORTDATE), $MOD->Time);
sub GetCF($){
    my ($tgtRows)= @_;
        my $Sheet;
            Return ($Sheet->Range("A".$tgtRows)->{'Value'},
                    $Sheet->Range("L".$tgtRows)->{'Value'},
                    $Sheet->Range("M".$tgtRows)->{'Value'},
                    $Sheet->Range("S".$tgtRows)->{'Value'},
                    $Sheet->Range("V".$tgtRows)->{'Value'},
                    $Sheet->Range("Z".$tgtRows)->{'Value'});  
Comment
Watch Question

Commented:
In the code above, perl doesn't even try to read the cells of an Excel sheet :)

In the first line, you set
  my $Sheet = $Book->Worksheets ("Data");
*But* in the sub you have the line:
  my $Sheet;

This line in the sub creates a *new* lexically scoped variable named $Sheet. It is *not* the same as the $Sheet outside of the sub.
The new variable $Sheet in the sub is never assigned to, thus it is undef by default.
Then, $Sheet->Range() triggers the error you described, which is obvious. In the sub $Sheet is undefined, thus you can't call the method Range() on it. It's not an object, it's just undef!

Either delete the line
  my $Sheet;
from the sub competely, or change both declarations of $Sheet to "our $Sheet".

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok, so I changed my $Sheet to Our $Sheet in both places and it now says that the subroutine is undefined. I have ialso included the remaing parts of scripts where I try to create the $tmphash.

our $Sheet = $Book->Worksheets ("Data");
my @CF = ($ID, $SO, $LI, $QT, $CR->Date(DATE_SHORTDATE), $CR->Time,
          $MOD->Date(DATE_SHORTDATE), $MOD->Time);
sub GetCF($){
    my ($tgtRows)= @_;
        our $Sheet;
            Return ($Sheet->Range("A".$tgtRows)->{'Value'},
                    $Sheet->Range("L".$tgtRows)->{'Value'},
                    $Sheet->Range("M".$tgtRows)->{'Value'},
                    $Sheet->Range("S".$tgtRows)->{'Value'},
                    $Sheet->Range("V".$tgtRows)->{'Value'},
                    $Sheet->Range("Z".$tgtRows)->{'Value'});

@CF=GetCF($Rows);

while ($Sheet->Range("A". ++$Rows)->{'Value'}){
    my @CF = GetCF($Rows);
    if (!exists $tmphash {$CF[1]}->{$CF[2]}){
        print $CF [1][2];
    }
}

Commented:
Sorry but I don't know the Excel modul at all. So I'm afraid I can't help you any further.
If you don't bother about the points, just post the new problem in a new question. If you do bother, ask in
http://www.experts-exchange.com/Community_Support/
to undo the accept and reopen the question. Other experts won't read this and provide answers as long as the question is marked as accepted.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.