How can I open an existing Excel file and communicate with it using ActiveX?

I would like an example where ActiveX is used to open an existing spreadsheet to perform read and write operations. I would like to manipulate the data in Excel and then retrieve such changes in MATLAB without having to resave the spreadsheet.

Answer 1

[MathWorks Support Team]

This bug has been fixed in Release 14 Service Pack 3 (R14SP3). For previous product releases, read below for any possible workarounds:

The following provides an example of opening an existing Excel file named example.xls and proceeding to write and read with it:

% Specify file name

file = 'D:\Applications\MATLAB701\work\example.xls'; % This must be full path name

% Open Excel Automation server

Excel = actxserver('Excel.Application');

Workbooks = Excel.Workbooks;

% Make Excel visible

Excel.Visible=1;

% Open Excel file

Workbook=Workbooks.Open(file);

% Specify sheet number, data, and range to write to 

sheetnum=1;

data=rand(4);  % use a cell array if you want both numeric and text data

range = 'F10:I13';

% Make the first sheet active

Sheets = Excel.ActiveWorkBook.Sheets;

sheet1 = get(Sheets, 'Item', sheetnum);

invoke(sheet1, 'Activate');

Activesheet = Excel.Activesheet;

% Put MATLAB data into Excel

ActivesheetRange = get(Activesheet,'Range',range);

set(ActivesheetRange, 'Value', data);

% Here you might manipulate the data in Excel

% Now read the data from the sheet; you could specify a new range here

Range = get(Activesheet,'Range',range);

out = Range.value;

% Save file

invoke(Workbook,'Save')

% Close Excel and clean up

invoke(Excel,'Quit');

delete(Excel);

clear Excel;

Answer 2

[Pruthvi G]

Download Link :: https://in.mathworks.com/matlabcentral/fileexchange/74993-get-sheet-names-from-excel-file

%%********************************************************************************
%   Name          : xl_xlsfinfo
%   Author        : Pruthvi Raj G
%   Version       : Version 1.0 - 2011b Compactible
%   Description   : Finds all the sheets in the Excel file (.xls,.xlsm,.xlsx)
%   Input         : File_Name with path included.
%   Date          : 11-Feb-2020
%
%   Examples      : xl_xlsfinfo('D:\Pruthvi\Test_file.xls')
%*********************************************************************************

Use the Below Lines of Code ::

sheets = xl_xlsfinfo('D:\Pruthvi\Test_file.xls')

 sheets =
  1×5 cell array
    {'Sheet1'}    {'Sheet2'}    {'Sheet3'}    {'Sample'}    {'Data'}