How to avoid duplicating a chart object, on a specific Excel worksheet, via MATLAB?
2 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I want to create an XY plot, on a chart, on a specific worksheet in an Excel file, via MATLAB. The x and y values (for the XY plot) are already there on that specific worksheet of the Excel file. All I want to do is to create an XY plot on a chart, on the same worksheet of the Excel file. With help from MATLAB Forum a couple of weeks ago, I wrote a MATLAB script (as shown below). My script works well when I run it the first time. However, if I run my script the second time, another XY plot chart object will be created (on top of the first XY plot chart object) on the same worksheet in the Excel file. If I run my script multiple times (say a total of 5 times), I will end up with 5 identical XY plot chart objects on the same worksheet in the Excel file.
What shall I do to avoid this mistake?
Any comments and suggestions will be greatly appreciated!!!
P.S. For your review, the original Excel file (with the x and y values only, and no XY plot chart objects yet) is also attached.
% clear memory, clear Command Window
clear; clc;
% set file path and file name for an Excel file
filename = 'practice.xlsx';
fullpathToExcelFile = [pwd '\' filename];
% name of the data sheet that I want to work on. The Excel file already has a work sheet named 'DataSheet'.
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 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 sheets (i.e., get the "Sheets" object that contains all information related to sheets)
myWorkSheets = myWorkBook.Sheets;
% define active worksheet
myWorkSheet = myWorkSheets.get('Item', sheetName); % get my worksheet number
myWorkSheet.Activate; % set it as the active worksheet
% 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;
% speficy x and y values. The x values are on the cell range 'A2:A14', and the y values are on the cell range 'B2:B14' of the existing Excel file.
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection(1).Values = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
% Set X-axis and Y-axis titles.
myChartObject.Chart.Axes(1,1).HasTitle = true;
myChartObject.Chart.Axes(1,1).AxisTitle.Caption = 'Stimulus Intensity (cu)';
myChartObject.Chart.Axes(2,1).HasTitle = true;
myChartObject.Chart.Axes(2,1).AxisTitle.Caption = 'ECAP Amplitude (uV)';
% 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;
1 Commento
Bob Thompson
il 5 Dic 2018
Unfortunately, I never found a good solution to this, so I would be interested to hear any real solutions.
Theoretically, since it's possible to select a chart object, initially by adding a new chart, it should be possible to select an existing chart, and data set. I don't know how the detection or organization of this works in actx though, so I don't know the actual commands necessary. But if you did find out, you could couple that with an existance check. I.e. see if a chart exists, and if it does select it for editing. If it doesn't select it and make a new one.
In the mean time though, the work around I used was to save a copy of my excel template file under a second name, and delete the file after I was done with it, so I would have a clean version each time.
Risposta accettata
Fuh-Cherng
il 5 Dic 2018
Modificato: Fuh-Cherng
il 5 Dic 2018
4 Commenti
Guillaume
il 5 Dic 2018
I'm not sure what sort of feedback is expected here. Since you seem happy with your own answer, I've only took a quick glance. The few things I've spotted:
- There's never any reason to Activate anything and it's never a good idea to rely on the Activexxx as what is active may change without you being aware of it.
- SeriesCollection(1) is not the way to iterate over the elements of the collection. That syntax works in VBA because it recognises it as a shortcut to the default property Item but since matlab doesn't recognise default properties, you have to call it explictly:
SeriesCollection.Item(1)
As it is you'd get an error if you'd try to access the 2nd item of the collection as matlab would recognise SeriesCollection(2) as an attempt to get the 2nd item in the (scalar) array of SeriesCollection.
Più risposte (0)
Vedere anche
Categorie
Scopri di più su Use COM Objects in MATLAB in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!