MATLAB Answers

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

1 view (last 30 days)
Fuh-Cherng
Fuh-Cherng on 4 Dec 2018
Edited: Fuh-Cherng on 5 Dec 2018
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;

  1 Comment

Bob Nbob
Bob Nbob on 5 Dec 2018
Unfortunately, I never found a good solution to this, so I would be interested to hear any real solutions.
Theoretically, since it's possible to select a chart object, initially by adding a new chart, it should be possible to select an existing chart, and data set. I don't know how the detection or organization of this works in actx though, so I don't know the actual commands necessary. But if you did find out, you could couple that with an existance check. I.e. see if a chart exists, and if it does select it for editing. If it doesn't select it and make a new one.
In the mean time though, the work around I used was to save a copy of my excel template file under a second name, and delete the file after I was done with it, so I would have a clean version each time.

Sign in to comment.

Accepted Answer

Fuh-Cherng
Fuh-Cherng on 5 Dec 2018
Edited: Fuh-Cherng on 5 Dec 2018
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

  4 Comments

Show 1 older comment
Fuh-Cherng
Fuh-Cherng on 5 Dec 2018
The 'break command is to exit the 'for chart = 1 : nCharts' loop. I do that so that, as soon as the targeted chart object is found, there is no need to continue searching on other chart objects on the same worksheet in the Excel file.
Guillaume
Guillaume on 5 Dec 2018
I'm not sure what sort of feedback is expected here. Since you seem happy with your own answer, I've only took a quick glance. The few things I've spotted:
  • There's never any reason to Activate anything and it's never a good idea to rely on the Activexxx as what is active may change without you being aware of it.
  • SeriesCollection(1) is not the way to iterate over the elements of the collection. That syntax works in VBA because it recognises it as a shortcut to the default property Item but since matlab doesn't recognise default properties, you have to call it explictly:
SeriesCollection.Item(1)
As it is you'd get an error if you'd try to access the 2nd item of the collection as matlab would recognise SeriesCollection(2) as an attempt to get the 2nd item in the (scalar) array of SeriesCollection.
Fuh-Cherng
Fuh-Cherng on 5 Dec 2018
Thank you so much, Guilaume. I will update my script accordingly.
I am very unfamiliar with Active-X. However, the task that I am working on requires me to utilize Active-X to create charts on Excel worksheet. Thus, I am looking for anything that can help me improve the script.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!