Highlighted
Absent Member.
Absent Member.
665 views

[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
0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel Chart

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
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel Chart

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.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.