How can I use the .NET interface to connect to an existing Excel application?

The documentation page "Work with Microsoft Excel Spreadsheets Using .NET" gives an example of how to use .NET to start a new instance of an Excel application, but how can I connect to an existing Excel application? I am looking for something with a similar functionality to the COM function "actxGetRunningServer".

Answer 1

[MathWorks Support Team]

You can do this with "System.Runtime.InteropServices.Marshal.GetActiveObject". The following example connects to the workbook "myData", reads and writes some data to it, formats some cells, then closes the workbook and quits Excel.

First, add the necessary assemblies. .NET Framework is required, as "GetActiveObject" is only available in .NET Framework. 

dotnetenv('framework');

NET.addAssembly('microsoft.office.interop.excel');
NET.addAssembly('System.Runtime.InteropServices');

Connect to the running instance of Excel. Note that if there is more than one Excel application open (which will show up as separate processes in Task Manager), there is no way for MATLAB to control which instance is connected to.

excelApp = System.Runtime.InteropServices.Marshal.GetActiveObject('Excel.Application');

Since the Excel .NET interface is a wrapper around the COM interface, "excelApp" is a COM object and must be explicitly cast to a "Microsoft.Office.Interop.Excel.ApplicationClass" type.

app = Microsoft.Office.Interop.Excel.ApplicationClass(excelApp);

If there is more than one workbook open in Excel (which will show up as sub-processes associated with the main Excel process in Task Manager), we need to iterate through "Workbooks.Item" to find the workbook that we want. In this case, we search through the open workbooks for the workbook "myData".

books = app.Workbooks;
found = false;
name = "myData";
for i = 1:books.Count
    [~, bookName, ~] = fileparts(books.Item(i).Name);
    if bookName == name
        wrkbook = books.Item(i);
        found = true;
        break
    end
end

Once we have found the workbook, we can interact with it like how we would with any other .NET interface object. Note that "comWrksheet" must be cast to "Microsoft.Office.Interop.Excel.Worksheet", as it is a COM object because "sheets.Item" can return objects of different types.

if found
    comWrksheet = wrkbook.Worksheets.Item(1);
    wrksheet = Microsoft.Office.Interop.Excel.Worksheet(comWrksheet);
    
    % Get data in cells B2:F6
    range = wrksheet.Range('B2:F6');
    data = cell2mat(cell(range.Value2));
    
    % Rotate the matrix by 90 degrees clockwise
    rotatedData = rot90(data, -1);
    
    % Write rotated data back to Excel
    outputRange = wrksheet.Range('G2:K6');
    outputRange.Value2 = rotatedData;
    
    % Set Interior to yellow
    outputRange.Interior.Color = 65535;
    
    % Save
    Save(wrkbook)

    % Close the workbook
    Close(wrkbook)
    
    % Close connections
    clear wrkbook range outputRange range comWrksheet wrksheet
else
    fprintf("Could not find an open workbook %s.\n", name)
end

Close Excel and close all connections. If you ever want to reconnect to a COM object, make sure you clear all variables from a previous connection.

Quit(app)
clear app excelApp books