Calling and computing within Excel from Matlab

Let's say I have an excel worksheet where A1 = 3; A2 = 4; and A3 = A1+A2. I want to feed a new value of A1 and A2 in from Matlab of values 5 and 6. I then want Excel to make the calculations and then I want to pull out the A3 value which will be 11. How can I make code to do that?

I've tried to ask AI for some help and it has produced the following code.

excel = actxserver('Excel.Application');
excel.Visible = false;
workbook = excel.Workbooks.Open('C:\Book1.xlsx');
sheet = workbook.Worksheets.Item(1);
sheet.Cells(1, 1).Value = 'Hello, World!';
workbook.Save;
sheet.Calculate;
value = sheet.Cells(1, 1).Value;
disp(value);
workbook.Close(false);
excel.Quit;
delete(excel);

You would think that code would put in the value "Hello World" in the first cell in the worksheet and then save after calculating. Turns out, it puts "Hello World" in every cell within the worksheet.

[Cris LaPierre]

I'd suggest searching Answers for actx or activex.

Here's one post from MathWorks Support about communicating with Excel from MATLAB using ActiveX:

https://www.mathworks.com/matlabcentral/answers/96794-how-can-i-open-an-existing-excel-file-and-communicate-with-it-using-activex?s_tid=srchtitle

Answer 1

[Cris LaPierre]

Something like this works for me

% Specify file name
path = cd;
name = "Book1.xlsx";
file = fullfile(path,name); % This must be full path name

% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks.Open(file);

excelWB_sheet_1=Workbooks.Sheets.Item(1); %Get first sheet

% Change values of cells A1 and A2
excelWB_sheet_1.Range('A1').Value = 5;
excelWB_sheet_1.Range('A2').Value = 10;

Excel.Calculate; % Force Excel to compute equations

% Extract value from cell A3 and save to a MATLAB variable
valueA3 = excelWB_sheet_1.Range('A3').Value

% Close the workbook and quit Excel application
Workbooks.Close(false); % false to not save changes
Excel.Quit;
delete(Excel); % Release the COM server

[Matt]

That did the trick. Thanks so much!

[Matt]

The Workbooks.Close(false) errors out for some reason in my script. I can't figure it out. It doesn't matter much, but does anyone know why it throws an error?

tic
data_csv = readtable('C:\Users\matt\Test3\diagnostics.csv');
toc;
% Create an Excel object
path = 'C:\Users\matt\';
name = "Fusing Algorithm.xlsx";
excelFilePath = fullfile(path,name); % This must be full path name
ExcelObj = actxserver('Excel.Application');
WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);
%     Set visibility to false (optional)
excelWB_sheet_1=WorkBooks.Sheets.Item(1); %Get first sheet
total_rows = height(data_csv);%*0+10;
clear Outputs
tic
Outputs{total_rows,3} = 0;
found_something = 1;
for j = 1:total_rows
    % rand_row = ceil(height(data_csv)*rand())*0+j; %Randomly sample results
    rand_row = j;
    for i = 1:8
        char_data{i} = string(data_csv{rand_row,2+2*i});
        conf_data{i} = data_csv{rand_row,3+2*i};
    end
    % Write the matrix to the Excel file
    matrix = [char_data; conf_data];
    excelWB_sheet_1.Range('b3:i4').Value = matrix;
    ExcelObj.Calculate; % Force Excel to compute equations
    % xlswrite(excelFilePath,matrix,1,'b3');
    % pause(.05)
    if (mod(j,1000)==0)
        fprintf('Char %d - %0.1f min on %s\n',rand_row,toc/60,datetime)
    end
    Old_prediction = data_csv.Predicted(rand_row);
    Old_confidence = data_csv.OverallConfidence(rand_row);
    % readValue = readcell(excelFilePath, 'Range','b20:b21');
    readValue = excelWB_sheet_1.Range('b20:b21').Value;
    Outputs{j,1} = str2double(string(readValue(2)));
    Outputs{j,2} = string(readValue(1));
    Outputs{j,3} = rand_row;
    if or(strcmp(Old_prediction,Outputs{j,2})==0, abs(Outputs{j,1}-Old_confidence)>.01)
        fprintf('Previous character %d was %c @ %0.2f\n',j, string(Old_prediction),Old_confidence)
        fprintf('New character %d is %c @ %0.2f\n',j, Outputs{j,2},Outputs{j,1})
        Notable_output{found_something, 1} = j;
        Notable_output{found_something, 2} = Old_prediction;
        Notable_output{found_something, 3} = Old_confidence;
        Notable_output{found_something, 4} = Outputs{j,2};
        Notable_output{found_something, 5} = Outputs{j,1};
        found_something = found_something + 1;
    end
end
WorkBooks.Close(false); % false to not save changes
ExcelObj.Quit;
delete(ExcelObj); % Release the COM server

[Walter Roberson]

What is the error message, if any?

[Cris LaPierre]

I borrowed that part from your original code. However, in the thread I linked you to in my comment, they just use the following (replace Workbook with Workbooks to work with my Answer). Decide if you want to save the changes in the Excel file or not, and adapt the code accordingly.

% Save file
invoke(Workbook,'Save')
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;

[Matt]

I'm still getting a "Unrecognized function or variable 'Workbooks'." error. The script you posted works great for me. I modified relevant portions and it is not allowing me to save or close. Not sure what I'm doing wrong.

path = 'C:\Users\matt.pittard\Documents\test\';
name = "test.xlsx";
excelFilePath = fullfile(path,name); % This must be full path name
ExcelObj = actxserver('Excel.Application');
WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);
excelWB_sheet_1=WorkBooks.Sheets.Item(1); %Get first sheet

invoke(Workbooks,'Save')
% Close Excel and clean up
invoke(ExcelObj,'Quit');
delete(ExcelObj);
clear ExcelObj;

[Walter Roberson]

WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);

You are assigning to WorkBooks with a capital B.

invoke(Workbooks,'Save')

You are using Workbooks with a lower-case b

[Matt]

Sometimes it takes a second look. :-) Thanks for the help. The original code works now too.

[Matt]

I'm writing to and reading from this Excel file almost 700,000 times. One thing I am noticing is that the execution time per iteration doubled between the first iteration and the final iteration. I re-wrote the functions to write and read from Excel to be inline with the first document posted in the responses and it sped up the time considerably, but it still increases with each execution call. Not sure what causes the issue, but it would be good to figure out.

tic
data_csv = readtable('C:\Users\matt\Test3\diagnostics.csv');
toc;
% Create an Excel object
path = 'C:\Users\matt\';
name = "Fusing Algorithm.xlsx";
Excel = actxserver('Excel.Application');
WorkBooks = Excel.Workbooks.Open(excelFilePath);
% Excel.Visible=1;
% Make the first sheet active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', sheetnum);
invoke(sheet1, 'Activate');
excelWB_sheet_2 = Excel.Activesheet;
total_rows = height(data_csv);
clear Outputs
tic
Outputs{3} = 0;
found_something = 1;
use_excel = 1;
char_data{8} = 0;
conf_data{8} = 0;
for j = 1:total_rows
    % rand_row = ceil(height(data_csv)*rand())*0+j;
    rand_row = j;
    for i = 1:8
        char_data{i} = string(data_csv{rand_row,2+2*i});
        conf_data{i} = data_csv{rand_row,3+2*i};
    end
    % Write the matrix to the Excel file
    matrix = [char_data; conf_data];
    if (mod(j,1000)==0)
        fprintf('Char %d - %0.1f min on %s\n',rand_row,toc/60,datetime)
    end
    Old_prediction = data_csv.Predicted(rand_row);
    Old_confidence = data_csv.OverallConfidence(rand_row);
    if use_excel
        ActivesheetRange = get(excelWB_sheet_2,'Range','b3:i4');
        set(ActivesheetRange, 'Value', matrix)
        readValue = get(excelWB_sheet_2,'Range','b20:b21').value;
    else
        readValue(1)=Old_prediction;
        readValue(2)=num2cell(Old_confidence);
    end
    Outputs{1} = str2double(string(readValue(2)));
    Outputs{2} = string(readValue(1));
    Outputs{3} = rand_row;
    if or(strcmp(Old_prediction,Outputs{2})==0, abs(Outputs{1}-Old_confidence)>.01)
        fprintf('Previous character %d was %c @ %0.2f\n',j, string(Old_prediction),Old_confidence)
        fprintf('New character %d is %c @ %0.2f\n',j, Outputs{2},Outputs{1})
        Notable_output{found_something, 1} = j;
        Notable_output{found_something, 2} = Old_prediction;
        Notable_output{found_something, 3} = Old_confidence;
        Notable_output{found_something, 4} = Outputs{2};
        Notable_output{found_something, 5} = Outputs{1};
        found_something = found_something + 1;
    end
end
WorkBooks.Close(false); % false to not save changes 
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;