On a project that I am doing for Toomre Capital Markets, I have needed to compare two versions of complicated financial model. One is written in Excel and the other in Matlab. The model uses over 150 different input variables, so it can be a challenge to make sure that essentially the same variables are passed to both models. Today, I modified one of the routines to use ActiveX to send data from Matlab to Excel. The process was not easy to figure out, but once I figured it out, it is extremely straightforward.

Initially, we were using the Matlab command xlswrite. It is a quick and easy way to write data from Matlab into Excel. It uses a COM server, and can be a little slow, especially if you are making multiple calls. However, if you want to do anything more than simple reading and writing of data to a spreadsheet from Matlab, you need to start using the ActiveX server functionality that Matlab supplies.

In our case, we wanted to have a Matlab function invoke a macro in Excel and then save the spreadsheet with the results changed as a result of the macro. This turned out to be surprisingly easy to do. First you need to start Excel as an ActiveX server.

excel = actxserver(‘Excel.Application’);

Then, you need to open the Excel file:

file = excel.Workbooks.Open(xlsfile);

In this case, we passed the Excel file name using the variable ‘xlsfile’. I’m not sure what Excel thought was the directory when I started the Excel application, so I passed a fully qualified filename.

Once the file was open, all that needed to be done was execute the macro, save the file, close the file, and then delete the ActiveX session. The commands were very straight forward:

excel.ExecuteExcel4Macro(‘!mymacro()’);
file.Save;
file.Close;
delete(excel);

It worked like a charm and helped automate some of the testing of the two different models.

While I was in there, I thought I would see if I could easily pass values from Matlab to the Excel ActiveX server.

It took a bit of experimenting until I found what seems to be the simplest and most efficient manner of doing this.

First, I created an object for the tab in the Excel file.

sheet1=excel.Worksheets.get(‘Item’, ‘Sheet1’);

Then, I could create an object for a range within the sheet

range1=get(sheet1,’Range’, ‘A1’);

With this object, I could then either get values from Excel:

a1=range1.Value;

or, I could set values in Excel

range1.Value=123;

In our case, we wanted to write values to a spreadsheet, and the spreadsheet had many tabs. So, I set up a sheet object for each table, keeping the sheet object in a struct.

Sheet.Data=excel.Worksheets.get(‘Item’, ‘Data’);
Sheet.Assumptions=excel.Worksheets.get(‘Item’, ‘Assumptions’);
Sheet.Override=excel.Worksheets.get(‘Item’,’Override’);
% etc.

Thus, when I wanted to write a value, I would execute

range=get(Sheet.Data,’Range’, ‘A1’);
range.Value=123;

This was close enough to the xlswrite command:

xlswrite(xlsfile, 123, ‘Data’, ‘A1’);

that it became easy to change the 150 or so xlswrite commands to use ActiveX.

Two other commands that I found very helpful. First is making Excel visible:

excel.Visible=1;

This is very useful when you are debugging, and can be undo by setting Visible=0. The second was to activate a sheet. For example, once I’ve created the Sheet.Data object, I can activate it, thus making the sheet in Excel the active sheet, with the command

Sheet.Data.Activate();

It is worth noting that a sheet doesn’t need to be active in order to store or retrieve data from it. Indeed, activating the sheet just adds more overhead, so I never used Activate(), except during testing.

Another thing to keep in mind. In the command above, the periods serve two different purposes. The first is indicating that Data is part of the structure Sheet, and the second is separating the object from the method it is invoking.

A few last tricks: I haven’t found a good resources explaining all that you can do with the Excel ActiveX server. However, if you want to see the methods available, once you’ve started the ActiveX server, try

excel.invoke;
% and
excel.get;

The first will give you a list of methods available and the second a list of properties available. Also, you can start other ActiveX servers the same way, e.g.

word = actxserver(‘Word.Application’);

Are you doing any interesting with ActiveX and Matlab? We’d love to hear what you are doing or your ideas on what can be done with this.

(Originally published at Toomre Capital Markets.)