How do I identify which methods and properties I need to use when calling Excel from MATLAB using COM Automation?

6 views (last 30 days)
I have an Excel file that I want to update from MATLAB. The specific properties I need to modify cannot be changed using XLSWRITE.
For example, I would like to change the background color and font color of a cell in my spreadsheet, but I do not know what methods and properties to use in MATLAB in order to accomplish this.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 7 Oct 2009
To determine the Excel methods and properties you need to accomplish your task, you should consult Microsoft's Excel documentation. One good resource is the Microsoft Excel Visual Basic Reference which you can get to by selecting Help->Microsoft Excel Help in Excel. Then click on Table of Contents->Microsoft Excel Visual Basic Reference.
A simple method to get started is to record a macro in Excel and then examine the code to find out what properties are used in the Visual Basic for Application (VBA) code. You can follow these steps to identify what methods and properties to use:
1. Start Excel.
2. Select Tools->Macro->Record New Macro
3. Modify the name of the macro to mymacro and click the OK button.
4. Manually edit the Excel spreadsheet to your liking.
5. Once you are done editing the spreadsheet, select Tools->Macro->Stop Recording
6. Select Tools->Macro->Macros… Select mymacro and click the Edit button.
7. Inspect the generated Visual Basic for Applications (VBA) code. You will then be able to identify what methods and properties can be used to reproduce the events you triggered in Excel from MATLAB. For example, if you modify the font and cell color for cell A1, the following VBA code is generated:
Sub mymodule()
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 42
End With
End Sub
and here is the MATLAB code inferred from this VBA code:
xlswrite('C:\Temp\test.xls',1,'A1');
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open('C:\Temp\test.xls');
Range= Excel.Range('A1');
Range.Interior.ColorIndex = 3;
Range.Font.ColorIndex = 42;
Excel.Visible=1;
If you need to use a certain constant such as xlUnderlineStyleNone, you will need to find out what value it represents, before you can use it in MATLAB. To do this, do the following:
1. While being in the Microsoft Visual Basic Editor, press F2 or use the View menu to open the Object Browser.
2. Enter the constant, for example xlUnderlineStyleNone, in the search field on the top left of the dialog and press Enter.
3. At the bottom of the dialog, you will see a declaration such as the following:
Const xlUnderlineStyleNone = -4142 (&HFFFFEFD2)
4. You can now use this constant in MATLAB by declaring it as follows:
xlUnderlineStyleNone = -4142;

More Answers (0)

Products

Community Treasure Hunt

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

Start Hunting!