How do I determine the names and idicies of worksheets in an Excel file using ActiveX?
18 ビュー (過去 30 日間)
古いコメントを表示
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.
0 件のコメント
採用された回答
Fangjun Jiang
2018 年 9 月 14 日
I suggest
N_Sheets=ewb.Sheets.Count;
for k=1:N_Sheets
ewb.Sheets.Item(k).Name
end
その他の回答 (1 件)
Pruthvi G
2020 年 4 月 13 日
Download Link :: https://in.mathworks.com/matlabcentral/fileexchange/74993-get-sheet-names-from-excel-file
%%********************************************************************************
% 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'}
0 件のコメント
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!