actxGetRunningServer and Excel Dynamic range

Hi guys,

I'm usign an ExcelSheet on which historic data from broker is coming :

I use the following matlab code:

format bank;
prvClose = 13380;
% start observation date od stocastic process
prvDate = "2022-05-09";
% last date
expDate = "2022-06-17";
dayToExp  = datenum(expDate) - datenum(prvDate);
dayToExp_1 = 1;

% Attach a Running xcel object.
e= actxGetRunningServer('Excel.Application');
eWkbk = e.ActiveWorkbook;
eSheet = eWkbk.Sheets.Item(1);
dat_range = 'C7#'; %<<<<---- DYNAMIC RANGE NOTATION
dataTable = eSheet.Range(dat_range);
dataTable.Value 

When I check the loaded data I see that the dynamic range C7# is not loaded and only the first cell C7 is loaded with value 0

ans =

0

K>> 

I was expecting to see a table (or an array) with values in C7:C4316

Now as a problem solver I though .."never mind I know the number of rows so i use the full range notation, and the code was simply modified in this way :

format bank;
prvClose = 13380;
% start observation date od stocastic process
prvDate = "2022-05-09";
% last date
expDate = "2022-06-17";
dayToExp  = datenum(expDate) - datenum(prvDate);
dayToExp_1 = 1;

% Attach a Running xcel object.
e= actxGetRunningServer('Excel.Application');
eWkbk = e.ActiveWorkbook;
eSheet = eWkbk.Sheets.Item(1);
dat_range = 'C7:C4316';  %<<<<<-----FUL RANGE NOTATION
dataTable = eSheet.Range(dat_range);
dataTable.Value 

And look the answer !!:

ans =

4310×1 cell array

{[  0]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}
{[NaN]}

There is no way to read values inside a dynamic range .

May be I'm missing something, any hint ?

Massimo

Answer 1

[Massimo Salese]

I'have done some experiment and I found a strange behaviur.

In the above code I was attaching an istance of Excel running on pc but ..if i change strategy and ask to matlab to open and load a excel file using the component actxserver('Excel.Application') I'm able to acces to dynamic range:

% Create an Excel object.
e = actxserver('Excel.Application');
e.Visible = 1;
eWkbk = e.Workbooks;
eFile = eWkbk.Open('C:\Users\msalese\Documents\new_finance\Stocks\HistoricalData.xlsm');
eSheet1 = eFile.Sheets.Item('ESTX50');
eSheet1.Activate;
dat_range = 'C7#'; %<<<<<--- DYNAMIC RANGE NOTATION
rngObj = eSheet1.Range(dat_range);
dataTable = rngObj.Value

and this work giving the follwing output:

dataTable =

4316×1 cell array

{[       0]}
{[   -1.00]}
{[   -2.00]}
{[   -3.00]}
{[   -4.00]}
{[   -5.00]}
{[   -6.00]}
{[   -7.00]}

tahta i can convert in arrau in this way:

table2array(cell2table(dataTable))

I still don't understand why this happen, con someone help me ?

Thanks