Use MATLAB to execute Excel What-If Goal Seek Analysis - Stack Overflow

admin管理员组

文章数量:1122796

I have a MATLAB code that is copying data to an Excel file and running hundreds of formulas (for anyone interested, I am using a revised version of CREST developed by NREL). Afterwards, I want to run a What-If Analysis using the goal seek ability in Excel to find the minimum cost of the product.

Because of the complexity of the spreadsheet, I cannot recreate the formulas in MATLAB (in a timely fashion). The What-If Analysis works manually, but I would like to run goal seek for thousands of files. Is there a way to use MATLAB to execute this? I would like to open the spreadsheet, run the goal seek, save, and close the file and do this for thousands of files. I know how to do everything other than execute the goal seek.

Thank you!

I have a MATLAB code that is copying data to an Excel file and running hundreds of formulas (for anyone interested, I am using a revised version of CREST developed by NREL). Afterwards, I want to run a What-If Analysis using the goal seek ability in Excel to find the minimum cost of the product.

Because of the complexity of the spreadsheet, I cannot recreate the formulas in MATLAB (in a timely fashion). The What-If Analysis works manually, but I would like to run goal seek for thousands of files. Is there a way to use MATLAB to execute this? I would like to open the spreadsheet, run the goal seek, save, and close the file and do this for thousands of files. I know how to do everything other than execute the goal seek.

Thank you!

• excel
• matlab

Share Improve this question Follow asked Nov 21, 2024 at 22:23 Janel NiskaJanel Niska 2122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The MSDN VBA documentation is usually a good reference for how to programmatically control Excel from MATLAB, since the syntax with the COM interface for Excel is closely aligned with VBA.

In particular, from the GoalSeek VBA docs:

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa195749(v=office.11)

The VBA code would be something like:

Worksheets("Sheet1").Range("C3").GoalSeek _
    Goal:=15, _
    ChangingCell:=Worksheets("Sheet1").Range("B3")

We can do this in MATLAB as follows:

workbookName = 'Test.xlsx';
sheetName = 'Sheet1';
goalseekAddr = 'C3';
goalseekVal = 15;
changeAddr = 'B3';

sheet = getSheet( workbookName, sheetName );
changeCell = sheet.Range(changeAddr);
invoke( sheet.Range(goalseekAddr), 'GoalSeek', goalseekVal, changeCell );

You have to use invoke to call the GoalSeek function, but otherwise it's operating on the same inputs

• It's a function of the range you wish to seek on
• The first input argument is the value to seek for
• The second input is the cell to change

getSheet is a helper function I've written, but you might already have something which gets the sheet COM object via actxserver

---

Helper function

function sheet = getSheet( workbookName, sheetName )
    % Try and connect to open Excel instance, open a new one if not
    try
        Excel = actxGetRunningServer('Excel.Application');
    catch
        Excel = actxserver('Excel.Application');
    end
    % Find the workbook by name
    workbook = [];
    for iwb = 1:Excel.Workbooks.Count
        if strcmpi( workbookName, Excel.Workbooks.Item(iwb).Name )
            workbook = Excel.Workbooks.Item(iwb);
            break;
        end
    end
    if isempty(workbook)
        Excel.Workbooks.Open( workbookName );      
    end
    % Assume the sheet exists...
    sheet = workbook.Sheets.Item(sheetName);
end

本文标签： Use MATLAB to execute Excel WhatIf Goal Seek AnalysisStack Overflow