Delete selected worksheet from Excel

Hello

There will be some sheets in an Excel fie,I have to delete few perticular sheets from same excel.

for j= 1:length(idx1)        %idx1 will be having array list of Worksheet names 
    newidx = idx1(j);
    excelName1= fullfile(newPath,excelFile);       %file name and address, worksheet to be deleted from this Excel.
    try 
        Excel = actxserver('Excel.Application');
        Workbooks = Excel.Workbooks;
        Workbook = Workbooks.Open(excelName1);
        Sheets = Excel.ActiveWorkBook.Sheets;                
        current_sheet = get(Sheets, 'Item', j);                  % Not sure with this command, I want to copy the Sheet to current_sheet
        invoke(current_sheet, 'Delete')                             % tried to implement this logic from one of the Matlab Answers . I have                   
        %%%attached link below
        %current_sheet.delete                         % NOT SURE with this. 
        dbstop if caught error
    end
    Workbook.Save;
    Excel.Quit;
    Excel.delete;
end

Please let me know what changes I need to do in this code.

I referred below link.

How do I delete worksheets in my Excel file using MATLAB ? - (mathworks.com)

Thank you

Please let me know for brief

Answer 1

[dpb]

You don't seem to be following the path I laid out but reinventing the wheel and changing coding styles...

The thread <Excel Utilities Attached> has the basic class of Excel functions I referred to before from @Image Analyst. I've munged on the copy I have here significantly for a few cases, but the basics for doing stuff is there. I'd STRONGLY recommend just using something of the sort to get something working and then spend some time studying the code and in particular, the Excel VBA doc and generate some macros to study as well.

The code you've got above has the location of opening the ActiveX COM engine inside the loop -- a VERY bad idea to operate on a single workbook -- open the connection and the workbook once and do everything you're going to do first as I outlined in my sample usage code before.

Again, while you can bury ActiveX code in line, encapsulating it into functions gives flexibility and allow to get the error handling and low-level stuff tucked away out of sight of the main code, thus helping to see/design/write/debug it. Encapsulation serves a very big purpose in that regards.

IOW, I'm telling you to NOT write the functions in line but use the packaged routines to do the specific operations -- you mention wanting to copy a sheet in the comments -- that's what the function I gave you just now does, USE IT!!! instead of rewriting it in line again -- that's the other things functions do; save rewriting (and debugging) the same code over and over and over...

Answer 2

[dpb]

excelName1= fullfile(newPath,excelFile);       %file name and address, worksheet to be deleted from this Excel.
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
Workbook = Workbooks.Open(excelName1);
Sheets = Excel.ActiveWorkBook.Sheets;                
for j= 1:length(idx1)        %idx1 will be having array list of Worksheet names 
  current_sheet = get(Sheets, 'Item', j);                  % Not sure with this command, I want to copy the Sheet to current_sheet
   invoke(current_sheet, 'Delete')                             % tried to implement this logic from one of the Matlab Answers . I have                   
end
Workbook.Save
Workbook.Close
Excel.Quit;
delete Excel

The above is still wrongheaded and would delete as many sheets as are number of elements in idx1 from 1:numel(idx1) in order; you don't give any information on what idx1 actually is -- is it a sheet name or a sheet number?

If it is a number in the original workbook and they are in numerical order and numbered from the position in the original workbook, as soon as you delete the first one, then there is one less sheet in the workbook and the number of all sheets after the one you just deleted will just change by one (lower). This will then lead to not deleting the sheets you have in mind at all excepting the first one.

As said before, it will be MUCH safer and you'll have a lot less likelihood of not doing something irrecoverable (you DO have backups of any important data, don't you????!!!!) to use the utility routines and also to do all your selecting by sheet NAMES not numbers -- using a popup selector with the list of sheetnames from which to select those desired is one fairly convenient way to do that altho it can, of course, all be done programatically (with due care an thought aforehand rather than after).

The routine in @Image Analyst's class will handle all the gory details for you if you will use it as is documented therein and pass it a list of sheetnames wanted to be deleted.

I strongly suggest to use it instead of trying to reinvent the wheel.

[Santosh Biradar]

Hello @dpb

A very clear and strong points you wrote here.

As I am very new to Script and I am working with different two scripts, so I am using bit sort of same things every time. I acknowledged that using same logic with different scenarios is NOT always recommended, But the same side, the project I am working, they are always giving small and similar changes everyday, So I am getting stuck into same loop.

2 weeks before I just started to work on M-scripts.I am definitely trying NOT to do the basic mistakes.

Thank you for your recommendations

[dpb]

If you're working with Excel routinely, put @Image Analyst's class somewhere in the path you have for general utilities (I always create one named, somehwat originally, "Utils" that is second in my search path behind the working path directory. That makes it always available from anywhere happen to be working.

Then, whenever there's the need for one of these operations, utilize it instead of ad hoc code and the transition gradually will be made without needing a clean break.

[Santosh Biradar]

@dpb

Yes, I am implimenting with @Image Analyst code.

Passing the Delete funtion in my script. I changed and updated according to it, but still there are a few thinigs maybe I am missing out.

Thank you.

[Santosh Biradar]

I have a thing to ask

After calling DeleteExcelSheets(excelName1, newidx) in the script

%%Value of newidx = "AAA" i,e, Excel Sheet Name

%% Value of excelName1 = D:\SANTOSH\Task2\Aug\NewSfun_SummaryResult\ABC.xlsx

Here as I am calling Excel Details and SheetName.

so Can't I just use Sheets.Item(newidx).Delete; in this function, because I am already sorting the SheetName which is to be deleted?

function DeleteExcelSheets(excelName1, newidx)

try

Excel = actxserver('Excel.Application');

Workbooks = Excel.Workbooks;

Workbook = Workbooks.Open(excelName1);

Sheets = Excel.ActiveWorkBook.Sheets;

Sheets.Item(newidx).Delete;

end

Thank you

[dpb]

Possible, but...

Creating the ActiveX connection more than once is extremely time-consuming and is very likely to cause the system to hang if done multiple times in succession.

It is critical to arrange the logic of your code to minimize the number of times you do this -- it's easy to demonstrate this is an issue -- if you try to use the builtin writecell or other of the family in a loop addressing a cell/sheet at a time it will turn out to be extremely slow at best or simply crash and burn at worst.

Those routines are standalone using the filename on each call (as they must be) so they're doing the same thing under the hood. But, it simply doesn't work reliably to try to code in that fashion on a cell-by-cell or sheet-by-sheet basis -- "been there, done that, have the scars to show for having done." You'll also find any number of Q? on the forum about performance issues with xlswrite just as an example and postings on the File Exchange where folks have modified it to remove the creation of the COM connection object and opening/closing of the file from that routine in order to be able to write to the workbook multiple times. You've got the same kind of thing going on in your application -- you need to avoid doing that (multiple creation/open/close on same workbook) and open once at beginning, do the work, and save/close when done.

[dpb]

I just tested that

Excel_utils.DeleteExcelSheets(Excel,sheetName)

when used as documented DOES work as advertised -- I do not understand the reluctance to not simply use it; makes no sense otherwise to not.

If you must, retrieve the sheet names array from the workbook using sheetnames and look up the names using the indices if those somehow are what you have and you're certain it's the Nth sheet to delete.

[dpb]

I flagged/closed the duplicate Q? as being duplicate identically as there's a way forward here...or at least let's get a way forward in one place instead of having the duplicate.

One key problem in that particular code snippet there that prevents it from doing what you're wanting is that the workbook is never resaved after the deletion operation, even it that code is successful on the open copy.

There's the issue also that the workbook must be writeable -- not a locked copy and these routines don't check for that. When debugging such code, as also it's highly likely one will leave stranded Excel processes open that can only be killed through the Task Manager and some of those may have the given workbook locked. Be sure you don't have something like that going on, too...

And, I can't repeat often enough about not reopening new ActiveX COM sessions; when you put one of those in a function as above, then when the function that variable goes out of focus and is destroyed -- but that doesn't terminate the actxserver process itself; it's still hanging around but you've then lost the handle to it when the variable goes out of scope when the function finishes.

I hadn't thought of it before, having "just used what works" having mostly been in a rush to get something going for specific purpose, and even though it's cumbersome passing around the one handle variable from the calling function (and I don't really care for that coding paradigm, either), I just kept it as being almost inevitable.

However, you've pushed hard enough I've looked a little more in depth and I think the answer is in <actxGetRunningServer> -- it will return one (or perhaps more) handles to an existing/running/registered server if exists and an error or empty result if not. Checking this on entry to a routine and using the returned handle/function variable if exists instead will avoid creating duplicates and is also the way to not lose access when the local variable goes out of scope.

After poking around just now, to terminate a running COM server

Quit(hE)
delete(hE)
clear hE

will do so, but ONLY if there are no other variables still in scope that are/were valid object handles; anything still hanging around will be sufficient to hold that process open...that's what makes debugging so tenuous and easy to have duplicate background Excel processes hanging around -- a debugging session that crashes will clear all the variables from MATLAB workspace, but the objects themselves that had been successfully created up to that point are still in memory and then can only be killed by killing the background process itself.

Answer 3

[dpb]

OK, I'll create new Answer to move out of the previous longish comments and to reflect what I now (think) I know that I wasn't fully aware of before --

If you really want to do this this way, and are satisfied you've enough logic in place to ensure the index is the proper one (if you sort, you must be sure to sort in descending order), I'd do something more like

function DeleteExcelSheets(excelFQN,sheet)
% DeleteExcelSheets(excelFQN,sheet)
% will delete sheet as sheet name or index from Excel workbook given by
% fully-qualified filename excelFQN

try     % see if COM server already running; retrieve its handle or create if not
  Excel=actxgetGetRunningServer('Excel.Application');
catch   % not running, start it
  Excel=actxserver('Excel.Application');
end
% the above really should still have additional error checking to ensure
% success, but for brevity we'll skip for now...

% see if this workbook is already being worked on/open...
if ~isempty(Excel.ActiveWorkbook)                                       % there's a workbook already open
  if matchhes(Excel.ActiveWorkbook.FullName,excelFQN,'ignorecase',1)    % and it's the one we wanted
    Workbook=Excel.ActiveWorkbook;
  else                                                                  % gots to open it...
    Workbook=Excel.Workbooks.Open(excelName1);                
  end
else                                                                  % gots to open it...
  Workbook=Excel.Workbooks.Open(excelName1);                
end
Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Item(newidx).Delete;
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(0)  % this is problematical I believe -- 
end

Caution -- I had other stuff didn't want to mess with so the above is "air code", untested -- but I think it's an outline that should work for either names or indices -- again with the caveat on calling it repetitively with indices and changing numbers of sheets is risky.

While I show the .Close(0) operation here; that is, imo, not a wise thing to do forcefully -- there's where the overhead comes in when doing repeated calls. If you're bound and determined to not use the global handle and structure the top-level code such that it is passed around as in using the class library methods illustrated, I think then you need to implement another parameter to tell the routine whether to close the file or not -- that way in a loop you can set the flag to 'False' on all iterations until the last at which time you can then close it -- IF that's all that's happening to that file. But, again, if the very next step is some other set of operations, then it makes no sense to close the file to immediately turn around and open it again -- saving it every time is overhead enough; closing and reopening is a killer as described earlier.

If it were my application and utilities, I'd structure it to open the file at the beginning and keep it open until done, THEN save it and close the file just before exiting the app.