populating graph in excel using ole

[Former Member]

Hi All,

I am able to populate the graph from abap into excel using OLE.

But I am not able to take x-axis and y-axis according to my requirement, its taking by its own as default. please see the below code:

<b>CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1

EXPORTING

#1 = GV_LINE_CNTR

#2 = 1.

CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2

EXPORTING

#1 = GV_LINNO1

#2 = GV_COLNO.

CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS

EXPORTING

#1 = GS_CELL1

#2 = GS_CELL2.

CALL METHOD OF GS_CELLS 'Select' .

GET PROPERTY OF GS_APPLICATION 'Charts' = GS_CHARTS .

CALL METHOD OF GS_CHARTS 'Add' = GS_CHART .

CALL METHOD OF GS_CHART 'Activate' .

SET PROPERTY OF GS_CHART 'ChartType' = '93' .</b>Now I want to give my own range. But i am not able to.

I am also not able to give labels of x-axis and y-axis.

Please help, its very urgent............

[naimesh_patel]

Hello,

try this code. It creates macro as a file, and than uses that macro to show the chart

Regards,

Naimesh

*********************

REPORT ZTEST_NP .

INCLUDE OLE2INCL. " OLE objects include

*export data variables

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

WORKBOOKS TYPE OLE2_OBJECT, " list of workbooks

THIS_WORKBOOK TYPE OLE2_OBJECT, " workbook

CELL TYPE OLE2_OBJECT, " cell

BOOK_FONT TYPE OLE2_OBJECT. " font

*chart creation DATA: variables

DATA: MODULE TYPE OLE2_OBJECT, "macro

NEWMODULE TYPE OLE2_OBJECT. "macrotab with VB source code

• INternal tableTABLES: no_table.

DATA H TYPE I.

• table that contains Visual Basic source code

DATA: BEGIN OF MACROFILE OCCURS 0,

LINE(80) ,

END OF MACROFILE.

*Internal table with Months and related Profits

DATA: BEGIN OF NO_TABLE OCCURS 10, "Internal table with no's

MONTH(8) TYPE C,

PROFIT(8) TYPE C,

END OF NO_TABLE.

*Months added to table as well as Profits

NO_TABLE-MONTH = 'June'.

NO_TABLE-PROFIT = '1000'. APPEND NO_TABLE. "Update table

NO_TABLE-MONTH = 'July'.

NO_TABLE-PROFIT = '4000'. APPEND NO_TABLE. "Update table

NO_TABLE-MONTH = 'August'.

NO_TABLE-PROFIT = '3000'. APPEND NO_TABLE. "Update table

NO_TABLE-MONTH = 'September'.

NO_TABLE-PROFIT = '7000'. APPEND NO_TABLE. "Update table

NO_TABLE-MONTH = 'October'.

NO_TABLE-PROFIT = '7000'. APPEND NO_TABLE. "Update table

NO_TABLE-MONTH = 'November'.

NO_TABLE-PROFIT = '6000'. APPEND NO_TABLE. "Update table

CALL METHOD OF THIS_WORKBOOK 'InsertFile'

EXPORTING #1 = 'C:\VBsource.tmp'.

*********Fill makro table with VB source code************************

• the macro's name is draw_graph

MACROFILE-LINE = 'sub draw_graph()'. APPEND MACROFILE.

MACROFILE-LINE = 'Range("a1:a1").Font.Bold = True'.APPEND macrofile.

MACROFILE-LINE =

'Range("a1:a1").Value = "Net Profits for last few months.

"'. APPEND macrofile. "Bold heading at top of sheet.

MACROFILE-LINE = 'Range("A2:B7").Select'. APPEND macrofile.

MACROFILE-LINE = 'charts.add'. APPEND MACROFILE.

MACROFILE-LINE = 'activechart.charttype = xllinemarkersStacked

'. APPEND macrofile.

MACROFILE-LINE =

'Activechart.setsourcedata source:=sheets("Sheet1").range("A2:B7") ,

PLOTBY:= XLROWS'. APPEND macrofile.

MACROFILE-LINE = 'activechart.location where:=xllocationasobject, name:=

"sheet1"'. APPEND macrofile.

MACROFILE-LINE = 'With ActiveChart'. APPEND MACROFILE.

MACROFILE-LINE = '.hasLegend = false '. APPEND MACROFILE.

MACROFILE-LINE = ' .Axes(xlValue, xlPrimary).HasTitle = True'.

APPEND MACROFILE.

MACROFILE-LINE = '.axes(xlvalue, xlprimary).axistitle.characters.text =

"Net Profit ($)"'.

APPEND MACROFILE.

MACROFILE-LINE = 'End With'. APPEND MACROFILE.

MACROFILE-LINE = 'msgbox ("Chart completed!")'. APPEND macrofile.

MACROFILE-LINE = 'end sub '. APPEND MACROFILE.

*******************vb source code end********************************

***********create file with visual basic source code *******************

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

FILENAME = 'c:\VBsource.tmp' "directory with temporary file

FILETYPE = 'ASC'

TABLES

DATA_TAB = MACROFILE

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_WRITE_ERROR = 2

INVALID_FILESIZE = 3

INVALID_TABLE_WIDTH = 4

INVALID_TYPE = 5

NO_BATCH = 6

UNKNOWN_ERROR = 7

OTHERS = 8.

**********create file with visual basic source code end**************

**write info to screen (ABAP)

LOOP AT NO_TABLE.

WRITE: / SY-VLINE NO-GAP,

NO_TABLE-MONTH COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP,

NO_TABLE-PROFIT COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP.

ENDLOOP.

ULINE (61).

**********************START THE EXCEL APPLICATION*********************

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'. "type of application

PERFORM CHECK_ERRORS.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

PERFORM CHECK_ERRORS.

CALL METHOD OF H_EXCEL 'Workbooks' = WORKBOOKS.

PERFORM CHECK_ERRORS.

• create a new workbook

CALL METHOD OF WORKBOOKS 'Add' = THIS_WORKBOOK.

PERFORM CHECK_ERRORS.

***********fill the Excel table with data*****************************

PERFORM FILL_EXC_TAB USING 1 1 1 'Profits:'(001). "initial heading

LOOP AT NO_TABLE.

H = SY-TABIX + 1.

PERFORM FILL_EXC_TAB USING H 1 0 NO_TABLE-MONTH.

ENDLOOP.

LOOP AT NO_TABLE.

H = SY-TABIX + 1.

PERFORM FILL_EXC_TAB USING H 2 0 NO_TABLE-PROFIT.

ENDLOOP.

***********fill the Excel table with data end*************************

************DRAW the CHART using the values exported *******************

CALL METHOD OF H_EXCEL 'Modules' = MODULE.

PERFORM CHECK_ERRORS.

CALL METHOD OF MODULE 'Add' = NEWMODULE. " the macro

PERFORM CHECK_ERRORS.

CALL METHOD OF NEWMODULE 'Activate'.

PERFORM CHECK_ERRORS.

• get source code from temporary file

CALL METHOD OF NEWMODULE 'InsertFile' EXPORTING #1 = 'C:\VBsource.tmp'.

PERFORM CHECK_ERRORS.

• executing VBA program code -> macro name = "draw_graph"

CALL METHOD OF H_EXCEL 'Run' EXPORTING #1 = 'draw_graph'.

PERFORM CHECK_ERRORS.

FREE OBJECT H_EXCEL. "free up memory

PERFORM CHECK_ERRORS.

FORM FILL_EXC_TAB USING I J BOLD VAL.

CALL METHOD OF H_EXCEL 'Cells' = CELL EXPORTING #1 = I #2 = J.

PERFORM CHECK_ERRORS.

SET PROPERTY OF CELL 'Value' = VAL .

PERFORM CHECK_ERRORS.

GET PROPERTY OF CELL 'Font' = BOOK_FONT.

PERFORM CHECK_ERRORS.

SET PROPERTY OF BOOK_FONT 'Bold' = BOLD .

PERFORM CHECK_ERRORS.

ENDFORM.

• error handling subroutine

FORM CHECK_ERRORS.

IF SY-SUBRC NE 0.

WRITE: / 'OLE error:'(010), SY-SUBRC.

STOP.

ENDIF.

ENDFORM.

[Former Member]

I tried to use this code but it is showing a error( file not found)in the excel sheet.

please help...

[naimesh_patel]

Hello,

May be you don't have access to generate the file on C drive.

So put a break point after ws_download. If file doesn't get downloaded, change the location like c:\temp\vbsource.tmp

If you change the location of file, you need to change that in the method also...

• get source code from temporary file

CALL METHOD OF NEWMODULE 'InsertFile' EXPORTING #1 = 'C:\VBsource.tmp'.

regards,

Naimesh

[Former Member]

Hi,

Check this ...

Here you can see the example.

Regards

vijay