Reading Excel

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

I am trying to read an Excel workbook with multiple worksheets. While running the script it is coming as can't call method worksheets on an undefined value.Please help me how to sort out


    use Win32::OLE qw(in with);
    use Win32::OLE::Const 'Microsoft Excel';
   # $Win32::OLE::Warn = 3;                                # die on er
+rors...
    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');  # get alread
+y active Excel
                                                          # applicatio
+n or open new
     $Book = $Excel->Workbooks->Open("C:/perlpractice/FU_HMI_List.xlsm
+"); # open Excel file
     

     $Sheet = $Book->Worksheets(3);       

     # select worksheet number 1
    $array = $Sheet->Range("B1:E40")->{'Value'};        # get the cont
+ents
    $Book->Close;
    $i=0;
    $j=0;
    foreach  $ref_array (@$array) {                     # loop through
+ the array
                                   $i++;  

                                   $j=0;
        foreach  $scalar (@$ref_array) {
                  $AOA[$i][$j]="$scalar";
                  $j++;
                  print $j;
        }
       
    } 
print "$AOA[1][1]";
[download]

Comment on Reading Excel Download Code

Replies are listed 'Best First'.
Re: Reading Excel by NetWallah (Canon) on Jan 27, 2017 at 06:01 UTC
It looks like your $Book object is undefined. Try adding error-checking: `$Win32::OLE::Warn = 3; # Die on Errors. my $excelfile = 'C:/perlpractice/FU_HMI_List.xlsm'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') or Win32::OLE->new('Excel.Application', 'Quit') or die Win32::OLE->LastError(); my $Book = $Excel->Workbooks->Open($excelfile) or die Win32::OLE->Last +Error(); my $Sheet = $Book->Worksheets(3) or die Win32::OLE->LastError();` [download] "use strict;" would help too, in the long run. ...it is unhealthy to remain near things that are in the process of blowing up. man page for WARP, by Larry Wall	[reply] [d/l]
Re^2: Reading Excel by ravi179 (Novice) on Jan 27, 2017 at 06:28 UTC
OLE exception from "Microsoft Excel" unable to get the open property of the workbook class Win32::ole(0.1709)error 0x800a03ec in method/propertyget "open" at C:\perl\Read.pl line 9 iam getting this	[reply]
Re^3: Reading Excel by Corion (Patriarch) on Jan 27, 2017 at 07:03 UTC
Please show the code you are running. Note that the error message complains about `open` with a lower case "o". The Excel method is named `Open`, with an upper case "O".	[reply] [d/l] [select]
Re^4: Reading Excel by ravi179 (Novice) on Jan 27, 2017 at 07:12 UTC
Re: Reading Excel by Ratazong (Monsignor) on Jan 27, 2017 at 07:06 UTC
My guess is also that opening the file fails. Is the .xlsm-file you want to open really there? Have you tried opening it from the same directory your perl-script is in (so without a path)? In my experience Excel is sometimes picky related to path and path delimiters (slash vs. backslash). In my scripts I always use `$Excel->Workbooks->OpenXML` instead of `$Excel->Workbooks->Open`. Maybe this works for you? HTH, Rata	[reply] [d/l] [select]
Re^2: Reading Excel by ravi179 (Novice) on Jan 27, 2017 at 08:22 UTC
I want to open .XLSM only.i tried using OpenXML.But it didn't help.	[reply]
Re^3: Reading Excel by Corion (Patriarch) on Jan 27, 2017 at 09:17 UTC
How did using `->OpenXML()` fail for you? What was the error message? What was the script you used?	[reply] [d/l]
Re^4: Reading Excel by ravi179 (Novice) on Jan 27, 2017 at 09:29 UTC
Re^5: Reading Excel by Corion (Patriarch) on Jan 27, 2017 at 09:35 UTC
A reply falls below the community's threshold of quality. You may see it by logging in.


good chemistry is complicated, and a little bit messy -LW
	PerlMonks