[archive] Excel Chart

[Migrated content. Thread originally posted on 23 February 2005]

A thanks to all who contribute to this forum. By the examples given, I have been able to put formatted data into Excel for my users and they excited by the possibilities.

I have been working on the Excel chart example given in a prior post and have been adding features. I am very new to this type of programming and have not been able to translate the following code that it will work:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.ChartArea.Select
MsgBox (xlCategory)
MsgBox (xlPrimary)
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X axis Title"
End With

Any help would be greatly appreciated.

Thanks in advance, Duane

Here a sample showing how to create a chart in excel. I now the previous post is an old one but I thought I'd share this sample with everyone here on the forum as it seems to be a hot topic.

There may be several ways to do this.

IDENTIFICATION DIVISION.
PROGRAM-ID. EXCELTEST.
AUTHOR. HANS JIVESTEN JIKON DATA.
DATE-WRITTEN. MARCH 2007 IN A DULL STOCKHOLM HOTEL ROOM.

* SHOWS HOW TO CREATE AN EXCEL CHART.

*
* Vers Time Description
* ---- ---------------- ----------------------------------------------
* 2007-03-01 17:59 New program.



ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
*=====================
*
SPECIAL-NAMES.
COPY "EXCEL.DEF".
.



DATA DIVISION.
WORKING-STORAGE SECTION.
*=======================
*
77 XLAPP HANDLE of Application.
77 XLWBK HANDLE of WorkBook.
77 XLSHT HANDLE of Worksheet.
77 XLCHART HANDLE of Chart.
77 XLRANGE HANDLE of Range.
77 XLAXIS HANDLE of Axis.



PROCEDURE DIVISION.
MAIN-SECTION SECTION.
*====================
*
CREATE-APPLICATION-OBJECT.
CREATE @Application OF @Excel HANDLE IN XLAPP.
MODIFY XLAPP @Visible = 1.

CREATE-WORKBOOK.
MODIFY XLAPP @Workbooks::Add() GIVING XLWBK.

CREATE-WORKSHEET.
INQUIRE XLWBK @ActiveSheet IN XLSHT.

FILL-SOME-VALUES-IN.
MODIFY XLAPP @Range("B1")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "Jan".
MODIFY XLAPP @Range("C1")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "Feb".
MODIFY XLAPP @Range("D1")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "Mar".
MODIFY XLAPP @Range("E1")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "Apr".
MODIFY XLAPP @Range("A2")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2006".
MODIFY XLAPP @Range("A3")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2007".
MODIFY XLAPP @Range("A4")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2008".
MODIFY XLAPP @Range("B2")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "1".
MODIFY XLAPP @Range("B3")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("B4")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("C2")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "1".
MODIFY XLAPP @Range("C3")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "1".
MODIFY XLAPP @Range("C4")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("D2")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("D3")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "3".
MODIFY XLAPP @Range("D4")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("E2")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "1".
MODIFY XLAPP @Range("E3")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "2".
MODIFY XLAPP @Range("E4")::Select().
MODIFY XLAPP @ActiveCell::FormulaR1C1 = "1".

FREEZE-PANES.
MODIFY XLAPP @Range("B2")::Select().
MODIFY XLAPP @ActiveWindow::FreezePanes = 1.

CREATE-GRAPH.
INQUIRE XLSHT @Range("A1:E4") IN XLRANGE.
MODIFY XLAPP @Charts::Add() GIVING XLCHART.
MODIFY XLAPP @ActiveChart::ChartType = xlColumnClustered.
MODIFY XLCHART
@SetSourceData(XLRANGE, xlRows)
@Location(xlLocationAsNewSheet)
@HasTitle = 1
@ChartTitle::Characters::Text = "The Graph".
MODIFY XLCHART @Axes(xlCategory, xlPrimary) GIVING XLAXIS.
MODIFY XLAXIS
@HasTitle = 1
@AxisTitle::Characters::Text = "Month".
MODIFY XLCHART @Axes(xlValue, xlPrimary) GIVING XLAXIS.
MODIFY XLAXIS
@HasTitle = 1
@AxisTitle::Characters::Text = "Value".


DESTROY-OBJECTS.
DESTROY XLSHT.
DESTROY XLAXIS.
DESTROY XLCHART.
DESTROY XLRANGE.
DESTROY XLWBK.
DESTROY XLAPP.

THE-END.
STOP RUN.

Regards,
Hans

[Migrated content. Thread originally posted on 23 February 2005]

A thanks to all who contribute to this forum. By the examples given, I have been able to put formatted data into Excel for my users and they excited by the possibilities.

I have been working on the Excel chart example given in a prior post and have been adding features. I am very new to this type of programming and have not been able to translate the following code that it will work:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.ChartArea.Select
MsgBox (xlCategory)
MsgBox (xlPrimary)
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X axis Title"
End With

Any help would be greatly appreciated.

Thanks in advance, Duane

You could also create an XML file. Create a chart manually in Excel and then save it as XML and see what it looks like...

Then, C$XML is very handy.

[Migrated content. Thread originally posted on 23 February 2005]

A thanks to all who contribute to this forum. By the examples given, I have been able to put formatted data into Excel for my users and they excited by the possibilities.

I have been working on the Excel chart example given in a prior post and have been adding features. I am very new to this type of programming and have not been able to translate the following code that it will work:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.ChartArea.Select
MsgBox (xlCategory)
MsgBox (xlPrimary)
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X axis Title"
End With

Any help would be greatly appreciated.

Thanks in advance, Duane

You could also create an XML file. Create a chart manually in Excel and then save it as XML and see what it looks like...

Then, C$XML is very handy.