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

Thanks monks, for resolving last query. This time I have a question regarding deleting excel worksheets using WIN32::OLE.

Below is the code: 
$sheet_num++;
$Book->Worksheets($sheet_num)->Delete;

[download]

This delete statement doesn't work at all. Please help.

Sorry All. Title is corrected. But Corian & marto, I have tried everything you guyz suggested. But problem is still as it is. About 'delete' command, what I meant to say was, that sheet is not getting deleted.

Replies are listed 'Best First'.
Re: HOW TO DELETE EXCEL WORKSHEETS USING WIN32::OLE
by Corion (Patriarch) on Sep 07, 2010 at 12:19 UTC
[reply]
[d/l]
Re: How to delete Excel worksheets using Win32::OLE
by davies (Monsignor) on Sep 07, 2010 at 13:10 UTC
    You haven't provided working code, so I'm reduced to guessing. However, you may well have a mistake that I often kick myself for making. Excel renumbers sheets after each insert and delete. Therefore, if you have code anything like: 
    for (1..2) {$wb->Sheets($_)->Delete;}

    [download]
    (untested), and there are two sheets in the book, what will happen is that sheet 1 will be deleted, sheet 2 will be re-indexed to 1 and then the attempt to delete sheet 2 will crash, because there is now only 1 sheet in the book.

    Another possibility is that you should be using Sheets() instead of Worksheets(). Working code really does help us to help you. We don't want business logic - we're far more interested in Perl! But, as others have said, please don't shout.

    Regards,

    John Davies

    Update: another possibility is that you are trying to delete every sheet in the book. That will never work - Excel can't accept books with no sheets.
[reply]
[d/l]
[select]
Re: HOW TO DELETE EXCEL WORKSHEETS USING WIN32::OLE
by marto (Cardinal) on Sep 07, 2010 at 12:24 UTC
[reply]
Re: HowTo Delete Excel Worksheets Using WIN32::OLE
by dasgar (Priest) on Sep 07, 2010 at 16:53 UTC

    I agree with the previous responses from others. Also, davies has a very good point about the indexing of the worksheets. Another example of an indexing issue is the scenario where you're code is expecting the worksheets to be in one order, but a user rearranges the order. That will create some problems and also is the reason that I prefer to specify a worksheet by name.

    As Corion pointed out, you're not giving us much information about your problem. Are you getting any errors or are you verifying afterward that the worksheet was not deleted? Also, are you saving changes upon exit? If not, your deletion is not being saved.

    Just for fun, I created a blank Excel spreadsheet with multiple worksheets. I was able to use the code below to delete a worksheet.

    use strict;

use Win32::OLE;

my $efile = "c:\\test2.xls";
my $excel_error = 0;
my $excel = new Win32::OLE('Excel.Application','Quit') || ($excel_erro
+r = 1);
if ($excel_error) {
  undef $excel;
  die "Unable to open Excel.\n";
}

my $book = $excel->Workbooks->Open($efile) || ($excel_error = 1);
if ($excel_error) {
  undef $book;
  undef $excel;
  die "Unable to open Excel file.\n";
}

$book->Worksheets('Sheet4')->Delete();

$book->Close({SaveChanges => 1});
undef $book;
undef $excel;

    [download]

    Hopefully this might help you out.

[reply]
[d/l]

      Hello All. Thanks A lot Again To All you Monks for resolving the issue.

      Davies thanks a lot. Your pointer to excel indexing proved too good.

      Further one thing which actually resolved my problem is 'suppressing confirmation dialogues'. Sheets which are not blank, will prompt for confirmation before deleting these sheets. I suppressed this dialogue using following code & the problem was long gone.

       $Excel->Application->{DisplayAlerts}  = 0;

      And yes, 'll take care not to put titles in CAPS in future.(:-)

[reply]
[d/l]

Back to Seekers of Perl Wisdom