MATLAB: How to avoid duplicating a chart object, on a specific Excel worksheet, via MATLAB

active xexcelxy plot

I want to create an XY plot, on a chart, on a specific worksheet in an Excel file, via MATLAB. The x and y values (for the XY plot) are already there on that specific worksheet of the Excel file. All I want to do is to create an XY plot on a chart, on the same worksheet of the Excel file. With help from MATLAB Forum a couple of weeks ago, I wrote a MATLAB script (as shown below). My script works well when I run it the first time. However, if I run my script the second time, another XY plot chart object will be created (on top of the first XY plot chart object) on the same worksheet in the Excel file. If I run my script multiple times (say a total of 5 times), I will end up with 5 identical XY plot chart objects on the same worksheet in the Excel file.
What shall I do to avoid this mistake?
Any comments and suggestions will be greatly appreciated!!!
P.S. For your review, the original Excel file (with the x and y values only, and no XY plot chart objects yet) is also attached.
% clear memory, clear Command Window
clear; clc;
% set file path and file name for an Excel file
filename = 'practice.xlsx';
fullpathToExcelFile = [pwd '\' filename];
% name of the data sheet that I want to work on. The Excel file already has a work sheet named 'DataSheet'.
sheetName = 'DataSheet';
% create an object of Active-X COM server for Excel application
excelApp = actxserver('Excel.Application'); % create an Active-X COM server for Excel application
excelApp.DisplayAlerts = false; % turn alerts off. So we don't get the message: "The file already exists. Do you want to replace it?" all the time.
excelApp.Visible = true; % make this Excel application visible, so we can see what happens
% open an Excel file
myWorkBook = excelApp.workbooks.Open(fullpathToExcelFile); % open a workbooks object by using the specified filepath and filename. NOTE: Full path is required
% define my work sheets (i.e., get the "Sheets" object that contains all information related to sheets)
myWorkSheets = myWorkBook.Sheets;
% define active worksheet
myWorkSheet = myWorkSheets.get('Item', sheetName); % get my worksheet number
myWorkSheet.Activate; % set it as the active worksheet
% create an object of ChartObjects
myChartObject = myWorkSheet.ChartObjects.Add(100, 30, 400, 250);
% create an object of Chart.
myPlots = myChartObject.Chart;
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';
% create an object of SeriesCollection (XY plot for the raw data)
line1 = myPlots.SeriesCollection.NewSeries;
% speficy x and y values. The x values are on the cell range 'A2:A14', and the y values are on the cell range 'B2:B14' of the existing Excel file.
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection(1).Values = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
% Set X-axis and Y-axis titles.
myChartObject.Chart.Axes(1,1).HasTitle = true;
myChartObject.Chart.Axes(1,1).AxisTitle.Caption = 'Stimulus Intensity (cu)';
myChartObject.Chart.Axes(2,1).HasTitle = true;
myChartObject.Chart.Axes(2,1).AxisTitle.Caption = 'ECAP Amplitude (uV)';
% save and close Excel file
myWorkBook.SaveAs(fullpathToExcelFile);
myWorkBook.Close;
% quit and delete this Active-X COM Excel application object, and delete this object
excelApp.Quit;
excelApp.delete;

Best Answer

Hi Bob,
Thank you SO MUCH for your comments! Greatly Appreciated!!!
Inspired by your comments, I revised my script (shown below) and it seemed to work properly. I used the title of a chart to check the existence of a targeted chart object. I know this approach is not ideal, because not all charts would have their 'HasTitle' option turned on.
If there is anything that I can do to further improve this script, please let me know.
Thanks again for your comments and inspiration!
% clear memory, clear Command Window
clear; clc;
% set file path and file name for an Excel file
filename = 'practice.xlsx';
fullpathToExcelFile = [pwd '\' filename];
% name of the data sheet that I want to work on. The Excel file already has a work sheet named 'DataSheet'.
sheetName = 'DataSheet';
% title of the chart that I want to work on. This chart may, or may not, already exist in the targeted worksheet of the Excel file.
myChartTitle = 'ECAP Growth Function';
% create an object of Active-X COM server for Excel application
excelApp = actxserver('Excel.Application'); % create an Active-X COM server for Excel application
excelApp.DisplayAlerts = false; % turn alerts off. So we don't get the message: "The file already exists. Do you want to replace it?" all the time.
excelApp.Visible = true; % make this Excel application visible, so we can see what happens
% open an Excel file
myWorkBook = excelApp.workbooks.Open(fullpathToExcelFile); % open a workbooks object by using the specified filepath and filename. NOTE: Full path is required
% define my work sheets (i.e., get the "Sheets" object that contains all information related to sheets)
myWorkSheets = myWorkBook.Sheets;
% define active worksheet
myActiveWorkSheet = myWorkSheets.get('Item', sheetName); % get a handle of my active worksheet
myActiveWorkSheet.Activate; % set it as the active worksheet
% check existence of a targeted chart object
nCharts = myActiveWorkSheet.ChartObjects.count;
if nCharts >= 1
for chart = 1 : nCharts
currentChartObject = myActiveWorkSheet.ChartObjects.Item(chart);
currentChartTitle = get(currentChartObject.Chart.ChartTitle, 'Text');
if strcmpi(currentChartTitle, myChartTitle) == true
% get handles for the chart object, the plots object, and the line object, that already exist on the active worksheet in the Excel file
myChartObject = currentChartObject;
myPlots = myChartObject.Chart;
line1 = myPlots.SeriesCollection.Item(1);
% update XY data for line1


updateXYPlot(myActiveWorkSheet, myPlots, line1);
break;
else
if chart == nCharts
% create a new chart, its associated plots object, and line1 object

[myChartObject, myPlots, line1] = createMyNewChart(myActiveWorkSheet, myChartTitle);
% update XY data for line1
updateXYPlot(myActiveWorkSheet, myPlots, line1);
break;
end
end % if strcmpi(currentChartTitle, myChartTitle) == true
end % for chart = 1 : nCharts
else
% create a new chart, its associated plots object, and line1 object
[myChartObject, myPlots, line1] = createMyNewChart(myActiveWorkSheet, myChartTitle);
% update XY data for line1
updateXYPlot(myActiveWorkSheet, myPlots, line1);
end % if nCharts >= 1
% save and close Excel file
myWorkBook.SaveAs(fullpathToExcelFile);
myWorkBook.Close;
% quit and delete this Active-X COM Excel application object, and delete this object
excelApp.Quit;
excelApp.delete;
function [myChartObject, myPlots, line1] = createMyNewChart(myActiveWorkSheet, myChartTitle)
% create an object of ChartObjects
myChartObject = myActiveWorkSheet.ChartObjects.Add(100, 30, 400, 250);
% create an object of Chart.
myPlots = myChartObject.Chart;
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = myChartTitle;
% Set X-axis and Y-axis titles.
myChartObject.Chart.Axes(1).HasTitle = true; % X axis title
myChartObject.Chart.Axes(1).AxisTitle.Text = 'Stimulus Intensity (cu)';
myChartObject.Chart.Axes(2).HasTitle = true; % Y axis title
myChartObject.Chart.Axes(2).AxisTitle.Text = 'ECAP Amplitude (uV)';
% create an object of SeriesCollection (XY plot for the raw data)
line1 = myPlots.SeriesCollection.NewSeries;
end
function updateXYPlot(myActiveWorkSheet, myPlots, line1)
% speficy x and y values. The x values are on the cell range 'A2:A14', and the y values are on the cell range 'B2:B14' of the existing Excel file.
myPlots.SeriesCollection(1).XValue = myActiveWorkSheet.Range('A2:A14');
myPlots.SeriesCollection(1).Values = myActiveWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
end