Using Cells() in VBA with Excel via ActiveX

While off topic, there are enough using Excel and ActiveX that it is a pretty active subject area, so I'll add one more I couldn't solve syntax for --

The Range object Cells addressing mode in VBA allows one to address ranges by numeric calculations and pass row, column instead of a range expression. This is, as the documentation notes, extremely handy when need to calculate addresses to avoid string manipulations to build those address expressions.

So, can anybody figure out how to implement the example code with ActiveX in MATLAB?

Sub SetUpTable() 
Worksheets("Sheet1").Activate 
For TheYear = 1 To 5 
    Cells(1, TheYear + 1).Value = 1990 + TheYear 
Next TheYear 
For TheQuarter = 1 To 4 
    Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter 
Next TheQuarter 
End Sub

I've had no success, all my tries have boiled down to something like

K>> Excel.ActiveSheet.Cells(2,2).Address
Index in position 1 exceeds array bounds (must not exceed 1). 
K>> Excel.ActiveSheet.Cells(2).Address
Index exceeds the number of array elements (1). 
K>> Excel.ActiveSheet.Cells(1).Address
ans =
    '$1:$1048576'
K>> Excel.ActiveSheet.Excel.Cells(1).Address
K>>

where cannot get row,column addressing offsets to be used.

Would be a useful adjunct to @ImageAnalyst's Excel_Utils class...I have gotten the Find method to work to return a cell location of interest and then wanted/needed to reference locations relative to it that raised the issue.

Answer 1

[Harimurali]

Hi dpb,

I understand that you have used the “Cells” function in Excel VBA macro and want to implement the same logic in MATLAB using ActiveX.

Please follow these steps to implement the example code you have attached to the question in MATLAB:

• Run the Excel application in an Automation server process using the“actxserver”function and the program ID,“excel.application”.

exl = actxserver('Excel.Application');

• The“exl”object provides access to several interfaces supported by the Excel program. Use the“Workbooks”interface to open the Excel file containing the data.

exlWkbk = exl.Workbooks;

exlFile = exlWkbk.Open('path\to\required\exlfile.xlsx');

• Create a new sheet and make it active.

newSheet = exlFile.Sheets.Add();

newSheet.Activate();

• Using the “Range” object interface, select a range of cells and initialize it with zeros.

arr = zeros([5,6]);

range = get(newSheet,'Range','A1:F5');

set(range, 'Value', arr);

• Using the “UsedRange” object interface, index the cells in the range initially selected by specifying the row and column of a particular cell.

for theYear = 1:5

newSheet.UsedRange.Value{1, theYear + 1} = 1990 + theYear;

end

for theQtr = 1:4

newSheet.UsedRange.Value{theQtr + 1, 1} = "Q" + theQtr;

end

newSheet.UsedRange.Value{1, 1} = '';

for i = 2:5

for j = 2:6

newSheet.UsedRange.Value{i, j} = '';

end

end

• After inserting the required values into the sheet, save the changes and quit the automation server.

exlFile.Save();

exlFile.Close();

exl.Quit();

exl.delete();

Please refer the following links for more information on how to use Active automation interface to connect MATLAB to Excel:

• https://www.mathworks.com/matlabcentral/answers/94822-are-there-any-examples-that-show-how-to-use-the-activex-automation-interface-to-connect-matlab-to-ex
• https://www.mathworks.com/help/releases/R2021b/matlab/matlab_external/example-reading-excel-spreadsheet-data.html

Hope this helps.