Delete selected worksheet from Excel

16 views (last 30 days)
Santosh Biradar
Santosh Biradar on 7 Aug 2022
Answered: dpb on 8 Aug 2022
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.
Thank you
Please let me know for brief

Answers (3)

dpb
dpb on 7 Aug 2022
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...

dpb
dpb on 7 Aug 2022
Edited: dpb on 7 Aug 2022
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.
  7 Comments
dpb
dpb on 8 Aug 2022
Edited: dpb on 8 Aug 2022
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.

Sign in to comment.


dpb
dpb on 8 Aug 2022
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!