Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

OLE, Excel, and PERL

by Anonymous Monk
on Oct 30, 2002 at 17:23 UTC ( [id://209156]=perlquestion: print w/replies, xml ) Need Help??

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

Hello, I installed Active State 5.6.1 rev 633 on a Win2K box. When I try to run the following progralm I get the error 
C:\Documents and Settings\dln1\My Documents\PERL>perl excel.pl
Can't call method "Open" on an undefined value at excel.pl line 16.

[download]
The source is: 
use OLE;

$xlfile ='Book1.xls';

##### OLE - Excel Connection

# Create OLE object - Excel Application Pointer
$xl_app = CreateObject OLE 'Excel.Application' || die $!;

# Set Application Visibility
# 0 = Not Visible
# 1 = Visible
$xl_app->{'Visible'} = 0;

# Open Excel File
$workbook = $xl_app->Workbooks->Open($xlfile);

# setup active worksheet
$worksheet = $workbook->Worksheets(1);

# retrieve value from worksheet


$cellA1 = $worksheet->Range("A1")->{'Value'};
$cellB1 = $worksheet->Range("B1")->{'Value'};

print "Cell A1 = $cellA1";


$worksheet->Range("A1")->{'value'} = "01aBcD2";
$cellA1 = $worksheet->Range("A1")->{'Value'};
print "\nCell A1 = $cellA1";
$cellA1 =  uc $cellA1;
print "\nCell A1 = $cellA1";

# Close It Up
$xl_app->ActiveWorkbook->Close(0);
$xl_app->Quit();

[download]
I verified the file exists and can be opened by excel. Any assistance in diagnosing the problem would be appreciated. Thanks, Daniel

Replies are listed 'Best First'.
Re: OLE, Excel, and PERL
by Mr. Muskrat (Canon) on Oct 30, 2002 at 18:05 UTC

    I'd use Win32::OLE as OLE.pm is deprecated but that's not the problem. You must give the full path to the Excel spreadsheet before it will work.

    This is not the greatest example (I haven't included any error checking) but it does work.

    use strict;
use warnings;
use Win32::OLE;
my $xlfile ='c:\windows\desktop\test\Book1.xls';
my $xl_app = Win32::OLE->new("Excel.Application");
$xl_app->{'Visible'} = 0;
my $workbook = $xl_app->Workbooks->Open($xlfile);
my $worksheet = $workbook->Worksheets(1);
my $cellA1 = $worksheet->Range("A1")->{'Value'};
my $cellB1 = $worksheet->Range("B1")->{'Value'};
print "Cell A1 = $cellA1";
$worksheet->Range("A1")->{'value'} = "01aBcD2";
$cellA1 = $worksheet->Range("A1")->{'Value'};
print "\nCell A1 = $cellA1";
$cellA1 =  uc $cellA1;
print "\nCell A1 = $cellA1";
$xl_app->ActiveWorkbook->Close(0);
$xl_app->Quit();

    [download]

[reply]
[d/l]
      Thanks for your help. I created a file C:\tmp\Book1.xls and updated your code and ran the program and got: 
      C:\Documents and Settings\dln1\My Documents\White Papers\AANC>perl tes
+t.pl
Win32::OLE(0.1502) error 0x80070005: "Access is denied"
    in PROPERTYPUT "Visible" at test.pl line 6
Win32::OLE(0.1502) error 0x80070005: "Access is denied"
    in METHOD/PROPERTYGET "" at test.pl line 7
Can't call method "Open" on an undefined value at test.pl line 7.

      [download]
      It appears there is something wrong. I do not understand what or how to diagnose. Any ideas?
[reply]
[d/l]

        Do you have Microsoft Excel installed on that computer?

[reply]
Re: OLE, Excel, and PERL
by cacharbe (Curate) on Oct 30, 2002 at 18:32 UTC

    When you add a little error checking, what happens? 

    use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # Die on Errors.

my $excelfile = 'book1.xls';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit')
            || die Win32::OLE->LastError(); 

   $Excel->{DisplayAlerts}=0;   

my $Book = $Excel->Workbooks->Open($excelfile) or die Win32::OLE->Last
+Error();

print Win32::OLE->LastError();

    [download]

    And of course, I would be remiss if I didn't mention the Tutorial

    C-.

    ---
    Flex the Geek

[reply]
[d/l]
Re: OLE, Excel, and PERL
by jsprat (Curate) on Oct 30, 2002 at 20:23 UTC
    The code shown will never die here:

    $xl_app = CreateObject OLE 'Excel.Application' || die $!;

    Your code will never die here. || binds to the constant string 'Excel.Application', not the method call. Here's how perl parses that line (see that there is no 'die' in the parsed code!):

    C:\>perl -MO=Deparse,p -MWin32::OLE -e "$xl_app = CreateObject OLE 'Ex
+cel.Applic
ation' || die $!;"
$xl_app = 'OLE'->CreateObject('Excel.Application');
-e syntax OK

    [download]

    Use or in place of ||, or use parens to explicitly show the precedence you want. Now when the code fails, it will die with an appropriate error message.

    Note: It would be more idiomatic to write that line the same way perl parses it ( $xl_app = 'OLE'->CreateObject('Excel.Application') ) instead of the function-like way it is written.

[reply]
[d/l]
[select]
Re: OLE, Excel, and PERL
by Enlil (Parson) on Oct 30, 2002 at 17:43 UTC
    Is the excel file in the current working directory (the location where you are running the perl script). If not you might want to move the script to where the excel file exists or change the $xlfile value to include the whole path. This would be my first assumption.

    UpdateAnother module you might want to look into is Spreadsheet-ParseExcel.

    -enlil

[reply]
      Yes, I check the file out using both a full path and local path. I also have 3 other programs that exhibit the same problem that have worked in the past. Any other ideas on diagnosing or solving?
[reply]
Re: SOLVED - OLE, Excel, and PERL
by Anonymous Monk on Nov 01, 2002 at 16:04 UTC
    Hello, Thanks for all the help (especially Mr. Chuck.) I found the problem. This one is rather embarrassing, but interesting so I thought I'd let the group in on it. I was racking my brain thinking what the hell could enforce permissions outside the standard Win2K user stuff. Then I remembered. A while back I installed Network Associates VirusScan Console software in order to be able to connect from the outside into my client site. I shut it off and reran the test. Worked like a charm. Here's the pertinant diagnositic program: CODE: 
    C:\Documents and Settings\dln1\My Documents\White Papers\AANC>type tes
+t2.pl
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # Die on Errors.

my $excelfile = 'C:\tmp\Book1.xls';

# VERIFY EXISTENCE
print "LISTING $excelfile\n";
print `dir $excelfile`;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit')
            || die Win32::OLE->LastError();

   $Excel->{DisplayAlerts}=0;

#my $Book = $Excel->Workbooks->Open($excelfile) or die
my $Book = $Excel->Workbooks->Add() or die Win32::OLE->LastError();

Win32::OLE->LastError();

print Win32::OLE->LastError();

    [download]
    OUTPUT: 
    C:\Documents and Settings\dln1\My Documents\White Papers\AANC>perl tes
+t2.pl
LISTING C:\tmp\Book1.xls
 Volume in drive C is LOCAL DISK
 Volume Serial Number is 9C3D-51DA

 Directory of C:\tmp

10/30/2002  10:17a              13,824 Book1.xls
               1 File(s)         13,824 bytes
               0 Dir(s)   8,263,262,208 bytes free
Win32::OLE(0.1502) error 0x80070005: "Access is denied"
    in PROPERTYPUT "DisplayAlerts" at test2.pl line 17

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://209156]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2024-04-19 17:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found