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

Hi, I am trying to overwrite and existing excel file - but it should not display waring "Do u want to save the changes.."

I am using the following code:

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my $exlM            = Win32::OLE->GetActiveObject('Excel.Application')
+ || Win32::OLE->new('Excel.Application', 'Quit') || die "Can'nt attac
+h to excel" ;
$exlM->{DisplayAlerts}=0;
my $workbook= $exl->Workbooks->open("D:/Perl/$view_dataM");
my $sheet= $workbook->Worksheets(1);
....
...
$sheetM->cells($a,4)->{Value}="Value";
$sheetM->Save();
$workbookM->Quit;

[download]

Problem is if I put "$exlM->{DisplayAlerts}=0;" then waring message is not getting displayed but also file is not getting saved.

Thanks for any help

Replies are listed 'Best First'.
Re: Overwriting an excel file
by Khen1950fx (Canon) on Feb 15, 2010 at 11:39 UTC
    I don't use Windows, so this script is untested:
    #!/usr/bin/perl

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

my $exlM = Win32::OLE->GetActiveObject('Excel.Application')
  || Win32::OLE->new( 'Excel.Application', sub { $_[0]->Quit; } )
  or die "Can't attach to excel";
$exlM->{'Visible'} = 1;
$exlM->{DisplayAlerts} = 0;
my $workbook = $exl->Workbooks->open('D:/Perl/view_dataM');
my $sheet    = $workbook->Worksheets(1);

$sheet->cells( $a, 4 )->{Value} = "Value";
$workbook->SaveAs(
    {
        Filename   => 'D:/Perl/view_dataM',
        FileFormat => xlWorkbookNormal
    }
);
$workbook->Close();
$exlM->Quit();

    [download]
[reply]
[d/l]
      thanks it worked... :)
[reply]
Re: Overwriting an excel file
by Ratazong (Monsignor) on Feb 15, 2010 at 10:56 UTC

    You can delete the original file before doing the $sheetM->Save() (or even before starting to edit your new Excel-file). At least that is the workaround I use.

    Unfortunately, if you cannot delete the original file (e.g because it is open ...), you will still have your problem. In that case, I would recommend to either turn the warning-dialog on again or to save the new file using another filename

    HTH, Rata
[reply]
[d/l]
Re: Overwriting an excel file
by Gangabass (Vicar) on Feb 15, 2010 at 12:33 UTC

    You are saving worksheet but must save workbook.

[reply]

Back to Seekers of Perl Wisdom