http://www.perlmonks.org?node_id=491187

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

Hi all
I am trying to delete an Excel Worksheet Sheet2 through perl. The part of the code is as below
use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; use Win32::OLE::NLS qw(:LOCALE :DATE); use Win32::OLE; use Win32::OLE::Const; #use Win32::API; $Win32::OLE::Warn = 3; # die on errors... my $Constant = Win32::OLE::Const->Load('Microsoft Excel'); $Excel = Win32::OLE->new('Excel.Application', 'Quit') || die "Error launching MS Excel ".Win32::OLE->LastError; $Book = $Excel->Workbooks->Add; $Sheet = $Book->Worksheets(1); $Sheet->{Name} = "Sheet1"; $Excel->WorkSheets("Sheet2")->Delete();
But when I execute this, Excel will ask for deletion conformation. it pops up a menu saying
The selected Sheet(s) will be permanently deleted. . to delete the selected sheets, click ok . to cancel the deletion click, cancel
Now How can a sheet in Excel be deleted such that excel will not ask for any conformation?
Thanks in advance

Replies are listed 'Best First'.
Re: Delete a sheet from Excel
by planetscape (Chancellor) on Sep 12, 2005 at 09:02 UTC

    The equivalent in VBA to the statement you are looking for is:

    Application.DisplayAlerts = FALSE

    Set to false before executing code for which you want to skip confirmations. It's a good idea to set it back to true when you are done. :-)


    Update: BTW, for anyone wondering, the same basic idea in MS Access is called "SetWarnings".

    HTH,

    planetscape
      Thank u very much. it really works. I have one more problem. If I open the file having chart in a new sheet, it pops up a chart menu to customize the chart. Can this also be disabled?
      Thanks once again

        That's a good question. Without testing, I'm inclined to think

        Application.ScreenUpdating=False

        might handle that if Application.DisplayAlerts = FALSE doesn't. Try it, if that doesn't work, we'll see if we can't find something else. ;-)

        An offsite reference that may help too is The Spreadsheet Page with its extensive Excel FAQs.

        Good luck, and keep me posted,


        Update: Not sure this would work either, but you might try hiding/unhiding the sheet containing the chart, at least until/unless you want the user to have control over it (i.e., when you are done). Toggle the sheet's Visible property between xlSheetVeryHidden and xlSheetVisible (constants defined below).

        *** Constant Group: XlSheetVisibility #define xlSheetVisible -1 #define xlSheetHidden 0 #define xlSheetVeryHidden 2

        Update x 2: A couple of other things to try:

        ActiveChart.Deselect de-activates a chart.

        Excel is event-driven. Events may be toggled on/off by setting Application.EnableEvents appropriately.

        planetscape
      I would say Yes for MS ACCESS and its DoCmd.SetWarnings... but if you want to take over Excel and really shut down Excel messages you will have to set the "DisplayAlerts" method of your Excel.Application Object to "False" cheers
Re: Delete a sheet from Excel
by marto (Cardinal) on Sep 12, 2005 at 09:08 UTC