Link Excel spreadsheet to Matlab
1 次查看(过去 30 天)
显示 更早的评论
Hi everyone,
I want to link an Excel spreadsheet with Matlab.
I use the following piece of commands
When it comes to the last line the following error appears:
'Dot indexing is not supported for variables of this type.'
Can somehow replace the last command and make it work? I am using 2021b Version of Matlab.
Thank you very much
ex = actxGetRunningServer('Excel.Application');
book = ex.ActiveWorkbook;
sheet = book.Worksheets.Item(1);
0 个评论
回答(2 个)
dpb
2022-10-11
编辑:dpb
2022-10-11
sheet=get(ex.sheets,'Item',1);
should work.
There's a whole litany of what will/won't actually get resolved through the COM engine by direct translation from the Excel object model; I've yet to figure out how to tell other than trial and error. If it'll work, tab completion will uncover what there's access to in an active connection; other than that, it's trial and error afaict.
There are essentially limitless possibilities of ways to code COM in returning handles with access to the various objects from which one has access to the components/methods/properties thereof. If return the 'Sheets' collection object, then I think I recall you can refer to its members with dot nomenclature.
2 个评论
dpb
2022-10-12
编辑:dpb
2022-10-12
Can't debug COM remotely; something appears wrong before you get here, then...there's nothing in the code snippet provided that checks that there is an open workbook...
The line I posted works and was taken directly from the following routine which works reliably after opening a workbook just changing the variable names to match yours above...
% Tries to Activate the sheet. Can pass in the number of the sheet (1,2,3,etc.) or name ('Results').
function ActivateSheet(Excel, sheetNameOrNumber)
try
worksheets = Excel.sheets;
numSheets = worksheets.Count;
if isnumeric(sheetNameOrNumber)
thisSheet = get(worksheets, 'Item', sheetNameOrNumber);
thisSheet.Activate;
else
% Loop over all sheets looking for sheetname.
for currentSheet = 1 : numSheets
thisSheet = get(worksheets, 'Item', currentSheet);
thisSheetName = strtrim(thisSheet.Name);
if strcmpi(thisSheetName, sheetNameOrNumber)
% Found the sheet we were looking for. Activate it.
thisSheet.Activate;
break; % No need to keep looking.
end
end
end
catch ME
errorMessage = sprintf('Error in function ActivateSheet.\n\nError Message:\n%s', ME.message);
fprintf('%s\n', errorMessage);
WarnUser(errorMessage);
end
return; % from ActivateSheet
end % of the ActivateSheet() method.
The above is from the set of Excel utilities previously posted here by either @Image Analyst or @Star Strider, I forget which; all goes to them for providing the base code.
The above is used something like..
Excel = actxserver('excel.application')
excelFullFileName=fullfile(cd,'Data.xlsx')
workbook = Excel.Workbooks.Open(excelFullFileName)
workbook.Worksheets
ans.Item(1).Name
Excel.ActiveWorkbook.Close(0)
Excel.ActiveWorkbook
Excel.Quit
delete(Excel)
which was extracted from local commandhistory of a debugging session while I was testing a new routine to rename a worksheet.
NB: the line
workbook = Excel.Workbooks.Open(excelFullFileName)
is MANDATORY to have an active workbook object to play with first.
There well may be more efficient routes to the end (in fact, I'm sure there are in many cases), but I've not taken the time to try to do so for the most part; without the facilities of something like the VBA builtin editor, figuring out the translation from VBA code to working COM syntax is something akin to black magic; about the only tool there is in the MATLAB environment is that there is tab completion on an active object at the command line while debugging code, but that's about it.
I've not been able to find a text file from which to be able to even look up the error message text from the error code...
Image Analyst
2022-10-12
Excel is probably not already running so you should not use actxGetRunningServer but use actxserver instead. What I do is
% Get the Excel server object.
try
% If it's running, get the server object.
Excel = actxGetRunningServer('Excel.Application');
catch
% If it's not running, the above will throw an error and we'll have to open a server object from scratch.
Excel = actxserver('Excel.Application');
end
excelWorkbook = Excel.workbooks.Open(excelFullFileName);
Excel.visible = true; % Make Excel appear so we can see it, otherwise it is hidden.
See attached utility class.
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!