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

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; 

[Bob Thompson]

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.

Answer 1

[Fuh-Cherng]

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

[Bob Thompson]

That seems like a reasonable way of getting it done. Unfortunately, I don't have anything more elegant than that, and searching this stuff online can be a pain.

The only comment I have would be to wonder why you use 'break' inside your if statements. It doesn't seem necessary to specifically break the if statement at those times, but maybe I'm missing something.

[Fuh-Cherng]

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]

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]

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.