Hello Guys
The code below works fine (complete code). The problem is, how can one set the number of rows in the following lines. These two lines sets the chart data parameters. Although one can use "oSheet.UsedRange.Rows.Count" but how to encorporate it in the code. Please complete the following 2 lines.
oExcel.ActiveChart.SetSourceData(oSheet.Range("A2:A16,E2:E16"), xlColumns)
oExcel.ActiveChart.SeriesCollection(1).XValues = "=History!R2C1:R16C1"
Thanks & Regards, Jitendra Shahani
Complete Code :-
#Define xlTrue -1
#Define xlFalse 0
#Define msoScaleFromTopLeft 0
#Define msoFalse 0
LOCAL oExcel, oSheet
oExcel = CreateObject("Excel.Application")
oExcel.Visible = -1
oExcel.Workbooks.Open("C:\History.Xls")
oSheet = oExcel.ActiveSheet
oSheet.Cells.Select
oSheet.Columns.AutoFit
oExcel.Charts.Add
With oExcel.ActiveChart
.ChartType = xlLineMarkers
.SetSourceData(oSheet.Range("A2:A16,E2:E16"), xlColumns)
.SeriesCollection(1).XValues = "=History!R2C1:R16C1"
.Location(xlLocationAsObject, oSheet.Name)
EndWith
With oExcel.ActiveChart
.HasTitle = xlTrue
.ChartTitle.Characters.Text = "Purchase History"
.Axes(xlCategory, xlPrimary).HasTitle = xlTrue
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Document No."
.Axes(xlValue, xlPrimary).HasTitle = xlTrue
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Purchase Rate"
.HasDataTable = xlFalse
.Legend.Select
.Legend.Delete
EndWith
oExcel.ActiveChart.SeriesCollection(1).ApplyDataLabels
oExcel.ActiveChart.SeriesCollection(1).DataLabels.AutoScaleFont = xlTrue
With oSheet.Shapes("Chart 1")
.IncrementLeft(-183.75)
.IncrementTop(-105.75)
.ScaleWidth(1.99, msoFalse, msoScaleFromTopLeft)
.ScaleHeight(1.96, msoFalse, msoScaleFromTopLeft)
EndWith
With oExcel.ActiveChart.SeriesCollection(1).DataLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = xlFalse
.Superscript = xlFalse
.Subscript = xlFalse
.OutlineFont = xlFalse
.Shadow = xlFalse
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
EndWith