How to add an XY plot and a line plot on a chart, on a specific Excel sheet, via MATLAB?

I want to create a chart on a specific sheet on Excel via MATLAB. On this chart, I want to plot some raw data (as an XY scatter plot) and a fitted curve (as a simple line plot). I wrote a script (shown below), but encountered an error message:

The 'Value' property name is ambiguous in the '0002086B_0000_0000_C000_000000000046' class.

Error in AddChartInExcel_For_MatlabForum (line 45)

myPlots.SeriesCollection(1).Value = myWorkSheet.Range('B2:B14');

What shall I do to avoid this error message?

In addition, due to my limited programming skills in MATLAB, I used the xlswrite() function to write my raw data on a specific sheet and then used Active-X to create a chart on the same sheet. In other words, I opened and closed the same Excle file multiple times. It did not seem efficient. Unfortunately, it is the only solution that I can come up with so far. I hope I know how to create a chart by using xlswrite() funciton, or how to write raw data on a specific sheet through Active-X. If so, I will not need to open and close the same Excel file multiple times. I want to make my script efficient.

Any comments will be greatly appreciated!!!

Sincerely,

Fuh

% clear memory, clear Command Window 
clear; clc;

% obtain x values, y values, and the y values of a fitted curve
x = [169.00; 170.00; 173.00; 175.00; 176.00; 177.00; 178.00; 179.00; 180.00; 185.00; 190.00; 194.00; 195.00];
y = [11.78; 17.41; 13.83; 4.61; 15.36; 14.85; 15.88; 23.56; 14.85; 33.29; 37.9; 72.74; 65.57];
fittedCurve = [12.13; 12.35; 13.29; 14.25; 14.86; 15.58; 16.42; 17.41; 18.57; 27.77; 44.95; 64.81; 70.31];

% set file path and file name for an Excel file
filename = 'practice.xls';
fullpathToExcelFile = [pwd '\' filename];

% name the data sheet that I want to work on
sheetName = 'DataSheet';  

% write headings and data onto spreadsheet
xlswrite(fullpathToExcelFile, {'x' 'y' 'fitted curve'}, sheetName, 'A1:C1');
xlswrite(fullpathToExcelFile, [x y fittedCurve], sheetName, 'A2:C14');

% --- BEGINNING of my codes, trying to create a chart on the same spreadsheet --- % 

% 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 sheet
workSheets = myWorkBook.Sheets;  
myWorkSheet = workSheets.Item(sheetName);  

% 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;  
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14'); 
myPlots.SeriesCollection(1).Value = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';

% create another object of SeriesCollection (simple line plot for a fitted curve)
line2 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection(2).Value = myWorkSheet.Range('C2:C14'); 
line2.ChartType = 'xlLine';
line2.Name = 'fitted curve';

% save and close Excel file
myWorkBook.Save();   
myWorkBook.Close;

% quit and delete this Active-X COM Excel application object, and delete this object
excelApp.Quit;
excelApp.delete; 

% --- END of my codes, trying to create a chart on the same spreadsheet --- %  

Answer 1

[Harry Vancao]

The warning that you see, "The 'Value' property name is ambiguous ..." is caused by you trying to access "Value" property of the seriesCollection. The correct property name is "Values" (plural).

myPlots.SeriesCollection(2).Values = myWorkSheet.Range('C2:C14'); 

You should be able to use ActiveX to write data to an Excel spreadsheet. Please find the following documentation for details on how to do this here

The following is a modified script that should demonstrate how to write data without using "xlswrite" and thus avoiding opening Excel several times.

% obtain x values, y values, and the y values of a fitted curve 
x = [169.00; 170.00; 173.00; 175.00; 176.00; 177.00; 178.00; 179.00; 180.00; 185.00; 190.00; 194.00; 195.00]; 
y = [11.78; 17.41; 13.83; 4.61; 15.36; 14.85; 15.88; 23.56; 14.85; 33.29; 37.9; 72.74; 65.57]; 
fittedCurve = [12.13; 12.35; 13.29; 14.25; 14.86; 15.58; 16.42; 17.41; 18.57; 27.77; 44.95; 64.81; 70.31]; 

% set file path and file name for an Excel file 
filename = 'practice.xls'; 
fullpathToExcelFile = [pwd '\' filename]; 

% name the data sheet that I want to work on 
sheetName = 'DataSheet'; 

% write headings and data onto spreadsheet 
% xlswrite(fullpathToExcelFile, {'x' 'y' 'fitted curve'}, sheetName, 'A1:C1'); 
% xlswrite(fullpathToExcelFile, [x y fittedCurve], sheetName, 'A2:C14'); 

% --- BEGINNING of my codes, trying to create a chart on the same spreadsheet --- % 

% 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.Add % open a workbooks object by using the specified filepath and filename. NOTE: Full path is required 

% define my work sheet 
workSheets = myWorkBook.Sheets; 
myWorkSheet = excelApp.Activesheet 

% add data here 
eActivesheetRange = get(myWorkSheet, 'Range', 'A1:C1'); 
eActivesheetRange.Value = {'x' 'y' 'fitted curve'}; 
eActivesheetRange = get(myWorkSheet, 'Range', 'A2:C14'); 
eActivesheetRange.Value = [x y fittedCurve]; 



% 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; 
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14'); 
myPlots.SeriesCollection(1).Values = myWorkSheet.Range('B2:B14'); 
line1.ChartType = 'xlXYScatter'; 
line1.Name = 'raw data'; 

% create another object of SeriesCollection (simple line plot for a fitted curve) 
line2 = myPlots.SeriesCollection.NewSeries; 
myPlots.SeriesCollection(2).Values = myWorkSheet.Range('C2:C14'); 
line2.ChartType = 'xlLine'; 
line2.Name = 'fitted curve'; 

% 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; 

% --- END of my codes, trying to create a chart on the same spreadsheet --- %

[Farley Postgate]

that plural caused me all kinds of debugging lol...

Thanks!

Answer 2

[Fuh-Cherng]

Thank you SO MUCH! It solved my problem perfectly!!!