{"id":5990,"date":"2015-05-15T09:00:59","date_gmt":"2015-05-15T13:00:59","guid":{"rendered":"https:\/\/blogs.mathworks.com\/pick\/?p=5990"},"modified":"2015-05-13T20:09:00","modified_gmt":"2015-05-14T00:09:00","slug":"control-excel-using-activex","status":"publish","type":"post","link":"https:\/\/blogs.mathworks.com\/pick\/2015\/05\/15\/control-excel-using-activex\/","title":{"rendered":"Control Excel using ActiveX"},"content":{"rendered":"
\r\n

Jiro<\/a>'s pick this week is The X Collection<\/a> by Yvan Lengwiler<\/a>.\r\n <\/p>\r\n

My day job is a customer training engineer. I get to take people who have never used MATLAB or Simulink to the point where\r\n they feel comfortable in applying the tools in their day-to-day work. It's certainly a rewarding role.\r\n <\/p>\r\n

Recently during my MATLAB Fundamentals training course, I received a few questions regarding how to exchange data between\r\n MATLAB and Microsoft\u00ae Excel\u00ae. In the training, we talk about functions like xlsread<\/tt><\/a> and xlswrite<\/tt><\/a>. The person wanted the ability to control the formatting of cells, which xlswrite<\/tt> does not provide. Specifically, he wanted to change the width of a column and some of the cell colors. I pointed him to the\r\n function actxserver<\/tt><\/a>, which provides a way to connect to Windows\u00ae applications using ActiveX<\/a>. Once connected to Excel, he can use the various APIs<\/a> that are provided by Excel to customize. The reference page for actxserver<\/tt> has an example for connecting to Excel.\r\n <\/p>\r\n

I started browsing the File Exchange in hopes for finding additional examples that I could point people to for using actxserver<\/tt> with Excel. Sure enough, I found quite a few entries<\/a>, including this one<\/a> (and many others) by our very own Brett Shoelson<\/a>. I chose to highlight Yvan's X Collection, because he provides a suite of functions for performing various simple tasks within\r\n Excel, using ActiveX. He uses intuitive function names, such as XConnect<\/tt>, XOpenBook<\/tt>, and XAddSheet<\/tt>. Each of these functions take in various handles (Excel application, workbook, or worksheet) as inputs, so it's easy to understand\r\n how the functions are related to each other. In fact, if you open the files in the MATLAB Editor, you can place the cursor\r\n at one of the handle variables to see how they are used or created. The editor will highlight the same variables, so you can\r\n easily pick out where they are used.\r\n <\/p>\r\n

<\/p>\r\n

One suggestion I have for improvement is to convert this into a class, instead of a suite of functions. Naturally, these functions\r\n operate on handles that represent the Excel application, workbooks, and worksheets. Each of them can be a class, and each\r\n class can contain methods that specifically operate on the instances of the class. This will make the functions inherently\r\n invisible to everyone except to the class instances. You wouldn't have to catch errors resulting from invalid handle inputs.\r\n Of course, you may still need to catch other errors that may occur. The other reason for creating a class in this case would\r\n be to hide (encapsulate) the actual handles. The hExcel<\/tt> returned from XConnect<\/tt> is a COM.Excel_Application<\/tt>.\r\n <\/p>

hExcel = XConnect()<\/pre>
hExcel =\r\n\tCOM.Excel_Application\r\n<\/pre>
This handle exposes the user to various methods and properties provided by the Excel application.<\/p>\r\n   
 <\/p>\r\n   
This may be good or bad, but perhaps the intent is for users to use the functions provided by this suite and not the ones\r\n      that are natively available. By creating a class that maintains the handles internally as a property, it protects the users\r\n      from misusing the handle.\r\n   <\/p>\r\n   
Nonetheless, this is a great suite of functions for connecting MATLAB with Excel. I'm not doing justice to Yvan's entry if\r\n      I don't mention the true motivation for him creating this. If you need to repeatedly read from or write to an Excel file,\r\n      xlsread<\/tt> or xlswrite<\/tt> would suffer a performance hit because of the repeated connect\/disconnect that it's doing with Excel. With Yvan's XCollection,\r\n      you connect to Excel once, do the necessary read\/write operations, and disconnect once.\r\n   <\/p>\r\n   
Note that Yvan's entry does not provide the functionality the my training customer was asking for, but I pointed him to this\r\n         entry as a starting point.<\/i><\/p>\r\n   
Comments<\/b><\/p>\r\n   
Yvan provides detailed documentation of how to use the functions. Give this a try and let us know what you think here<\/a> or leave a comment<\/a> for Yvan.\r\n   <\/p>