Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Overwriting an excel file

by Rupa (Initiate)
on Feb 15, 2010 at 10:32 UTC ( [id://823261]=perlquestion: print w/replies, xml ) Need Help??

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;

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();
      thanks it worked... :)
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
Re: Overwriting an excel file
by Gangabass (Vicar) on Feb 15, 2010 at 12:33 UTC

    You are saving worksheet but must save workbook.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://823261]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-03-28 20:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found