Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

win32::ole excel savechanges

by Anonymous Monk
on Jan 09, 2012 at 10:38 UTC ( #946980=perlquestion: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re: win32::ole excel savechanges
by davies (Prior) on Jan 09, 2012 at 13:27 UTC

    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;


    John Davies

Re: win32::ole excel savechanges
by ww (Archbishop) on Jan 09, 2012 at 13:52 UTC
    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.

Re: win32::ole excel savechanges
by opitzs (Sexton) on Jan 10, 2012 at 09:26 UTC
    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.

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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://946980]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2023-06-06 12:44 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (26 votes). Check out past polls.