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

I have Excel 2007 installed but I need to save my excel file as a .xls, compatible with Excel 2003. My code doesn't seem to be working, it won't recongnize the SaveAs and prompts to save "Book1" any help would be appreciated

use Win32;
use Win32::OLE;
$excelFile = "c:\\perl\_scripts\\printVersions\\dest\\NMSPrintVersions
+.xls";
$xlApp = Win32::OLE->new('Excel.Application');
$xlApp->{Visible} = 0;
$xlBook = $xlApp->Workbooks->Add;
$sheet = $xlBook->ActiveSheet;
$range = $xlBook->ActiveSheet->Range("A1:C1");
$title = ["FILENAME","VERSION","DATEUPDATED"];
$range ->{value} = $title;
$sheet->Columns("B:B") -> {NumberFormat} = "#0.0";
$count=1;
foreach (@array) {
    ($one, $two, $three) = split('\,', $_);
    $mydata = ["$one", "$two", "$three"];
    $count++;
    $range = $xlBook->ActiveSheet->Range("A$count:C$count");
    $range->{Value} = $mydata
}

unlink ($excelFile);
#$xlBook->SaveAs($excelFile);
$xlBook->SaveAs({Filename=>"$excelFile",FileFormat=>xlExcel9795});
$xlBook->Close();
$xlApp->Quit();

[download]

Replies are listed 'Best First'.
Re: Win32::OLE - Excel, Can't save as Previous Version
by dasgar (Priest) on Aug 25, 2011 at 16:01 UTC

    When trying to figure out how to do something in Excel with Win32::OLE, I find it very useful to record a macros to do the task(s) and then check out the source code of the macros. Then I just grab the relevant portions and convert it into Perl code.

    Since I happen to have access to a system with Excel 2007 installed on it, I created a blank Excel 2007 file (.xlsx file extension) and recorded a macros to save it as an Excel 2003 format file. Based on the macros source code, it looks like your file format is in correct. It should be xlExcel8.

    Also, I pulled my source code of a script that I wrote late last year that did this exact thing. In that code, I had the file format as xlExcel8.

    So just change the following line

    $xlBook->SaveAs({Filename=>"$excelFile",FileFormat=>xlExcel9795});

    so that it becomes the following

    $xlBook->SaveAs({Filename=>"$excelFile",FileFormat=>xlExcel8});

    Without testing the rest of your code, I think that should fix it. Of course, I'd suggest that you add in use strict; and use warnings; as davies suggested. Also, if you try his other suggestion of making Excel visible, it will help debug any situations where Excel is using a pop-up window to get input from the user. Once you've got the bugs worked out, you can set Excel to be invisible again if you don't want to watch Excel do what your script tells it to do.

[reply]
[d/l]
[select]
      I checked excel's object browswer and xlExcel9597 seemed to be a valid option. I changed the xlexcel9597 to xlWorkbookNormal and that seemed to have solved the problem.
[reply]
        I had the same issue but found the solution was to not use the drive letter specifier - it broke the saveas method. 'C:\temp\file.xls' did not work '\temp\file.xls' worked fine
[reply]
Re: Win32::OLE - Excel, Can't save as Previous Version
by JaySal (Initiate) on Aug 25, 2011 at 13:56 UTC
    Opps I was missing 
    use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';

    [download]
[reply]
[d/l]

      Oopser - much, much oopser - you are missing

      use strict;
use warnings;

      [download]
      Life's too short for me to try to put everything needed by strict in for you, but I can see several potential problems. Perhaps the most likely is that Excel may be putting up a dialogue box when it tries to save (it certainly would if the file exists already or the directory is mis-spelled or ...), but since you have Excel hidden (why?), you can't see it. And since you don't have warnings, Perl won't tell you. Of course, it's possible that there's a simple typo somewhere that strict would pick up as well or instead.

      Regards,

      John Davies

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom