re: microsoft excel integration

[Former Member]

HI,

My requirement: when MDirector runs the report it should open the report in excel by automatic activation of the graph. That means it should show the graph in excel when he runs the report. How it is possible. I appreciaite your help.

rgds

p. krishna prasad

[former_member189629]

HI Prasad.

As you must be knowingn you would have to use OLE objects for your requirement. Check out the following snippets

Create an OLE Object:

*------- Create/start an Excel Object

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

*------- Set property Visible for the Excel Object

SET PROPERTY OF H_EXCEL 'Visible' = 1.

Create a template which the program can use in case the excel file has to be in some standard format:

*------- Open the Template to populate Report Data

CALL METHOD OF H_EXCEL 'Workbooks' = OLE_BOOKS.

CALL METHOD OF OLE_BOOKS 'Open' = OLE_DATABOOK

EXPORTING #1 = I_SOURCE_FILE.

Call method of excel to fill cells with the values:

CALL METHOD OF H_EXCEL 'Cells' = H_ZL

EXPORTING #1 = ROW

#2 = COL.

Call method of excel to create a graph with the populated values:

Save the template:

*------ Saving the Downloaded Template into a user specified location

CALL METHOD OF OLE_DATABOOK 'SaveAs'

EXPORTING #1 = I_DESTINATION_FILE

#2 = FILE_TYPE.

If you find this answer useful, please reward me for the same.

Good luck

Karthik Potharaju

[Former Member]

Hi,

Can anybody give how to active the chart(graph) in excel. I am new to ole coding. I appreciate your help.

rgs

p. krishna prasad

[naimesh_patel]

Hello,

Try this code.

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.