Link Excel spreadsheet to Matlab

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);

Answer 1

[dpb]

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.

[Ilias Minas]

Thanks for your help. Unfortunately It didnt work. The following error appears now

Error: Object returned error code: 0x800A03EC

Any other alternative?

[dpb]

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...

Answer 2

[Image Analyst]

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.