MATLAB Answers

How do I determine the names and idicies of worksheets in an Excel file using ActiveX?

64 views (last 30 days)
James on 14 Sep 2018
Answered: Pruthvi G on 13 Apr 2020
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.

More Answers (1)

Pruthvi G
Pruthvi G on 13 Apr 2020
% Name : xl_xlsfinfo
% Author : Pruthvi Raj G
% Version : Version 1.0 - 2011b Compactible
% Description : Finds all the sheets in the Excel file (.xls,.xlsm,.xlsx)
% Input : File_Name with path included.
% Date : 11-Feb-2020
% Examples : xl_xlsfinfo('D:\Pruthvi\Test_file.xls')
Use the Below Lines of Code ::
sheets = xl_xlsfinfo('D:\Pruthvi\Test_file.xls')
sheets =
1×5 cell array
{'Sheet1'} {'Sheet2'} {'Sheet3'} {'Sample'} {'Data'}




Community Treasure Hunt

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

Start Hunting!