win32::ole excel savechanges

[Anonymous Monk]

I'm trying to find out how to quit an excel spreadsheet opened with win32::ole without being prompted to save it. I've tried $Excel->Application->{DisplayAlerts} = 0; but still get prompted. thanks David Barham

[davies]

It would help if you showed us the code that is failing, cut down to the smallest amount that will demonstrate the problem. The other problem is that your explanation isn't precise. You can't "quit" a spreadsheet. You can close a file or quit the application. I suspect that this is where you are going wrong, as I have no problem deleting either of the last two lines from the following code:

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
$xl->{SheetsInNewWorkbook} = 1;
my $wb = $xl->Workbooks->Add;
$xl->VBE->ActiveVBProject->VBComponents->Add(1);

my $cm = $xl->VBE->ActiveVBProject->VBComponents(3)->CodeModule;
my $line = int($cm->CountOfLines);
$cm->InsertLines(++$line,"Function Example(n as double) as double");
$cm->InsertLines(++$line,"Example = n^2");
$cm->InsertLines(++$line,"End Function");

$wb->Sheets(1)->Cells(1,1)->{Formula} = '=example(2)';

$xl->{DisplayAlerts} = 0;
$wb->Close;
$xl->Quit;

Regards,

John Davies

[ww]

Why do you want to evade the save prompt? What's the obstacle to just selecting "no?" Has the spreadsheet been modified during the current open (seems unlikely in context of what you've told us, but still...)?

A search the (extensive) Win32::OLE, may find an answer there.

I would guess, however, that your hope of getting an answer here would be enhanced by providing a more complete problem statement.

[opitzs]

I have no experience with Win32::OLE, but the Excel-Interface itself should give you something like: $wb->Saved = 1; to change the state of the workbook. If the saved state is true, Excel doesn't bother asking anymore. Sorry I can't be of more help, but right now I am exclusively on Linux, so I can't test it.

[Ratazong]

Dear monks!

I currently had a similar problem (reading data from ~100 Excel-sheets), and of course I didn't want to manually click a dialog after each of them. Excel 2013 successfully detected that I was only reading them, however when migrating to Excel 2016, the annoying dialog appeared. Using the solution above prevents that dialog in Excel2016 :-)

Below is my code for your reference

Hope this helps! Rata

    my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); 
    my $workbook   = $Excel->Workbooks->OpenXML("someNameHere");     #
+ open file

    # some code here, only _reading_ from that workbook

    $Excel->{DisplayAlerts} = 0;            # 1. not required for Exce
+l 2013
    $workbook->{Saved} = 1;                 # 2. not required for Exce
+l 2013
    $workbook->close(0);
    $Excel->Workbooks->Close();

[Anonymous Monk]

Thanks that seems to work, the syntax requires {} round the 'Saved'. $wb -> {Saved} = 1; $wb -> Closed(0) ; works without producing a dialogue box. Davud