How to specify X tick intervals on an XY plot and a line plot on a chart, on a specific Excel sheet, via MATLAB?

I want to create an XY scatter plot (and a linear regression line), on a chart, on a specific worksheet on an Excel file, via MATLAB. The x and y values (for the XY scatter plot), and the y estimates (for the linear regressoin line), are already saved on that specific worksheet of the Excel file (please see attachment). All I want to do now is to create an XY scatter plot and a linear regression line on a chart, on the same worksheet of the Excel file. With help from MATLAB Forum a couple of months ago, I wrote a MATLAB script (shown below). My script worked okay, with one exception. The x tick marks were not evenly spaced. It seemed that the x tick marks were determined by the actually x values, which were not evenly spaced. This resulted in a problem ==> The linear regression line did not appear as a straight line.

Is there a way that I can specify the x tick marks, so that the x tick marks are evenly spaced (within a minimum and a maximum values on the x axis)? so that the linear regression line will appear as a straight line?

I tried "myChartObject.Chart.Axes(1).Minimum", "myChartObject.Chart.Axes(1).Maximum", "myChartObject.Chart.Axes(1).XTicks", etc. However, none of them worked.

I think the fundamental issue that I am having now is that I do not know the correct syntax of the possible commands/methods/functions/properties of the Active-X objects in MATLAB that are specific for Excel chart and plot creations. If anyone can tell me where I can find those resources, I would greatly appreciate it.

For your review, the Excel file (with the x, y, and y estimate values) is also attached.

Here is my script.

% clear memory, clear Command Window 
clear; clc;

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

% name the data sheet that I want to work on
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

% check existence of 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(150, 30, 400, 250);  

% create an object of Chart. 
myPlots = myChartObject.Chart; 
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';

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

% create another object of SeriesCollection (a simple line for linear regression)
line2 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection.Item(2).XValue = myWorkSheet.Range('A2:A14'); 
myPlots.SeriesCollection.Item(2).Values = myWorkSheet.Range('C2:C14'); 
line2.ChartType = 'xlLine';
line2.Name = 'linear regression';

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

Here is a snap shot of the chart on Excel that I have produced. (NOTE: The linear regression line does not appear as a straight line, because the x tick marks are determined by the actual x values that are not spaced evenly.)

[dpb]

https://docs.microsoft.com/en-us/office/vba/api/overview/excel

This is an Excel, not a Matlab Q? Above is VBA reference doc's; it's generally easier to go spelunking if you open the VBA Editor and use the tools therein for object exploration rather than try to just read help files.

However, I'd recommend just bring the data to Matlab and do your work there in a much easier environment instead.

[Fuh-Cherng]

Thanks a lot for the resource link. Greatly appreciated!!! I will spend some time and take a look of them.

I totally agree with you. Things would be much easier if I can simply bring the data to MATLAB and do all the work within MATLAB. The problem is that I am writing all these programming codes for my boss who is not programming-code savvy. My boss prefers that I generate results directly onto Excel files, so that she can see and access the resultant data directly from Excel. In order to make her happy and get my job done, I have to learn how to output a linear regression line on Excel, via MATLAB.

[dpb]

Record a macro in Excel and decipher it, then, maybe your simplest route.

"My boss prefers..."

There's no accounting for st.... :(

Answer 1

[Fuh-Cherng]

Thank you SO MUCH for the advice. Greatly appreciated!!!

The macro in Excel helps me A LOT. This is exactly the resource that I was looking for.

Through macro in Excel (and your advice), I have found the correct syntax and revised my code accordingly. I am now able to specify x tick intervals manually.

By the way, the actual reason that the linear regresion line did not appear as a straight line, by using my previous code, was due to a different mistake. In my previous code, I mistakenly specified the ChartType of the regression line as 'xlLine'. It should be specified as 'xlXYScatterLinesNoMarkers'.

Here is my revised code.

% clear memory, clear Command Window 
clear; clc;

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

% name the data sheet that I want to work on
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 Excel file
myWorkBook = excelApp.workbooks.Open(fullpathToExcelFile);  

% define my work sheet
workSheets = myWorkBook.Sheets;  
myWorkSheet = workSheets.Item(sheetName);  

% create an object of ChartObjects
myChartObject = myWorkSheet.ChartObjects.Add(150, 30, 400, 250);  

% create an object of Chart. 
myPlots = myChartObject.Chart; 
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';

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

% create another object of SeriesCollection (a simple line for linear regression)
line2 = myPlots.SeriesCollection.NewSeries;
line2.ChartType = 'xlXYScatterLinesNoMarkers';
line2.Name = 'linear regression';
myPlots.SeriesCollection.Item(2).XValue = myWorkSheet.Range('A2:A14'); 
myPlots.SeriesCollection.Item(2).Values = myWorkSheet.Range('C2:C14'); 

% specify x tick minimum, x tick maximum, and x major tick interval
myChartObject.Chart.Axes(1).Select;
myChartObject.Chart.Axes(1).MinimumScale = 165;
myChartObject.Chart.Axes(1).MaximumScale = 200;
myChartObject.Chart.Axes(1).MajorUnit = 5;  

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