Link Excel spreadsheet to Matlab

9 views (last 30 days)
Ilias Minas
Ilias Minas on 11 Oct 2022
Answered: Image Analyst on 12 Oct 2022
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);

Answers (2)

dpb
dpb on 11 Oct 2022
Edited: dpb on 11 Oct 2022
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 Comments
Ilias Minas
Ilias Minas on 12 Oct 2022
Thanks for your help. Unfortunately It didnt work. The following error appears now
Error: Object returned error code: 0x800A03EC
Any other alternative?
dpb
dpb on 12 Oct 2022
Edited: dpb on 12 Oct 2022
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...

Sign in to comment.


Image Analyst
Image Analyst on 12 Oct 2022
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.

Community Treasure Hunt

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

Start Hunting!