Creating an XY Scatter plot with ActX

I'm trying to make a scatter plot of the A and B columns in an Excel Spreadsheet using ActX. However I am stuggling to use the invoke function, more specifically

invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');

I've been having trouble with the invoke function from the start and have been doing my best to avoid it, as it seems like maybe it isn't compatible with R2021a (?), but I am struggling to find any sort of documentation to help me make the XY Scatter other than a few questions posted in 2014. Is there some place where I can find the documentation that I can't seem to find myself, or some other way to create a series of data on the chart? Thanks in advance for any help you might have.

[Walter Roberson]

https://www.mathworks.com/matlabcentral/answers/36559-how-do-you-set-the-dimensions-of-an-excel-plot-using-activex-from-matlab gives code in which they succeeded in creating a scatter plot in Excel.

Answer 1

[Allen]

The below method works similar to calling invoke, but does not expressly call the invoke function. There are also a few additional options for formating charts included.

% Create activeX COM server and intialize objects
Excel = actxserver('Excel.Application');
WB = Excel.Workbooks.Open('Your Excel filename.xlsx'); % Opens the desired Excel file
Excel.WindowState = 'xlMaximized'; % Maximize the window to the screen
Excel.Visible = 1; % Set the Excel file to visible. Default is hidden.
Excel.DisplayAlerts = 0; % Turns off Excel warnings

% Create abbreviated handles. Not necessary, but helpful.
WS = WB.WorkSheet;
WC = WB.Charts;

% Adds a new chartsheet with specified name and select chartsheet
Chart = WC.Add([],WS.Item(WS.Count));
Chart.Name = 'Your Chartsheet Name';
WC.Item(Chart.Name).Select;

% Add data series. CHANGE EXCEL RANGES AS NECESSARY
NSx = Excel.ActiveChart.SeriesCollection.NewSeries;
sheet = 'your worksheet name containing data to add to scatterplot';
NSx.XValues = ['=''',sheet,'''!$A$2:$A$11']; % string representing the excel range with X-data
NSx.Values = ['=''',sheet,'''!$B$2:$B$11']; % string representing the excel range with Y-data

% Changes the chart type to a scatter plot with markers
Excel.ActiveChart.ChartType = 'xlXYScatterLines'; % Only needs to be called once if adding additional series to plot

% Additional series formatting options
NSx.Name = 'Your Series Name'; % string
NSx.Format.Line.Weight = 1.0; % Line weight
NSx.MarkerStyle = 'xlMarkerStyleCircle'; % Markers type
NSx.Format.Line.ForeColor.RGB = RGB([red,green,blue],'excel'); % Line color [0-255]
NSx.MarkerBackgroundColor = 0; % Marker Color (black)
NSx.MarkerSize = 4; % Marker Size

% Assigns descriptions for the axes and chart titles
% Setting the x-axis
XAxis = Excel.ActiveChart.Axes(1);
XAxis.HasTitle = 1;
XAxis.AxisTitle.Caption = 'Time (sec)';
XAxis.TickLabelPosition = 'xlTickLabelPositionLow';
XAxis.HasMajorGridlines = 1;
XAxis.MinimumScale = -SupLim;
XAxis.MaximumScale = SupLim;
% Setting the y-axis
YAxis = Excel.ActiveChart.Axes(2);
YAxis.HasTitle = 1;
YAxis.AxisTitle.Caption = 'Acceleration (g)';
YAxis.HasMajorGridlines = 1;
YAxis.MinimumScale = XLim(1);
YAxis.MaximumScale = XLim(2);
% Sets the chart title
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = titxt;

% Change legend position. Legend requires manual fontsize change.
Excel.ActiveChart.Legend.Position = 'xlLegendPositionTop';

% Save and Close Excel file
Excel.ActiveWorkbook.Save;
Excel.Quit;
Excel.delete;

[Robert Guldi]

I've just tried this, and I get the message:

Error using
Interface.000208D7_0000_0000_C000_000000000046/Item
Invoke Error, Dispatch Exception: Invalid index.

when trying to run the line ,

Chart = WC.Add([],WC.Item(WC.Count));

even when I copy/paste exactly what you have here. Any ideas?

[Allen]

@Robert Guldi the error you are getting is likely because there are no chartsheets in the specified workbook. That line is trying to add a new chartsheet after the last chartsheet, but will error if none are present. You can replace with the following to add a chartsheet after the last worksheet instead.

Chart = WC.Add([],WS.Item(WS.Count));

I updated my answer to use this line instead and also remove the portion that was creating a new worksheet named 'delete'. That was a left over bit from one of my scripts that I forgot to remove and is not necessary.

[Farley Postgate]

This is great, any easy way to plot 4 or 5 series against one series such as time?