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 --- %
Best Answer