Is it possible to display the progress of FMINCON within Excel when calling FMINCON from Spreadsheet Link Ex?

2 views (last 30 days)
I am running FMINCON from an Excel spreadsheet via Spreadsheet Link Ex.
I would like to know how to send information to Excel about the progress of FMINCON.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 27 Jun 2009
Excel Link works sequentially in the sense that, once it's made a request to MATLAB to run commands, it simply waits until those commands have finished. So there is no way to grab the progress from the Excel side.
One workaround may be to push the data from the MATLAB side.
From within MATLAB, you can control Excel as an ActiveX COM server. This means that MATLAB can use Excel's API and manipulate Excel to update and display data. Furthermore, all of this can be done within the OutputFcn, which you can create for FMINCON.
For more information on the OutputFcn, please see the documentation for FMINCON and OPTIMSET.
To illustrate how to connect to Excel in the OutputFcn, below is an example that sends some iteration information to an Excel Spreadsheet named "Book1" with "Sheet1".
To run the example, open Excel with a blank worksheet. Then copy the following code into a MATLAB function:
function stop = myOutputFcn(x, optimValues, state)
stop = false;
%%open Excel connection
persistent e
if isempty(e)
e = actxGetRunningServer('excel.application');
end
%%retrieve data
eWkbk = e.Workbooks;
eFile = eWkbk.Item('Book1'); %get handle to currently open 'Book1' file
eSheet1 = eFile.Sheets.Item('Sheet1'); %get handle to 'Sheet1'
rangeObj = eSheet1.Range('A1:B3'); %with this range object you can change its properties
for ii = 1:3
rangeObj.Value = {'iteration' ,optimValues.iteration ;
'step size' ,optimValues.stepsize ;
'solver state',state }; %update values
end
To run an optimization and have the state of the optimization updated in Excel, perform the following at the MATLAB command prompt:
options = optimset('OutputFcn',@myOutputFcn)
x = fmincon(@(x) (x+3)^2,2,1,10,[],[],[],[],[],options)
This will allow you to push data into Excel, but you do have to be careful not to overwrite any vital cells in Excel!

More Answers (0)

Categories

Find more on Data Export to MATLAB in Help Center and File Exchange

Products


Release

R2009a

Community Treasure Hunt

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

Start Hunting!