MATLAB: Regarding data exchange between excel & Matlab

excelgenetic algorithmlinkMATLAB

Hello, I would like to ask a question regarding data exchange between excel & Matlab. I am using the built-in GA toolbox for optimization of a certain problem. The objective function is inserted in excel as it is exported from a third party program. The variables of the objective function are optimized in GA toolbox then send these variables to the excel to calculate the newer value of the objective function and so on. My question is how can I keep sending the values between matlab & excel automatically. I mean I would like to see the value updated in excel while matlab is running. Also, is it possible to break the loop of GA toolbox to perform some mathematical operations in excel for the objective function. Thanks in advance

Best Answer

you can try using the Spreadsheet Link EX toolbox. Basically, you can roughly use the following steps:

Send the variables of objective function from Excel to MATLAB using function MLPutMatrix
Call MLEvalString (from Excel) to invoke the MATLAB function file that optimizes these variables
Call MLGetMatrix (from Excel) to read in the resulting variables from MATLAB

In essence, you'd be using MLPutMatrix, MLEvalString and MLGetMatrix.

Example

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:

filename = 'C:\SomeExcelFile.xls';
% Open Excel Automation server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook = Excel.Workbooks.Open(filename);
% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;
% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';

The following code then saves the Workbook, quits Excel and removes the COM server:

% Save the file
Workbook.Save();
% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();
Excel.delete();
clear Excel;
clear Workbook;
clear Sheets;

MATLAB: How to use Excellink functions in a Excel Macro Function instead of a Sub

The above Macro Subroutine can be translated to the following Macro Function:

Function Calculate(A As Range, b As Range) As Variant
    MLOpen
    MLPutMatrix "A", A
    MLPutMatrix "b", b
    MLEvalString "x = myfunc(A, b)"
    MLGetVar "x", Calculate
End Function

The inputs to MLPutMatrix are Ranges. MLGetVar is used to write the "x" from the MATLAB workspace to the "Calculate" variable.

Note Excel Macro Functions only support one output range. For MATLAB functions with multiple outputs, consider writing to multiple ranges directly.

      MLGetMatrix "x", "x"
      MLGetMatrix "y", "y"

Best Answer

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

MATLAB: How to use Excellink functions in a Excel Macro Function instead of a Sub

Related Question