MATLAB: Matlab to excel and back! Q about using ActiveX

activexexcel

Can anyone direct me to a website or a manual or a tutorial that will help explain how to use ActiveX to control how data are displayed in an excel file (e.g. number format, font size, etc)? And that shows simple examples. Yes, I've read many previous such quierries and the suggestions to read the MatLab documentation. But frankly, I find those exremely confusing and not helpful for someone at my relatively beginning stage. The documentation often uses jargon and unfamiliar terms.

I've generated a Table of data with a MatLab script and have written this to an excel file using xlswrite and writetable. That works fine!

Now, however, I want to be able to change the number formats and fonts in some of the excel rows.

Any ideas where best to learn how to manipulate excel files using MatLab and ActiveX?

Best Answer

I'm unaware of anybody having written the specific tutorial/book you're looking for re: using COM for Excel with MATLAB...don't disagree there's probably an audience for having the pieces distilled and the particular peculiarities wrt MATLAB in conjunction with.

As Fanguin Jiang notes, it's really the VBA syntax/reference that is the Rosetta stone -- "all" COM is is another way to execute the VBA methods without using VBA itself -- what gets lost is the convenience of VBA interpreter/compiler that understands its syntax and data types and the builtin tab completion in the VBA editor for argument lists, etc, so you have to do the translation from that description of the desired function to the allowable COM syntax making allowances for MATLAB variable types, etc, along the way.

I complained about some troubles I was having here <Answers/533148-yet-another-excel-com-problem> and Image Analyst was kind enough to post his Excel utilities class that is undoubtedly the most complete and well-documented example I've seen as well as probably having 80-90% of what you're after already done.

Related Solutions

MATLAB: Can MATLAB pre-format individual cells when writing data to an EXCEL spreadsheet

Yes you can. See this method from my Excel class:

%-------------------------------------------------------------------------------------------------------
  % Selects all cells in the current worksheet in the specified range.
  % Horizontally aligns all the specified cell range.
  % horizAlign = 1 for left alignment.
  % horizAlign = 3 for center alignment.
  % horizAlign = 4 for right alignment.
  % Leaves with cell A1 selected.
  function AlignCells(Excel, cellReference, horizAlign, autoFit)
    try
      % Select the range
      Excel.Range(cellReference).Select;
      % Align the cell contents.
      Excel.Selection.HorizontalAlignment = horizAlign;
      Excel.Selection.VerticalAlignment = 2;
      if autoFit
        % Auto fit all the columns.
        Excel.Cells.EntireColumn.AutoFit;
      end
      % Put "cursor" or active cell at A1, the upper left cell.
      Excel.Range('A1').Select;
    catch ME
      errorMessage = sprintf('Error in function AlignCells.\nError Message:\n%s', ME.message);
      fprintf('%s\n', errorMessage);
      WarnUser(errorMessage);
    end % from AlignCells
    return;
  end

MATLAB: Append new rows in the middle of an excel file’s sheet

Probably the easiest way to find out the code you need to use is to record a macro in excel while you do the insertion and fix the formatting (which excel will inevitably mess up). The code you then use in matlab will be more or less identical to the VBA code generated by the macro (with adjustments for the difference in language syntax).

Best Answer

Related Solutions

MATLAB: Can MATLAB pre-format individual cells when writing data to an EXCEL spreadsheet

MATLAB: Append new rows in the middle of an excel file’s sheet

Related Question