Excel Automation - Charts.

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

Answer this question

Excel Automation - Charts.

septembereleven
To finish off Cetin's example

then you do a
RANGE(lcRange1+","+lcRange2)
Rodcet
Andrew,
Yes that's basically what I meant but having multiple ranges using list separator (comma in US) might break with regional settings. I prefer union:

with oExcel.ActiveWorkBook.ActiveSheet
oMyRange = oExcel.Union( .Range(m.lcRange1), .Range( m.lcRange2) )
endwith
Microsoft Empower for ISVs Team
lnRows = oSheet.UsedRange.Rows.Count
lcRange1 = 'A2:A'+ltrim(str(m.lnRows))
lcRange2 = 'E2:E'+ltrim(str(m.lnRows))
* Union ranges - it's dangerous to use range("A2:A16,E2:E16")
Excel Automation - Charts.

Answer this question