MATLAB: How to determine the names and idicies of worksheets in an Excel file using ActiveX

activexexcelMATLAB

I am using ActiveX to write data to a specifically named worksheet, say 'mysheet'. First I need to check if the sheet exists (if it doesn't, I can create a new sheet and rename it to 'mysheet'). If it does, I then need to determine its index (is it the first sheet? the second? the fifth?). The code below assumes the sheet exists, with ??? assigned to the sheet index.
sfile = 'myspreadsheet.xlsx';
ssheetout = 'mysheet';
data = [1 2; 3 4];
e = actxserver('Excel.Application'); % # open Activex server
ewb = e.Workbooks.Open([pwd '/' sfile]); % # open file (enter full path!)
eSheets = ewb.Worksheets;
sheet_out_idx = ???;
eSheetOut = eSheets.get('Item', sheet_out_idx);
eActivesheetRange = eSheetOut.get('Range', 'A1:B2');
eActivesheetRange.Value = data;
Yes, I can use xlsfinfo to get a list of sheets in the file. However xlsfinfo, like its brethren xlswread and xlswrite, does not close Excel cleanly and leaves an EXCEL.EXE process open. I often use add-ins, and they won't open the next time I open Excel, even if I open a different file, as long as the stale EXCEL.EXE exists. I therefore have to open Task Manager and kill it, but that's a crap shoot if I have other workbooks open, so I have to close everything then kill the offending instance.

Best Answer

I suggest
N_Sheets=ewb.Sheets.Count;
for k=1:N_Sheets
ewb.Sheets.Item(k).Name
end
Related Question