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

Howdy - I have strange Perl problem. I get zipped XLS file generated from a database. I wrote some Win32::OLE code to unzip file and extract data however I keep running into same issue.
If file was just extracted and never manually opened and OLE tries to get hold of Excel Workbook object the procedure fails.
If file was manually opened and then saved then OLE works as expected.
I use fairly standard way of extracting data: 
    unless (defined $excel)
    {
        eval 
        {
            $excel = Win32::OLE->GetActiveObject('Excel.Application')
        };
        die "Excel not installed" if $@;
    }
    
    unless (defined $excel) 
    {
        $excel = Win32::OLE->new('Excel.Application', 'Quit')
                or die "Oops, cannot start Excel";
    }
    #to avoid excessive dialogs when saving in non-Excel format
    $excel->{DisplayAlerts} = 0;

        #$excel->Workbooks->Open({ FileName => $fullname_input_file })
    #|| print STDERR "didnt open the file $!\n";

    # get a new workbook
    my $wbook;     
    if($wbook = Win32::OLE->GetObject($fullname_input_file)){;}
    else 
    {
        $wbook = $excel->{Workbooks}->{"$fullname_input_file"};
        if($wbook->Activate()){;}
        else
        {
            print STDERR "Could not open the file $fullname_input_file
+: $!\n";
            exit;
        }
    }

[download]
But if file was not opened manually I get: 
Win32::OLE(0.1709) error 0x800401e6: "Bad extension for file" at oleli
+b.pl line 1286.
        eval {...} called at olelib.pl line 1286
        main::save_excel_file_as_tab_delimited_MS_API_WSC('test2.xls',
+ '__read_in_excel_file_into_array.txt', 'ANNOUNCEMENT') called at ole
+lib.pl line 411
        main::save_excel_file_as_tab_delimited('test2.xls', '__read_in
+_excel_file_into_array.txt', 'ANNOUNCEMENT') called at olelib.pl line
+ 1970
        main::read_in_excel_file_into_array_WSC('test2.xls', 'ARRAY(0x
+2b4a334)', 'ANNOUNCEMENT') called at olelib.pl line 1798
        main::read_in_excel_file_into_array('test2.xls', 'ARRAY(0x2b4a
+334)', 'ANNOUNCEMENT') called at test_unzip_in_place.pl line 107
Win32::OLE(0.1709) error 0x80020009: "Exception occurred"
    in METHOD/PROPERTYGET
"c:\ecdev\src\src_dev\model\sq\scripts\test\test_unzip\test2.xls" at o
+lelib.pl line 1297.
Can't call method "Activate" on an undefined value at olelib.pl line 1
+298.

[download]
So basic problem is that I cannot seem to get hold of $wbook object. If I just open the file and do Ctrl-S to save it and then run the script then everything runs as expected.

Replies are listed 'Best First'.
Re: Win32::OLE for Excel fails to open newly generated XLS file
by Corion (Patriarch) on Jan 26, 2016 at 08:40 UTC

    You have switched off DisplayAlerts - this means that Excel will not tell you what it thinks is wrong.

    Also, you don't show us how you save the file, or what the filename is. "Bad extension for file" suggests that Excel expects something different than what you give it.

    Also see maybe Text::CSV_XS to read the file, or Spreadsheet::Read.

[reply]
[d/l]
      You have switched off DisplayAlerts - this means that Excel will not tell you what it thinks is wrong.
      True. I did it because otherwise I get extra dialogs on SaveAs as eg. text. I tried adding it back in with:
      $Win32::OLE::Warn = 2;
      Also, you don't show us how you save the file, or what the filename is. "Bad extension for file" suggests that Excel expects something different than what you give it.
      This is a bit of grey area. I use Excel 2013 that expects XLSX extension but file that I get come with older XLS extension.
      In any case - I changed my Workbook code to:
          # get a new workbook
    my $wbook;     
    $wbook = $excel->Workbooks->Open({ FileName => $fullname_input_fil
+e });
    $wbook->Save();

    unless(defined $wbook) {    
        if($wbook = Win32::OLE->GetObject($fullname_input_file)){;}
        else 
        {
            $wbook = $excel->{Workbooks}->{"$fullname_input_file"};
            if($wbook->Activate()){;}
            else
            {
                print STDERR "Could not open the file $fullname_input_
+file: $!\n";
                exit;
            }
        }
    }

      [download]
      and it seemed to work but it still feels like doing voodoo.
      Also see maybe Text::CSV_XS to read the file, or Spreadsheet::Read.
      Thank you for these suggestions - I went OLE way because I need to modify a lot of Excel files in place and I have not found a module that would cover it. For this particular issue - Excel files come as database-generated reports and extracting data from them is the only task so OLE is not strictly speaking necessary.
[reply]
[d/l]
[select]

        Maybe I misunderstand you - if you have .xls files, you cannot rename them to .xlsx and expect them to work.

        If you want to read .xls files from Perl, see Spreadsheet::ParseExcel.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom