MATLAB: How to programmatically set the elements of a chart object in Excel 2007 from MATLAB 7.8 (R2009a)

activexactxservercomexcelMATLABplot

I have an Excel file which has numerous sheets and each sheet has a chart object that is created in Excel. I wanted an example where MATLAB communicates with these Excel sheets and make changes to the title of the chart object in Excel sheet.

Best Answer

To access a chart object in Excel 2007 from MATLAB 7.8 (R2009a),

1) Start Excel as an ACTXSERVER application in MATLAB.

2) Open the Excel workbook.

3) Open the worksheet that contains the chart.

4) Get the Chart object to modify.

5) Implement the code that modifies the elements of the existing chart.

The following code provides an example on how to achieve this.

excel = actxserver('excel.application')
wkbk = excel.Workbooks.Open('Book1.xlsx') % open the excel file, full path need to be mentioned or else excel will pick it from most recently opened files.
wksheet = wkbk.Worksheets.Item('Sheet1') % Choose desired sheet
co = wksheet.ChartObjects.Item(1) % select the plot (this selects the first plot
co.Chart.ChartTitle.Text ='myplot1'  % Change the title
wkbk.Save % save the changes
excel.Quit % close excel

Also, see the link below for the way to do it using Microsoft Visual Studio with C# and Visual Basic:

 <http://msdn.microsoft.com/en-us/library/bb404904.aspx>

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 create chart objects in Excel from MATLAB 7.8 (R2008a)

To be able to access or create chart objects in excel sheets, you have to communicate with Excel from MATLAB using COM. To do this, you will have to create an Automation server in MATLAB using the actxserver command and use COM API to talk to Excel. Once you establish this connection, you can use commands similar to the excel VBA editor to programmatically add or edit objects in excel sheet.

Here is an example on how you could access data in excel sheet and draw chart objects. The chart Book.xlsx that contains the data is attached.

excel = actxserver('excel.application');
excel.Visible = 1;
wkbk = excel.Workbooks.Open('<FullPath>\Book.xlsx');
wkbk.ActiveSheet;
wkbk.ActiveSheet.Shapes.AddChart.Select;
wkbk.ActiveChart.SetSourceData(wkbk.ActiveSheet.get('Range','A2:B4'))
wkbk.ActiveChart.ChartType = 'xlBarClustered';
wkbk.SaveAs('<FullPath>\Book2.xlsx'); % save the changes
wkbk.Close;
excel.Quit; % close excel 
excel.delete;

The above API commands is provided by Microsoft Excel and for more specific information on these, refer to the Microsoft Excel's documentation page.

Best Answer

Related Solutions

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

MATLAB: How to create chart objects in Excel from MATLAB 7.8 (R2008a)

Related Question