Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Delete a sheet from Excel

by Anonymous Monk
on Sep 12, 2005 at 08:49 UTC ( #491187=perlquestion: print w/ replies, xml ) Need Help??
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

Comment on Delete a sheet from Excel
Select or Download Code
Re: Delete a sheet from Excel
by planetscape (Canon) 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 (Chancellor) on Sep 12, 2005 at 09:08 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (15)
As of 2014-07-29 13:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (217 votes), past polls