- Determine the chart position in terms of rows A VBA chart Object has properties TopLeftCell and BottomRightCell which return the cells in the upper left and lower right corners of the chart, respectively. For example, following VBA code is used to retrieve the row property of cells pertaining to the chart position:
- Determine the cell position in terms of points We can use the Left and Top properties to determine the distances (of a cell, row or column) to the left and top boundaries of the spreadsheet, in the unit of points. Here is an example using VBA code
Dim TopRow As Long
Dim BottomRow As Long
With ActiveSheet.ChartObjects(1)
TopRow = .TopLeftCell.Row
BottomRow = .BottomRightCell.Row
End With
Worksheets("Sheet1").Range("A5").Top
Worksheets("Sheet1").Range("B7").Left
Worksheets("Sheet1").Columns(6).Left
Worksheets("Sheet1").Rows(11).Top
Following Matlab code shows how to insert 10 charts into an excel spreadsheet, each having a figure number printed in a cell beside it:
ObjExcel = actxserver('Excel.Application');
TheWorkbook = ObjExcel.Workbooks.Open('example.xls');
DataSheet = TheWorkbook.Sheets.Item('Data');
RowLastChartBottom = 0;
for i = 1 : 10
RowChartTitle = RowLastChartBottom + 2;
DataSheet.Range('A', num2str(RowChartTitle)).Value = ['Chart ', num2str(i)];
%---
RowChartTop = RowChartTitle + 1;
PointsChartTop = ChartSheet.Range(['B', num2str(RowChartTop)]).Top;
ChartObject = ChartSheet.ChartObjects.Add(10, PointsChartTop, 1000, 250);
ChartObject.Chart.HasTitle = 1;
ChartObject.Chart.ChartTitle.Text = '......';
ChartObject.Chart.Axes.Item(1).TickLabelPosition = 'xlTickLabelPositionLow';
%---
CurveObject = ChartObject.Chart.SeriesCollection.NewSeries;
XRange = DataSheet.Range(......);
YRange = DataSheet.Range(......);
CurveObject.XValues = XRange;
CurveObject.Values = YRange;
CurveObject.ChartType = 'xlLineMarkers';
CurveObject.MarkerSize = 2;
CurveObject.Format.Line.Weight = 2;
%---
RowLastChartBottom = ChartObject.BottomRightCell.Row;
end
0 comments:
Post a Comment