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

Hi All

I would like to save an Excel file regardless if it exists or not!
I’ve used this; 
$xls -> ActiveWorkbook -> SaveAs( $ARGV[0] );

[download]
But, if the file exists then I get prompted to over right the file. That’s what I don't want to happen, I don't want to get prompted to save the file, I want the script to go ahead and save the file in any case.

I did try this (being a bit naive) 
$xls -> ActiveWorkbook -> Save( $ARGV[0] );

[download]
I.e. changed the "SaveAs" to "Save", but it did not work. I checked the Win32::OLE documentation but I couldn't find anything about "Save" rather than "SaveAs".

Installing The AutoSave add-in for every excel installation is not an option.

Any Perls of wisdom and perhaps an advise on where do you guys get this information from, because I have searched everywhere that I know to no avail, will be highly appreciated.

Thanks

Blackadder

Replies are listed 'Best First'.
Re: OLE Save and SaveAs
by jmcnamara (Monsignor) on Sep 07, 2003 at 21:33 UTC

    You can turn off Excel's "File ... already exists" warning by setting the Application.DisplayAlerts VBA variable. Here is a short example: 
        #!/usr/bin/perl -w


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


    my $application = Win32::OLE->new("Excel.Application");
    my $workbook    = $application->Workbooks->Add;
    my $worksheet   = $workbook->Worksheets(1);

    $worksheet->Cells(1,1)->{Value} = "Hello World";

    # Get current directory using Cwd.pm
    my $dir = cwd();


    # Turn off some Excel warnings
    $application->{DisplayAlerts} = "False";

    $workbook->SaveAs($dir . '/win32ole.xls');

    # Turn them back on (just in case)
    $application->{DisplayAlerts} = "True";


    $workbook->Close;

    [download]

    --
    John.

[reply]
[d/l]
Re: OLE Save and SaveAs
by Anonymous Monk on Sep 07, 2003 at 21:33 UTC
    Assuming you have a line somewhere like 
    $ex = Win32::OLE->new('Excel.Application' etc...

.
.
.
$ex->{DisplayAlerts} = 0; # alerts Off
$xls->ActiveWorkbook -> SaveAs( $ARGV[0] ); # save without prompt
$ex->{DisplayAlerts} = 1; # alerts On

    [download]
    poj
[reply]
[d/l]
Re: OLE Save and SaveAs
by guha (Priest) on Sep 08, 2003 at 12:23 UTC
    But, if the file exists then I get prompted to over right the file. That’s what I don't want to happen, I don't want to get prompted to save the file, I want the script to go ahead and save the file in any case.

    You *could* just simply unlink the file on disk before saving.

    unlink $ARGV[0] or die "yada yada $!";
xls -> ActiveWorkbook -> SaveAs( $ARGV[0] );

    [download]

    HTH

[reply]
[d/l]

      Nice lateral thinking, but it is unikely to work in this case. Win32 filesystems will protect files opened by one application from deletion or even being opened unless the first application explicitly permits the second access.

      Examine what is said, not who speaks.
      "Efficiency is intelligent laziness." -David Dunham
      "When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong." -Richard Buckminster Fuller
      If I understand your problem, I can solve it! Of course, the same can be said for you.
[reply]

        OK, rereading the OP, it is not clear whether he's loaded $ARGV[0] from disk, changed and saved to disk again. Or whether he's created the worksheet from scratch and then saved it.

        In the latter case the $ARGV[0].xls, could be the result of an previous run, and would not be held by the OS|Excel.

        The sentence blockquoted in my previous note seem to indicate the latter.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom