Losing EXCEL cell formats on save.

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

This is doing my head in. I have a script that allows me to add some formatting to an existing Excel sheet.
Excel starts up, "flashes" the worksheet complete with formatting to the screen, saves the sheet and closes Excel down.
When I open the sheet from explorer, however, all of the formatting has disappeared!

use strict; 
use Win32::OLE ; 
use Win32::OLE::Const 'Microsoft Excel';

 ########################################################
 ### get already active Excel application or open new

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

 ########################################################
 ### open Excel file
my $Book = $Excel->Workbooks->Open("H:/busyhour_1.xls");
 $Excel->{Visible} = 1;<br>
  my $Sheet = $Book->Worksheets("busyhour_1");
 ########################################################
 ###         format some cells

     $Sheet->Range("a1:d3")->Interior->{ColorIndex} =3;
       $Sheet->Range("a5:b5")->Interior->{ColorIndex} =16;
       $Sheet->Range("a11:j11")->Interior->{ColorIndex}=16;

       $Sheet->Range("a1:c3")->Font->{FontStyle}="Bold";
       $Sheet->Range("a5:b5")->Font->{FontStyle}="Bold";
       $Sheet->Range("a11:j11")->Font->{FontStyle}="Bold";

 ########################################################
 ###         save and close

    $Book->Save;
    $Book->Close;
[download]

Comment on Losing EXCEL cell formats on save. Download Code

Replies are listed 'Best First'.
Re: Losing EXCEL cell formats on save. by maa (Pilgrim) on May 11, 2004 at 12:18 UTC
Weird... how about `$Book->Close(1);` [download]	[reply] [d/l]
Re: Re: Losing EXCEL cell formats on save. by skotte (Initiate) on May 11, 2004 at 12:38 UTC
Weirder still. I downloaded "Spreadsheet-WriteExcel-0.43 " as cyocum recommended and merely unzipped it. I opened "WriteExcel.html" and went to "close" where I noticed the same " ( )" that maa suggested. I tried this and it allowed saving of the formatting (without installing the module) ! Here's the strange bit, when I then removed the "( )" (as a double-check) it still worked.... my head hurts. Anyway, thank you for your help, I can now get back to the task I started at 0800 this AM.	[reply]
Re: Losing EXCEL cell formats on save. by cyocum (Curate) on May 11, 2004 at 12:15 UTC
I would give this Spreadsheet-WriteExcel-0.43 module a shot. It might have already solved your problems. Be ware because I have not used the module myself.	[reply]