AUTOMATE DATA LABEL FONT SIZE

*!*    Author : Cetin Basoz

*!*    http://www.foxite.com/archives/0000252553.htm

 

#Define xlDataLabelsShowBubbleSizes  6

#Define xlDataLabelsShowLabel  4

#Define xlDataLabelsShowLabelAndPercent  5

#Define xlDataLabelsShowNone  -4142

#Define xlDataLabelsShowPercent  3

#Define xlDataLabelsShowValue  2

 

#Define xlColumns 2

#Define xlColumnClustered                                 51

#Define xl3DPieExploded 70

 

#Define xlLabelPositionBestFit 5

 

lcXLS = Sys(5)+Curdir()+'exceldatalabeltest.xls'

 

Create Cursor SampleData (ItemName c(10), v1 i, v2 i, v3 i)

For ix=1 To 10

  Insert Into SampleData Values ;

    ('Item'+Transform(ix), Rand()*100, Rand()*100, Rand()*100 )

Endfor

Copy To (m.lcXLS) Type Xls

 

*** set the LOCALEID to English

nlLocaleId=Sys(3004)        && Save local id

=Sys(3006,1033)                && We will be sending instructions in English

oExcel = Crea('excel.application')

With oExcel

  .workbooks.Open(m.lcXLS)

  .Visible = .T.

  With .ActiveWorkBook.ActiveSheet

    oChartRange = .UsedRange

    .ChartObjects.Add(200, 10, 500, 500)

    With .ChartObjects(1).Chart

      .SetSourceData(oChartRange, xlColumns)

      .hastitle = .T.

      .haslegend = .T.

      .ChartTitle.Caption = "Chart Title"

      .ChartType = xl3DPieExploded

      .ApplyDataLabels()

      With .SeriesCollection(1).DataLabels

        .ShowValue = .T.

        .ShowPercentage = .T.

        .Position = xlLabelPositionBestFit

        .Separator = CHR(10)

      Endwith

      With .SeriesCollection(1).DataLabels.Font

        .Name = 'Tahoma'

        .Size = 12

        .Underline = .T.

      Endwith

    Endwith

  Endwith

Endwith

=Sys(3006,Val(nlLocaleId))