create / display chart

mjpowellesq · Aug 24, 2011

Greetings,
OE 10.1. I am looking for a control to create / display a chart
Does anybody know the syntax to do this using Excel ??
TIA

mrobles · Aug 24, 2011

chWorkSheet:Range("B4:B6,C4:C6"):Select.
chWorkbook:Charts:Add.
chWorkbook:ActiveChart:ChartType = 57. /* Horizontal Bar */

mrobles · Aug 24, 2011

Code:

/* Full example */

DEFINE   variable chExcelApplication      AS COM-HANDLE.
DEFINE   variable chWorkbook              AS COM-HANDLE.
DEFINE   variable chWorksheet             AS COM-HANDLE.
DEFINE   variable chChart                 AS COM-HANDLE.
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Visible = TRUE.
chWorkbook = chExcelApplication:Workbooks:Add().
chWorkSheet = chExcelApplication:Sheets:Item(1).    
    
    DEF VAR t_nom AS CHAR.
    DEF VAR t_ran AS CHAR.
    DEF VAR delta AS INT INITIAL 10.
    chWorkSheet:Range('A1'):Value = 'Enero'.
    chWorkSheet:Range('A2'):Value = 'Febrero'.
    chWorkSheet:Range('A3'):Value = 'marzo'.
    chWorkSheet:Range('A4'):Value = 'Abril'.
    chWorkSheet:Range('B1'):Value = '1'.
    chWorkSheet:Range('B2'):Value = '5'.
    chWorkSheet:Range('B3'):Value = '3'.
    chWorkSheet:Range('B4'):Value = '6'.
    chWorkSheet:Range('A1:B4'):Select.
    chWorkbook:Charts:Add.
    chWorkbook:ActiveChart:ChartType = 57.
    chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
    t_nom = chWorkbook:ActiveChart:NAME.
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):TOP = 12.5 * (delta + 1).
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):LEFT = 60.
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):WIDTH = 250.
    chWorkbook:ActiveSheet:ChartObjects("Gráfico 1"):Activate.
    chWorkbook:ActiveChart:Legend:SELECT.
    chWorkbook:ActiveChart:Legend:DELETE.
    chWorkbook:ActiveChart:PlotArea:SELECT.
    chWorkbook:ActiveChart:PlotArea:Interior:ColorIndex = 19.
    chWorkSheet:Range('A1:B4'):Select.
    chWorkbook:Charts:Add.
    chWorkbook:ActiveChart:ChartType = 5.
    chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
    t_nom = chWorkbook:ActiveChart:NAME.
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):TOP = 12.5 * (delta + 1).
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):LEFT = 320.
    chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):WIDTH = 250.
    chWorkbook:ActiveSheet:ChartObjects("Gráfico 2"):Activate.
    chWorkbook:ActiveChart:Legend:SELECT.
    chWorkbook:ActiveChart:Legend:DELETE.
    chWorkbook:ActiveChart:PlotArea:SELECT.
    chWorkbook:ActiveChart:PlotArea:Interior:ColorIndex = 19.
RELEASE OBJECT chExcelApplication NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chWorksheet NO-ERROR.
RELEASE OBJECT chChart NO-ERROR.

mjpowellesq · Aug 25, 2011

Thank you, this is exactly what I required.
BFN

mjpowellesq · Aug 25, 2011

I am now trying to save the created document, I have the code somewhere.
I would be very grateful if you could point me in the right direction
TIA

mjpowellesq · Aug 25, 2011

Found it

vchWorkBook

ublishObjects:ADD(xlSourceChart,"C:\web1.htm","Sheet1","Chart1",xlHtmlStatic,"xx","").

Thanks
BFN

mjpowellesq · Aug 25, 2011

oops thats the next command I require, to make a web document from the chart, still looking to save the excel document TIA

mjpowellesq · Aug 25, 2011

Problem with the code that you originally gave me:
* *chWorksheet:Range('B' + STRING(v-col-num)):VALUE = STRING(2). *
The problem is, error accessing property/method range.
How do I find out the required attribute ? *It is obviously not Range
TIA

mjpowellesq · Aug 25, 2011

Then I wish to save the document locally, the attribute for Save as vFilename ??
TIA

mjpowellesq · Aug 25, 2011

Found the syntax:
**vchWorkBook:SaveAs("c:\temp\filename.xls",,,,,,,).

This will save the created excel document as filename.xls
BFN

mjpowellesq · Aug 25, 2011

Found the problem code from supplied example:
Progress doesn`t like this:
/* chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):TOP = 12.5 * (delta + 1). */
/* chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):LEFT = 320. */
/* chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):WIDTH = 250. */
/* chWorkbook:ActiveSheet:ChartObjects("Gráfico 2"):Activate. */
/* chWorkbook:ActiveChart:Legend:SELECT. */
/* chWorkbook:ActiveChart:LegendELETE. */
/* chWorkbook:ActiveChart

lotArea:SELECT. */
/* chWorkbook:ActiveChart

lotArea:Interior:ColorIndex = 19. */
Errors accessing method ....
TIA

Cringer · Aug 26, 2011

That's because the

in

ELETE was replaced with a smiley. Will edit.

Cringer · Aug 26, 2011

It's edited now so copy and paste the code again and it should work.

mjpowellesq · Aug 26, 2011

Sorry, still same problem. *I recopied the code into procedure editor.
It doesn`t like the syntax:
code:

DEFINE variable chExcelApplication AS COM-HANDLE.
DEFINE variable chWorkbook AS COM-HANDLE.
DEFINE variable chWorksheet AS COM-HANDLE.
DEFINE variable chChart AS COM-HANDLE.

CREATE "Excel.Application" chExcelApplication.

chExcelApplication:Visible = TRUE.
chWorkbook = chExcelApplication:Workbooks:Add().
chWorkSheet = chExcelApplication:Sheets:Item(1).

DEF VAR t_nom AS CHAR.
DEF VAR t_ran AS CHAR.
DEF VAR delta AS INT INITIAL 10.
chWorkSheet:Range('A1'):Value = 'Enero'.
chWorkSheet:Range('A2'):Value = 'Febrero'.
chWorkSheet:Range('A3'):Value = 'marzo'.
chWorkSheet:Range('A4'):Value = 'Abril'.
chWorkSheet:Range('B1'):Value = '1'.
chWorkSheet:Range('B2'):Value = '5'.
chWorkSheet:Range('B3'):Value = '3'.
chWorkSheet:Range('B4'):Value = '6'.
chWorkSheet:Range('A1:B4'):Select.
chWorkbook:Charts:Add.
chWorkbook:ActiveChart:ChartType = 57.
chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
t_nom = chWorkbook:ActiveChart:NAME.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):TOP = 12.5 * (delta + 1).
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):LEFT = 60.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):WIDTH = 250.
chWorkbook:ActiveSheet:ChartObjects("Gráfico 1"):Activate.
chWorkbook:ActiveChart:Legend:SELECT.
chWorkbook:ActiveChart:Legend

ELETE.
chWorkbook:ActiveChart

lotArea:SELECT.
chWorkbook:ActiveChart

lotArea:Interior:ColorIndex = 19.
chWorkSheet:Range('A1:B4'):Select.
chWorkbook:Charts:Add.
chWorkbook:ActiveChart:ChartType = 5.
chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
t_nom = chWorkbook:ActiveChart:NAME.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):TOP = 12.5 * (delta + 1).
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):LEFT = 320.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):WIDTH = 250.
chWorkbook:ActiveSheet:ChartObjects("Gráfico 2"):Activate.
chWorkbook:ActiveChart:Legend:SELECT.
chWorkbook:ActiveChart:Legend

ELETE.
chWorkbook:ActiveChart

lotArea:SELECT.
chWorkbook:ActiveChart

lotArea:Interior:ColorIndex = 19.
RELEASE OBJECT chExcelApplication NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chWorksheet NO-ERROR.
RELEASE OBJECT chChart NO-ERROR.

The code compiles, but when it is executed it falls down ??
TIA

mjpowellesq · Aug 26, 2011

DEFINE variable chExcelApplication AS COM-HANDLE.
DEFINE variable chWorkbook AS COM-HANDLE.
DEFINE variable chWorksheet AS COM-HANDLE.
DEFINE variable chChart AS COM-HANDLE.

CREATE "Excel.Application" chExcelApplication.

chExcelApplication:Visible = TRUE.
chWorkbook = chExcelApplication:Workbooks:Add().
chWorkSheet = chExcelApplication:Sheets:Item(1).

DEF VAR t_nom AS CHAR.
DEF VAR t_ran AS CHAR.
DEF VAR delta AS INT INITIAL 10.
chWorkSheet:Range('A1'):Value = 'Enero'.
chWorkSheet:Range('A2'):Value = 'Febrero'.
chWorkSheet:Range('A3'):Value = 'marzo'.
chWorkSheet:Range('A4'):Value = 'Abril'.
chWorkSheet:Range('B1'):Value = '1'.
chWorkSheet:Range('B2'):Value = '5'.
chWorkSheet:Range('B3'):Value = '3'.
chWorkSheet:Range('B4'):Value = '6'.
chWorkSheet:Range('A1:B4'):Select.
chWorkbook:Charts:Add.
chWorkbook:ActiveChart:ChartType = 57.
chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
t_nom = chWorkbook:ActiveChart:NAME.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):TOP = 12.5 * (delta + 1).
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):LEFT = 60.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 1"):WIDTH = 250.
chWorkbook:ActiveSheet:ChartObjects("Gráfico 1"):Activate.
chWorkbook:ActiveChart:Legend:SELECT.
chWorkbook:ActiveChart:Legend

ELETE.
chWorkbook:ActiveChart

lotArea:SELECT.
chWorkbook:ActiveChart

lotArea:Interior:ColorIndex = 19.
chWorkSheet:Range('A1:B4'):Select.
chWorkbook:Charts:Add.
chWorkbook:ActiveChart:ChartType = 5.
chWorkbook:ActiveChart:Location(2, chWorkSheet:NAME).
t_nom = chWorkbook:ActiveChart:NAME.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):TOP = 12.5 * (delta + 1).
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):LEFT = 320.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):WIDTH = 250.
chWorkbook:ActiveSheet:ChartObjects("Gráfico 2"):Activate.
chWorkbook:ActiveChart:Legend:SELECT.
chWorkbook:ActiveChart:Legend

ELETE.
chWorkbook:ActiveChart

lotArea:SELECT.
chWorkbook:ActiveChart

lotArea:Interior:ColorIndex = 19.
RELEASE OBJECT chExcelApplication NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.

RELEASE OBJECT chWorksheet NO-ERROR.
RELEASE OBJECT chChart NO-ERROR.

The web page keeps displaying the smiley, though this is NOT transfered in the code. *Thats why I resubmitted the code ??
TIA

mrobles · Aug 26, 2011

To save use one

chExcelApplication:activeWorkBook:SaveAs('c:\XDir\wrk.csv', 24,,,,,).
chExcelApplication:activeWorkBook:SaveAs('c:\XDir\wrk.xls', -4143 ,,,,,).

mrobles · Aug 26, 2011

The problem may be the shapes's name due the language. I use spanish

chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):TOP = 12.5 * (delta + 1).
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):LEFT = 320.
chWorkbook:ActiveSheet:Shapes:range("Gráfico 2"):WIDTH = 250.
chWorkbook:ActiveSheet:ChartObjects("Gráfico 2"):Activate.

then you need to know the names your excel uses in the graphics.
Open excel.
Enter some data
begin macro record
select the data
select draw graph
stop record.
Review the recorded macro and you will see the name. May be it is 'graphic 1', '1graphic', '1 Graphic' or some like that.
Then you can change in the program the text "Gráfico 2" and use the name that your excel uses.

Mario

mjpowellesq · Aug 31, 2011

I have resolved the issue, though Progress is now NOT releasing the document, even though I have included appropriate release statements (or so I must have thought, everything that I have opened/created with Progress I have released) Any ideas ??
TIA

mjpowellesq · Sep 1, 2011

chart to web

Greetings,

I have managed to save the document, using ;
chExcelApplication:activeWorkBook:SaveAs('C:\wrkS11.xls', -4143,,,,,)
Now I would like to be able to convert the chart to a web page.
I have the syntax that I am trying to understand to make work:
chWorkBook

ublishObjects:ADD(xlSourceChart,"C:\web1.htm","Sheet1","Chart1",xlHtmlStatic,"xx","")
Any help would be appreciated.
TIA

mrobles · Sep 1, 2011

Re: chart to web

Save as htm

chExcelApplication:activeWorkBook:Saveas('c:\WorkDir\x.htm', 44 ,,,,,).

create / display chart

Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

ProgressTalk.com Moderator

ProgressTalk.com Moderator

Member

Member

Member

Member

Member

Member

Member

Similar threads