actxGetRunningServer and Excel Dynamic range

6 次查看（过去 30 天）
Massimo Salese
Massimo Salese 2022-6-7
回答： Massimo Salese 2022-6-9
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

请先登录，再回答此问题。

回答（1 个）

Massimo Salese
Massimo Salese 2022-6-9
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

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by