Write data from SAP into Word document (OLE)

[Former Member]

Hello,

We have a existing word document and we want to fill it with data from SAP,

I want to write in specifique area in this document, can you show me how to do it from a abap program?

Thanks.

[Former Member]

Hello,

We have a existing word document and we want to fill it with data from SAP,

I want to write in specifique area in this document, can you show me how to do it from a abap program?

Thanks.

[Former Member]

Hi ,

Check out this sample program .

Code Part B.1 Data declarations

REPORT zole_tutor_example_ms_word .

*--Include for OLE-enabling definitions

INCLUDE ole2incl .

*--Global variables

*--Variables to hold OLE object and entity handles

DATA gs_word TYPE ole2_object . "OLE object handle

DATA gs_documents TYPE ole2_object . "Documents

DATA gs_actdoc TYPE ole2_object . "Active document

DATA gs_application TYPE ole2_object . "Application

DATA gs_options TYPE ole2_object . "Application options

DATA gs_actwin TYPE ole2_object . "Active window

DATA gs_actpan TYPE ole2_object . "Active pane

DATA gs_view TYPE ole2_object . "View

DATA gs_selection TYPE ole2_object . "Selection

DATA gs_font TYPE ole2_object . "Font

DATA gs_parformat TYPE ole2_object . "Paragraph format

DATA gs_tables TYPE ole2_object . "Tables

DATA gs_range TYPE ole2_object . "Range handle for various ranges

DATA gs_table TYPE ole2_object . "One table

DATA gs_table_border TYPE ole2_object . "Table border

DATA gs_cell TYPE ole2_object . "One cell of a table

DATA gs_paragraph TYPE ole2_object . "Paragraph

DATA gv_pos(5) TYPE n . "Position information for table

Step 2 Creating the OLE object and get main entities to handle variables.

START-OF-SELECTION .

*--Creating OLE object handle variable

CREATE OBJECT gs_word 'WORD.APPLICATION' .

IF sy-subrc NE 0 .

MESSAGE s000(su) WITH 'Error while creating OLE object!'.

LEAVE PROGRAM .

ENDIF .

*--Setting object's visibility property

SET PROPERTY OF gs_word 'Visible' = '1' .

*--Opening a new document

GET PROPERTY OF gs_word 'Documents' = gs_documents .

CALL METHOD OF gs_documents 'Add' .

*--Getting active document handle

GET PROPERTY OF gs_word 'ActiveDocument' = gs_actdoc .

*--Getting applications handle

GET PROPERTY OF gs_actdoc 'Application' = gs_application .

Code Part B.2 Creating the OLE object

Step 3 Setting the measurement unit to ‘CM.’

Code Part B.3 Setting measurement unit

*--Setting the measurement unit

GET PROPERTY OF gs_application 'Options' = gs_options .

SET PROPERTY OF gs_options 'MeasurementUnit' = '1' . "CM

Step 4 Some header text.

Code Part B.4 Setting header content

*--Getting handle for the selection which is here the character at the

*--cursor position

GET PROPERTY OF gs_application 'Selection' = gs_selection .

GET PROPERTY OF gs_selection 'Font' = gs_font .

GET PROPERTY OF gs_selection 'ParagraphFormat' = gs_parformat .

*--Setting font attributes

SET PROPERTY OF gs_font 'Name' = 'Arial' .

SET PROPERTY OF gs_font 'Size' = '10' .

SET PROPERTY OF gs_font 'Bold' = '0' . "Not bold

SET PROPERTY OF gs_font 'Italic' = '1' . "Italic

SET PROPERTY OF gs_font 'Underline' = '0' . "Not underlined

*--Setting paragraph format attribute

SET PROPERTY OF gs_parformat 'Alignment' = '2' . "Right-justified

CALL METHOD OF gs_selection 'TypeText'

EXPORTING

#1 = 'This is an OLE example!'.

*--Setting the view to the main document again

SET PROPERTY OF gs_view 'SeekView' = '0' . "Main document view

Step 5 Writing the title.

Code Part B.5 Writing the title

*--Reseting font attributes for the title

SET PROPERTY OF gs_font 'Name' = 'Times New Roman' .

SET PROPERTY OF gs_font 'Size' = '16' .

SET PROPERTY OF gs_font 'Bold' = '1' . "Bold

SET PROPERTY OF gs_font 'Italic' = '0' . "Not Italic

SET PROPERTY OF gs_font 'Underline' = '0' . "Not underlined

*--Setting paragraph format attribute

SET PROPERTY OF gs_parformat 'Alignment' = '1' . "Centered

CALL METHOD OF gs_selection 'TypeText'

EXPORTING

#1 = text-000.

*--Advancing cursor to the new line

CALL METHOD OF gs_selection 'TypeParagraph' .

Step 6 Writing some text.

Code Part B.6 Writing some text

*--Reseting font attributes for ordinary text

SET PROPERTY OF gs_font 'Name' = 'Times New Roman' .

SET PROPERTY OF gs_font 'Size' = '12' .

SET PROPERTY OF gs_font 'Bold' = '0' . "Not bold

SET PROPERTY OF gs_font 'Italic' = '0' . "Not Italic

SET PROPERTY OF gs_font 'Underline' = '0' . "Not underlined

*--Setting paragraph format attribute

SET PROPERTY OF gs_parformat 'Alignment' = '3' . "Justified

CALL METHOD OF gs_selection 'TypeText'

EXPORTING

#1 = text-001.

*--Skip some lines

DO 4 TIMES .

CALL METHOD OF gs_selection 'TypeParagraph' .

ENDDO .

Step 7 Inserting a table and filling some of its cells.

*--Getting entity handles for the entities on the way

GET PROPERTY OF gs_actdoc 'Tables' = gs_tables .

GET PROPERTY OF gs_selection 'Range' = gs_range .

*--Adding a table with 3 rows and 2 columns

CALL METHOD OF gs_tables 'Add' = gs_table

EXPORTING

#1 = gs_range " Handle for range entity

#2 = '3' "Number of rows

#3 = '2'. "Number of columns

*--Setting border attribute for the table

GET PROPERTY OF gs_table 'Borders' = gs_table_border .

SET PROPERTY OF gs_table_border 'Enable' = '1' . "With border

*--Filling the table with dummy data

*--Reseting font attributes for table content

SET PROPERTY OF gs_font 'Name' = 'Garamond' .

SET PROPERTY OF gs_font 'Size' = '11' .

SET PROPERTY OF gs_font 'Bold' = '0' . "Not bold

SET PROPERTY OF gs_font 'Italic' = '0' . "Not Italic

SET PROPERTY OF gs_font 'Underline' = '0' . "Not underlined

*--Getting cell coordinates

CALL METHOD OF gs_table 'Cell' = gs_cell

EXPORTING

#1 = '1' "first row

#2 = '1'. "first column

*--Getting the range handle to write the text

GET PROPERTY OF gs_cell 'Range' = gs_range .

*--Filling the cell

SET PROPERTY OF gs_range 'Text' = 'OLE' .

*--Getting cell coordinates

CALL METHOD OF gs_table 'Cell' = gs_cell

EXPORTING

#1 = '3' "third row

#2 = '2'. "second column

*--Getting the range handle to write the text

Code Part B.7 Some table work

GET PROPERTY OF gs_cell 'Range' = gs_range .

*--Filling the cell

SET PROPERTY OF gs_range 'Text' = 'OLE' .

*--Advancing the cursor to the end of the table

GET PROPERTY OF gs_table 'Range' = gs_range .

GET PROPERTY OF gs_range 'End' = gv_pos .

SET PROPERTY OF gs_range 'Start' = gv_pos .

CALL METHOD OF gs_range 'Select' .

*--Skip some lines

DO 3 TIMES .

CALL METHOD OF gs_selection 'TypeParagraph' .

ENDDO .

Step 8 Adding some other text and indent its paragraph.

Code Part B.8 Writing some indented text

*--Reseting font attributes for ordinary text

SET PROPERTY OF gs_font 'Name' = 'Times New Roman' .

SET PROPERTY OF gs_font 'Size' = '12' .

SET PROPERTY OF gs_font 'Bold' = '0' . "Not bold

SET PROPERTY OF gs_font 'Italic' = '0' . "Not Italic

SET PROPERTY OF gs_font 'Underline' = '0' . "Not underlined

*--Setting paragraph format attribute

SET PROPERTY OF gs_parformat 'Alignment' = '3' . "Justified

*--Indent the paragraph once

GET PROPERTY OF gs_selection 'Paragraphs' = gs_paragraph .

CALL METHOD OF gs_paragraph 'Indent' .

CALL METHOD OF gs_selection 'TypeText'

EXPORTING

#1 = text-002.

Step 9 Freeing object handle variable to deallocate memory.

Code Part B.9 Freeing object handle variable

FREE OBJECT gs_word.

3. General Scheme for Integration with Microsoft Excel

Secondly, let’s build an application that integrates with MS Excel and uses some of its basic features. So, let’s define the outline for its task as:

i. User inputs the number of worksheets.

ii. For each sheet, user creates some data that is also the source for a chart.

iii. Formats cells.

iv. Draws the chart and relocates it to the proper place on the sheet.

Again, to be more clear, the code will be written in a non-modular way that will repeat reusable parts. For your further works, you can modularize all these. For example, all functional codes may be written as subroutines to be collected in a subroutine pool altogether. Or a function group can be implemented. In fact, the best way is to develop a class to encapsulate all. The order of method calls is important, so do not change their order.

Step 1 Data declarations.

Code Part C.1 Data declarations

REPORT zole_tutor_example_ms_excel .

INCLUDE ole2incl .

DATA: gs_excel TYPE ole2_object ,

gs_wbooklist TYPE ole2_object ,

gs_application TYPE ole2_object ,

gs_wbook TYPE ole2_object ,

gs_activesheet TYPE ole2_object ,

gs_sheets TYPE ole2_object ,

gs_newsheet TYPE ole2_object ,

gs_cell1 TYPE ole2_object ,

gs_cell2 TYPE ole2_object ,

gs_cells TYPE ole2_object ,

gs_range TYPE ole2_object ,

gs_font TYPE ole2_object ,

gs_interior TYPE ole2_object ,

gs_columns TYPE ole2_object ,

gs_charts TYPE ole2_object ,

gs_chart TYPE ole2_object ,

gs_charttitle TYPE ole2_object ,

gs_charttitlechar TYPE ole2_object ,

gs_chartobjects TYPE ole2_object .

DATA gv_sheet_name(20) TYPE c .

DATA gv_outer_index LIKE sy-index .

DATA gv_intex(2) TYPE c .

DATA gv_line_cntr TYPE i . "line counter

DATA gv_linno TYPE i . "line number

DATA gv_colno TYPE i . "column number

DATA gv_value TYPE i . "data

PARAMETERS: p_sheets TYPE i .

Step 2 Initiate the do-loop and OLE automation base objects.

Code Part C.2 Looping and initializing, adding new worksheets

START-OF-SELECTION .

DO p_sheets TIMES .

*--Forming sheet name

gv_intex = sy-index .

gv_outer_index = sy-index .

CONCATENATE 'Excel Sheet #' gv_intex INTO gv_sheet_name .

*--For the first loop, Excel is initiated and one new sheet is added

IF sy-index = 1 .

CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .

SET PROPERTY OF gs_excel 'Visible' = 1 .

GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .

GET PROPERTY OF gs_wbooklist 'Application' = gs_application .

SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1 .

CALL METHOD OF gs_wbooklist 'Add' = gs_wbook .

GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet .

SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name .

*--For the rest of loops, other sheets are added

ELSE .

GET PROPERTY OF gs_wbook 'Sheets' = gs_sheets .

CALL METHOD OF gs_sheets 'Add' = gs_newsheet .

SET PROPERTY OF gs_newsheet 'Name' = gv_sheet_name .

ENDIF .

gv_line_cntr = 1 . "line counter

Step3 Write the title and format it.

*--Title

*--Selecting cell area to be merged.

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = 1

#2 = 4.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

*--Merging

CALL METHOD OF gs_cells 'Merge' .

*--Setting title data

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'TITLE' .

Code Part C.3 Writing and formatting the title

*--Formatting the title

GET PROPERTY OF gs_cell1 'Font' = gs_font .

SET PROPERTY OF gs_font 'Underline' = 2 .

SET PROPERTY OF gs_font 'Bold' = 1 .

SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .

GET PROPERTY OF gs_cell1 'Interior' = gs_interior .

SET PROPERTY OF gs_interior 'ColorIndex' = 15 .

SET PROPERTY OF gs_interior 'Pattern' = -4124 .

SET PROPERTY OF gs_interior 'PatternColorIndex' = -4105 .

Step 4 Write some additional data for the title area and format them.

gv_line_cntr = gv_line_cntr + 1 .

*--Writing some additional data for the title

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'Sheet No' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 5.

SET PROPERTY OF gs_cell1 'Value' = ':' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 6.

SET PROPERTY OF gs_cell1 'Value' = gv_intex .

*--Formatting the area of additional data 1

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 5.

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_cells 'Font' = gs_font .

SET PROPERTY OF gs_font 'Bold' = 1 .

*--Formatting the area of additional data 2

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 5.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 5.

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_cells 'Columns' = gs_columns .

CALL METHOD OF gs_columns 'AutoFit' .

*--Bordering title data area

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 6.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

CALL METHOD OF gs_cells 'BorderAround'

EXPORTING

#1 = 1 "continuous line

#2 = 4. "thick

Code Part C.4 Some additional writing to the title area, formatting and bordering around the title area

Step 5 Put axis labels to the data area.

Code Part C.5 Axis Labels

*--Putting axis labels

gv_colno = 2 .

gv_line_cntr = gv_line_cntr + 5 .

gv_linno = gv_line_cntr - 1 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_linno

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'X' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'Y' .

Step 6 Generate some data.

Code Part C.6 Generating Data

*--Generating some data

DO 3 TIMES .

gv_value = gv_outer_index * sy-index * 10 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_linno

#2 = gv_colno.

SET PROPERTY OF gs_cell1 'Value' = sy-index .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = gv_colno.

SET PROPERTY OF gs_cell1 'Value' = gv_value .

gv_colno = gv_colno + 1 .

ENDDO .

Step 7 Set source data area for the chart.

Code Part C.7 Setting source data area for the chart

*--Source data area

gv_colno = gv_colno - 1 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_linno

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = gv_colno.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

Step8 Draw the chart

Code Part C.8 Draw the chart

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' = '51' . "Vertical bar graph

CALL METHOD OF gs_chart 'SetSourceData'

EXPORTING

#1 = gs_cells

#2 = 1.

SET PROPERTY OF gs_chart 'HasTitle' = 1 .

GET PROPERTY OF gs_chart 'ChartTitle' = gs_charttitle .

GET PROPERTY OF gs_charttitle 'Characters' = gs_charttitlechar .

SET PROPERTY OF gs_charttitlechar 'Text' = 'Sample Graph' .

Step 9 Locate the chart onto the current worksheet.

Code Part C.9 Locating the chart onto the current worksheet

*--Locate the chart onto the current worksheet

*--Activate current sheet

CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet

EXPORTING

#1 = gv_sheet_name.

CALL METHOD OF gs_activesheet 'Activate' .

CALL METHOD OF gs_chart 'Location'

EXPORTING

#1 = 2

#2 = gv_sheet_name.

Step 10 Reposition the chart to a proper place and finish the do-loop.

Code Part C.10 Repositioning the chart to a proper place and end of the do-loop counting sheet number

*--Reposition the chart on the worksheet (cut&paste)

CALL METHOD OF gs_activesheet 'ChartObjects' = gs_chartobjects .

CALL METHOD OF gs_chartobjects 'Select' .

CALL METHOD OF gs_chartobjects 'Cut' .

*--Select new area

gv_line_cntr = gv_line_cntr + 2 .

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_line_cntr

#2 = 1.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

CALL METHOD OF gs_activesheet 'Paste' .

ENDDO .

Step 11 Free OLE objects to deallocate memory.

Code Part C.11 Deallocating the memory

*--Deallocating memory

FREE: gs_excel, gs_wbooklist, gs_application, gs_wbook,

gs_activesheet,gs_sheets, gs_newsheet, gs_cell1,

gs_cell2, gs_cells, gs_range, gs_font, gs_interior,

gs_columns, gs_charts, gs_chart, gs_charttitle,

gs_charttitlechar, gs_chartobjects .

Result:

The result of the above program will be a number of worksheets having a title area, some

generated data, and a chart related to those data.

one more .

REPORT ZMEXCEL .

INCLUDE OLE2INCL.

TABLES : Zdayattd.

DATA : begin of IT_ZEMP occurs 0,

name(10),

age(3),

tel(15),

add(25),

end of it_zemp.

DATA : APPLICATION TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELLS TYPE OLE2_OBJECT.

CREATE OBJECT APPLICATION 'excel.application'.

*CREATE OBJECT APPLICATION 'graph.application'.

SET PROPERTY OF APPLICATION 'visible' = 1.

Call method of application 'Workbooks' = workbook.

perform errors.

call method of workbook 'Add'.

perform errors.

CALL METHOD OF APPLICATION 'Worksheets' = SHEET

EXPORTING #1 = 1.

PERFORM FILL_SHEET.

perform errors.

CALL METHOD OF SHEET 'Activate'.

CALL METHOD OF APPLICATION 'Worksheets' = SHEET

EXPORTING #1 = 2.

CALL METHOD OF SHEET 'Activate'.

perform errors.

PERFORM FILL_SHEET.

&----

---

*& Form FILL_SHEET

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM FILL_SHEET.

clear it_zemp.

refresh it_zemp.

DATA : ROW_MAX TYPE I VALUE 256,

INDEX TYPE I.

FIELD-SYMBOLS : <NAME>.

it_zemp-name = 'vishal'.

it_zemp-age = '25'.

it_zemp-tel = '123456'.

append it_zemp.

it_zemp-name = 'dinesh'.

it_zemp-age = '24'.

it_zemp-tel = '56789'.

append it_zemp.

loop at it_zemp.

INDEX = ROW_MAX * ( SY-tabix - 1 ) + 1.

DO 4 TIMES.

ASSIGN COMPONENT SY-INDEX OF STRUCTURE it_zemp TO <NAME>.

CALL METHOD OF SHEET 'Cells' = cells EXPORTING #1 = INDEX.

SET PROPERTY OF Cells 'Value' = <NAME>.

*SET PROPERTY OF Cells 'visible' = 1.

add 1 to index.

ENDDO.

ENDloop.

ENDFORM. " FILL_SHEET

&----

---

*& Form errors

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM errors.

write 😕 sy-msgli.

ENDFORM.

Please reward if useful.

[Former Member]

Hi,

Thank you very much, it is very helpful.

Thanks.

[Former Member]

Hello Dinesh,

I have tested your program - wow this is great and really veeery helpfull!

One question: How could I set the document to "ReadOnly" ? (With a document created with method "Add" not "Open".)

Somehow I cannot figure out how to use the method "Protect" of Object Document. (I am absolute beginner in VisualBasic)

Could you give me a hint ?

Thanks in advance,

Johannes

[Former Member]

hi Khaled,

I am also having the same requirement.

I am very new to oops programing and interfacing.

Could you plz send me the sample code for this requirement?

Please send me the same at [email protected].

many thanks foryour time and efforts.

Vinod Doshi.