Creating an XY Scatter plot with ActX
10 views (last 30 days)
Show older comments
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.
1 Comment
Walter Roberson
on 29 May 2021
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.
Answers (1)
Allen
on 30 May 2021
Edited: Allen
on 1 Jun 2021
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;
3 Comments
Allen
on 1 Jun 2021
Edited: Allen
on 1 Jun 2021
@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
on 9 Sep 2022
This is great, any easy way to plot 4 or 5 series against one series such as time?
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!