Skip to Content
author's profile photo Former Member
Former Member

OLE2 issue in converting excel macro to abap code

Hello Folks,

Could you please assist me converting below Marcro to ABAP code.

Here's my VBA Code (Excel macro code):

Sub Macro1()

'

' Macro1 Macro

'

'
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Regards

Aradhya.J.M.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 06, 2013 at 06:31 AM

    Hi Aradhya,

    DATA: H_EXCEL TYPE OLE2_OBJECT,

    GS_CELL1 TYPE OLE2_OBJECT,

    GS_CELL2 TYPE OLE2_OBJECT,

    GS_CELLS TYPE OLE2_OBJECT.

    CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

    EXPORTING

    #1 = 1 " 1st Row

    #2 = 1. " 1st Column

    ==============================================================

    CALL METHOD OF H_EXCEL 'Cells' = GS_CELL2

    EXPORTING

    #1 = 5 " 5th Row

    #2 = 4. " 4th Column

    ==========================================================

    *

    CALL METHOD OF H_EXCEL 'Range' = GS_CELLS " Select Range Area using above row and column

    EXPORTING

    #1 = GS_CELL1

    #2 = GS_CELL2.

    CALL METHOD OF GS_CELLS 'Select' . " Select

    ========================================================

    CALL METHOD OF H_EXCEL 'AutoFit'.

    Regard's

    Smruti


    test321.png (12.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Aradhya,

      is it ur Unicode System ? if "Yes" then Check this SAP Sample Standard Program using OOABAP ,

      BCS_EXAMPLE_7 . If this Program getting any error , Please check this Notes:

      Note 1151258 - Error when sending Excel attachments

      Note 1844477 - "Error occurred during transmission - return code: <XERR_BCS>" with report BCS_EXAMPLE_7

      Regard's

      Smruti

  • author's profile photo Former Member
    Former Member
    Posted on May 27, 2013 at 10:26 AM

    Refer this,

    *&---------------------------------------------------------------------*
    *& Report ZBC_SHA_SALES_INFO_IN_EXCEL
    *&
    *&---------------------------------------------------------------------*
    *&
    *&
    *&---------------------------------------------------------------------*

    REPORT zbc_sha_sales_info_in_excel.
    INCLUDE <icon>.
    * EXCEL sheet using OLE automation.
    INCLUDE ole2incl.
    INCLUDE excel__c.
    *----------------------------------------------------------------------*
    * Customer Database Tables
    *----------------------------------------------------------------------*
    TABLES: vbak, "Sales Header
    vbap, "Sales Item
    likp, "Delivery Header
    lips, "SD document: Delivery: Item data
    vttk, "Shipment Header
    vttp, "Shipment Item
    vbrk, "Billing Document: Header Data
    vbrp, "Billing Document: Item Data
    kna1. "General Data in Customer Master
    *----------------------------------------------------------------------*
    * Internal Tables
    *----------------------------------------------------------------------*
    DATA: BEGIN OF it_output OCCURS 10 ,
    vbeln LIKE vbak-vbeln, "Sales Document
    audat LIKE vbak-audat, "Document Date (Date Received/Sent)
    netwr LIKE vbak-netwr, "Net Value of the Sales Order
    waerk LIKE vbak-waerk, "SD Document Currency
    kunnr LIKE vbak-kunnr, "Sold-to party
    name1 LIKE kna1-name1, "Name 1
    posnr LIKE vbap-posnr, "Sales Document Item
    matnr LIKE vbap-matnr, "material number
    maktx LIKE makt-maktx, "material desc.
    vbeln1 LIKE likp-vbeln, "Delivery
    tknum LIKE vttp-tknum, "Shipment Number
    distz LIKE vttk-distz, "Distance
    medst LIKE vttk-medst, "Unit of measure for distance
    route LIKE vttk-route, "Route
    vbeln2 LIKE vbrk-vbeln, "Billing Document
    status LIKE icon-name, "Status
    selkz(1).
    DATA: END OF it_output.

    DATA: BEGIN OF it_vbak OCCURS 10,
    vbeln LIKE vbak-vbeln,
    kunnr LIKE vbak-kunnr,
    waerk LIKE vbak-waerk,
    audat LIKE vbak-audat,
    netwr LIKE vbak-netwr,
    END OF it_vbak.

    DATA: BEGIN OF it_vbap OCCURS 10,
    vbeln LIKE vbap-vbeln,
    posnr LIKE vbap-posnr,
    matnr LIKE vbap-matnr,
    END OF it_vbap.

    DATA: BEGIN OF it_makt OCCURS 10,
    matnr LIKE makt-matnr,
    maktx LIKE makt-maktx,
    END OF it_makt.

    DATA: BEGIN OF it_kna1 OCCURS 10,
    kunnr LIKE kna1-kunnr,
    name1 LIKE kna1-name1,
    END OF it_kna1.

    DATA: BEGIN OF it_lips OCCURS 10,
    vbeln LIKE lips-vbeln,
    vgbel LIKE lips-vgbel,
    vgpos LIKE lips-vgpos,
    matnr LIKE lips-matnr,
    END OF it_lips.

    DATA: BEGIN OF it_likp OCCURS 10,
    vbeln LIKE likp-vbeln,
    END OF it_likp.

    DATA: BEGIN OF it_vttp OCCURS 10,
    vbeln LIKE vttp-vbeln,
    tknum LIKE vttp-tknum,

    END OF it_vttp.

    DATA: BEGIN OF it_vbrp OCCURS 10,
    vbeln LIKE vbrp-vbeln,
    vgbel LIKE vbrp-vgbel,
    END OF it_vbrp.

    DATA: BEGIN OF it_vbrk OCCURS 10,
    vbeln LIKE vbrk-vbeln,
    END OF it_vbrk.

    DATA: BEGIN OF it_vttk OCCURS 10,
    tknum LIKE vttk-tknum,
    route LIKE vttk-route,
    distz LIKE vttk-distz,
    medst LIKE vttk-medst,
    END OF it_vttk.

    DATA: it_output1 LIKE TABLE OF it_output WITH HEADER LINE.
    *&---------------------------------------------------------------------*
    *& Variables
    *&---------------------------------------------------------------------*
    DATA: w_excel TYPE ole2_object, "Holds the excel application
    w_wbooks TYPE ole2_object, "Holds Work Books
    w_wbook TYPE ole2_object, "Holds Work Book
    w_cell TYPE ole2_object, "Holds Cell
    w_cell1 TYPE ole2_object, "Holds Cell
    w_cell2 TYPE ole2_object, "Holds Cell
    w_format TYPE ole2_object, "Object for format
    w_font TYPE ole2_object,
    w_wsheets TYPE ole2_object, "Holds Active Sheet
    w_wsheet TYPE ole2_object, "Holds Active Sheet
    w_range TYPE ole2_object, "To select a range
    w_interior TYPE ole2_object,
    w_formatconditions TYPE ole2_object,
    w_border TYPE ole2_object,
    w_row TYPE i,
    w_col TYPE i,
    w_size TYPE i.
    *----------------------------------------------------------------------*
    * ALV GRID Variables
    *----------------------------------------------------------------------*
    TYPE-POOLS: slis.

    DATA: gd_title TYPE lvc_title,
    gd_fieldcat TYPE slis_t_fieldcat_alv ,
    wa_fieldcat TYPE slis_fieldcat_alv,
    gd_layout TYPE slis_layout_alv,
    gd_sort TYPE slis_t_sortinfo_alv,
    wa_sort LIKE LINE OF gd_sort,
    gd_events TYPE slis_t_event,
    gd_extab TYPE slis_t_extab WITH HEADER LINE,
    wa_extab TYPE slis_t_extab WITH HEADER LINE,
    gd_event_exit TYPE slis_t_event_exit WITH HEADER LINE,
    wa_event TYPE slis_alv_event,
    wa_event_exit TYPE slis_t_event_exit,
    gd_save(1),
    gd_repid LIKE sy-repid,
    gd_dynnr LIKE sy-dynnr,
    gd_param1 LIKE sy-ucomm,
    gd_top TYPE slis_t_listheader,
    wa_top TYPE slis_listheader.
    *----------------------------------------------------------------------*
    * Selection screen
    *----------------------------------------------------------------------*
    SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
    SELECT-OPTIONS: s_vbeln FOR vbak-vbeln, " Sales Document
    s_vkorg FOR vbak-vkorg. " Sales Organization
    SELECTION-SCREEN END OF BLOCK b1.
    *----------------------------------------------------------------------*
    * MAIN PROGRAM
    *----------------------------------------------------------------------*
    gd_repid = sy-repid.

    PERFORM fetch_assign_data.

    IF it_output[] IS NOT INITIAL.

    PERFORM display_data.
    ENDIF.
    *&---------------------------------------------------------------------*
    *& Form fetch_assign_data
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM fetch_assign_data .
    SELECT vbeln
    kunnr
    waerk
    audat
    netwr FROM vbak
    INTO TABLE it_vbak
    WHERE vbeln IN s_vbeln
    AND vkorg IN s_vkorg.

    IF it_vbak[] IS NOT INITIAL.

    SELECT vbeln
    posnr
    matnr FROM vbap
    INTO TABLE it_vbap
    FOR ALL ENTRIES IN it_vbak
    WHERE vbeln EQ it_vbak-vbeln.

    SELECT kunnr
    name1 FROM kna1
    INTO TABLE it_kna1
    FOR ALL ENTRIES IN it_vbak
    WHERE kunnr EQ it_vbak-kunnr.

    IF it_vbap[] IS NOT INITIAL .

    SELECT matnr
    maktx FROM makt
    INTO TABLE it_makt
    FOR ALL ENTRIES IN it_vbap
    WHERE matnr EQ it_vbap-matnr.

    SELECT vbeln
    vgbel
    vgpos
    matnr FROM lips
    INTO TABLE it_lips
    FOR ALL ENTRIES IN it_vbap
    WHERE vgbel EQ it_vbap-vbeln
    AND vgpos EQ it_vbap-posnr.

    IF it_lips[] IS NOT INITIAL.

    SELECT vbeln FROM likp
    INTO TABLE it_likp
    FOR ALL ENTRIES IN it_lips
    WHERE vbeln EQ it_lips-vbeln.

    SELECT vbeln
    tknum FROM vttp
    INTO TABLE it_vttp
    FOR ALL ENTRIES IN it_lips
    WHERE vbeln EQ it_lips-vbeln.

    IF it_vttp[] IS NOT INITIAL .

    SELECT tknum
    route
    distz
    medst FROM vttk
    INTO TABLE it_vttk
    FOR ALL ENTRIES IN it_vttp
    WHERE tknum EQ it_vttp-tknum.

    ENDIF.

    SELECT vbeln
    vgbel FROM vbrp
    INTO TABLE it_vbrp
    FOR ALL ENTRIES IN it_lips
    WHERE vgbel EQ it_lips-vbeln.

    IF it_vbrp[] IS NOT INITIAL.

    SELECT vbeln FROM vbrk
    INTO TABLE it_vbrk
    FOR ALL ENTRIES IN it_vbrp
    WHERE vbeln EQ it_vbrp-vbeln
    AND vbtyp EQ 'M'.
    ENDIF. " IF it_vbrp[] IS NOT INITIAL.

    ENDIF. " IF it_lips[] IS NOT INITIAL.

    ENDIF. " IF it_vbap[] IS NOT INITIAL .

    ENDIF . " IF it_vbak[] IS NOT INITIAL.

    CLEAR it_output[].

    LOOP AT it_vbap.

    CLEAR it_output.
    it_output-status = icon_red_light.
    it_output-posnr = it_vbap-posnr. "Sales Doc. Item
    it_output-matnr = it_vbap-matnr. "Material No.

    READ TABLE it_makt WITH KEY matnr = it_vbap-matnr.
    IF sy-subrc EQ 0.
    it_output-maktx = it_makt-maktx. "Material Description
    ENDIF.

    READ TABLE it_vbak WITH KEY vbeln = it_vbap-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln = it_vbak-vbeln. "Sales doc. no.
    it_output-kunnr = it_vbak-kunnr. "Sold To Party
    it_output-waerk = it_vbak-waerk. "SD Doc. Currency
    it_output-audat = it_vbak-audat. "Document Date
    it_output-netwr = it_vbak-netwr. "Net Value

    READ TABLE it_kna1 WITH KEY kunnr = it_vbak-kunnr.
    IF sy-subrc EQ 0.
    it_output-name1 = it_kna1-name1. "Customer
    ENDIF.

    READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
    vgpos = it_vbap-posnr
    matnr = it_vbap-matnr.
    IF sy-subrc EQ 0.
    LOOP AT it_lips WHERE vgbel EQ it_vbap-vbeln AND
    vgpos EQ it_vbap-posnr AND
    matnr EQ it_vbap-matnr.

    * READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
    * vgpos = it_vbap-posnr
    * matnr = it_vbap-matnr.

    * IF sy-subrc EQ 0.
    it_output-status = icon_yellow_light.
    * Read Delivery Header
    READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln1 = it_likp-vbeln. "Delivery

    ** loop at shipment item
    LOOP AT it_vttp WHERE vbeln EQ it_likp-vbeln.

    * READ TABLE it_vttp WITH KEY vbeln = it_likp-vbeln.

    * IF sy-subrc EQ 0.

    * Read Shipment Header
    READ TABLE it_vttk WITH KEY tknum = it_vttp-tknum.
    IF sy-subrc EQ 0.
    it_output-tknum = it_vttk-tknum. "Shipment
    it_output-route = it_vttk-route. "Route
    it_output-distz = it_vttk-distz. "Distance
    it_output-medst = it_vttk-medst. "Unit Of Distance
    ENDIF.

    ENDLOOP.
    * ENDIF.
    ** Loop At Billing Item
    LOOP AT it_vbrp WHERE vgbel EQ it_lips-vbeln.

    * READ TABLE it_vbrp WITH KEY vgbel = it_lips-vbeln.
    *
    * IF sy-subrc EQ 0.

    * Read Billing Header
    READ TABLE it_vbrk WITH KEY vbeln = it_vbrp-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln2 = it_vbrk-vbeln. "Billing
    ENDIF.

    IF it_output-vbeln1 IS NOT INITIAL AND
    it_output-vbeln2 IS NOT INITIAL.
    it_output-status = icon_green_light.
    ENDIF.

    * ENDIF.
    ENDLOOP."Loop it_vbrp

    ENDIF. "sy-subrc EQ 0.

    APPEND it_output.
    ENDLOOP.

    ELSE.
    APPEND it_output.
    ENDIF.
    ENDIF.
    ENDLOOP.

    ENDFORM. " fetch_assign_data
    *&---------------------------------------------------------------------*
    *& Form display_data
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM display_data .
    CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
    i_program_name = gd_repid
    i_internal_tabname = 'IT_OUTPUT'
    i_inclname = gd_repid
    CHANGING
    ct_fieldcat = gd_fieldcat.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

    PERFORM manipulate_events.
    PERFORM manipulate_fieldcatlog.
    PERFORM manipulate_layout.

    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
    i_callback_program = gd_repid
    i_callback_pf_status_set = 'PF_STATUS'
    i_callback_user_command = 'USER_COMMAND'
    is_layout = gd_layout
    it_fieldcat = gd_fieldcat
    TABLES
    t_outtab = it_output.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.
    ENDFORM. " display_data
    *&---------------------------------------------------------------------*
    *& Form manipulate_events
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_events .
    CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
    EXPORTING
    i_list_type = 0
    IMPORTING
    et_events = gd_events
    EXCEPTIONS
    list_type_wrong = 1
    OTHERS = 2.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

    ELSE.

    READ TABLE gd_events WITH KEY name = 'USER_COMMAND' INTO wa_event.
    wa_event-form = 'USER_COMMAND1'.
    MODIFY gd_events FROM wa_event INDEX sy-tabix.

    ENDIF. "sy-subrc <> 0
    ENDFORM. " manipulate_events
    *&---------------------------------------------------------------------*
    *& Form manipulate_fieldcatlog
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_fieldcatlog .

    LOOP AT gd_fieldcat INTO wa_fieldcat.

    CASE wa_fieldcat-fieldname.

    WHEN 'VBELN' OR "Sales Doc. No.
    'VBELN1' OR "Delivery
    'VBELN2' OR "Billing
    'KUNNR' OR "Sold To Party
    'TKNUM' OR "Shipment
    'MATNR'. "Material No.

    wa_fieldcat-hotspot = 'X'.

    WHEN 'SELKZ'.
    wa_fieldcat-no_out = 'X'.

    WHEN 'NETWR'.
    IF sy-ucomm EQ 'EDIT'.
    wa_fieldcat-edit = 'X'.
    ENDIF.
    ENDCASE.

    MODIFY gd_fieldcat FROM wa_fieldcat.

    ENDLOOP.
    ENDFORM. " manipulate_fieldcatlog
    *&---------------------------------------------------------------------*
    *& Form manipulate_layout
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_layout .
    gd_layout-box_fieldname = 'SELKZ'.
    gd_layout-box_tabname = 'IT_OUTPUT'.
    gd_layout-colwidth_optimize = 'X'.
    ENDFORM. " manipulate_layout
    * Form user_command
    *&---------------------------------------------------------------------
    *
    * text
    *----------------------------------------------------------------------
    *
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------
    FORM user_command USING param1 TYPE sy-ucomm
    param2 TYPE slis_selfield.

    CASE param1.

    WHEN '&IC1'.
    IF param2-fieldname = 'VBELN'.
    SET PARAMETER ID 'AUN' FIELD param2-value.
    CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN'.

    IF param2-fieldname = 'KUNNR'.
    SET PARAMETER ID 'KUN' FIELD param2-value.
    CALL TRANSACTION 'XD03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'KUNNR'.

    IF param2-fieldname = 'VBELN1'.
    SET PARAMETER ID 'VL' FIELD param2-value.
    CALL TRANSACTION 'VL03N' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN1'.

    IF param2-fieldname = 'TKNUM'.
    SET PARAMETER ID 'TNR' FIELD param2-value.
    CALL TRANSACTION 'VT03N' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'TKNUM'.

    IF param2-fieldname = 'VBELN2'.
    SET PARAMETER ID 'VF' FIELD param2-value.
    CALL TRANSACTION 'VF03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN2'.


    IF param2-fieldname = 'MATNR'.
    SET PARAMETER ID 'MAT' FIELD param2-value.
    CALL TRANSACTION 'MM03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'MATNR'.

    WHEN 'EDIT'.
    READ TABLE it_output WITH KEY selkz = 'X'.
    DELETE it_output[] WHERE selkz NE 'X'.
    it_output1[] = it_output[].
    PERFORM display_data.

    WHEN 'SAVE'.

    LOOP AT it_output.


    UPDATE vbak SET netwr = it_output-netwr
    WHERE vbeln EQ it_output-vbeln
    AND kunnr EQ it_output-kunnr.
    IF sy-subrc EQ 0.
    MESSAGE 'Value Updated Sucessfully' TYPE 'S'.
    ELSE.
    MESSAGE 'Double click on the changed row' TYPE 'I'.
    ENDIF.
    ENDLOOP.

    WHEN 'EXCEL'.

    * READ TABLE it_output WITH KEY selkz = 'X'.
    * IF sy-subrc EQ 0.
    PERFORM open_excel.
    PERFORM add_worksheet.
    * ENDIF.

    WHEN 'BACK'.
    LEAVE TO SCREEN 1000.

    ENDCASE. "param1

    param2-refresh = 'X'.
    ENDFORM. "user_command
    *&---------------------------------------------------------------------*
    *& Form pf_status
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->RT_EXTAB text
    *----------------------------------------------------------------------*
    FORM pf_status USING rt_extab TYPE slis_t_extab.

    DATA: gd_index TYPE i.

    wa_extab-fcode = 'EDIT'.
    APPEND wa_extab TO gd_extab.
    CLEAR wa_extab.

    wa_extab-fcode = 'EXCEL'.
    APPEND wa_extab TO gd_extab.
    CLEAR wa_extab.

    IF sy-ucomm EQ 'EDIT' .

    SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

    ELSEIF sy-index EQ 0 AND sy-ucomm EQ ' '.

    SET PF-STATUS 'ZSTATUS'.

    ELSEIF sy-ucomm EQ 'BACK'.
    SET PF-STATUS 'ZSTATUS'.


    ELSEIF sy-index EQ 1 OR sy-ucomm EQ 'SAVE'.

    SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

    ENDIF. " IF sy-ucomm EQ 'EDIT' .
    ENDFORM. "pf_status
    *&---------------------------------------------------------------------*
    *& Form open_excel
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM open_excel .
    DATA: l_cnt TYPE i.

    CREATE OBJECT w_excel 'EXCEL.APPLICATION'.
    IF sy-subrc NE 0.
    WRITE : / 'Error CREATE OBJECT'.
    ENDIF. " IF sy-subrc NE 0.

    SET PROPERTY OF w_excel 'Visible' = 1.
    IF sy-subrc NE 0.
    WRITE : / 'Error MAKE EXCEL VISIBLE'.
    ENDIF. " IF sy-subrc NE 0.

    CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
    IF sy-subrc NE 0.
    WRITE : / 'Error GET WORKBOOKS'.
    ENDIF. " IF sy-subrc NE 0.

    CALL METHOD OF w_wbooks 'ADD' = w_wbook.
    IF sy-subrc NE 0.
    WRITE : / 'Error ADD TEMPLATE'.
    ENDIF. " IF sy-subrc NE 0.

    GET PROPERTY OF w_wbook 'Worksheets' = w_wsheets.
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    SET PROPERTY OF w_excel 'DISPLAYALERTS' = 0.

    GET PROPERTY OF w_wsheets 'Count' = l_cnt.

    l_cnt = l_cnt - 1.

    * Delete unwanted worksheets
    DO l_cnt TIMES.
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    CALL METHOD OF w_wsheet 'DELETE'.
    ENDDO. " DO l_cnt TIMES.
    ENDFORM. " open_excel
    *&---------------------------------------------------------------------*
    *& Form add_worksheet
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM add_worksheet .
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    CALL METHOD OF w_wsheets 'Add' = w_wsheet.

    w_size = 15.

    PERFORM fill_ccell USING 1 8 'Sales Details' 'X' '' '' '' '' 200.

    PERFORM merge_cell USING 1 8 1 9.

    PERFORM set_border USING 1 8 1 9 10 'X'.

    PERFORM set_color USING 1 8 1 9 'LV'.

    "Distance
    "Unit of measure for distance
    "Route
    "Billing Document
    "Status


    PERFORM fill_ccell
    USING 2 1 'Sales Document' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 2 'Document Date' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 3 'Net Value ' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 4 'SD Document Currency' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 5 'Sold-to party' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 6 'Name 1' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 7 'Sales Document Item' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 8 'Material number' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 9 'Material Desc.' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 10 'Delivery' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 11 'Shipment Number' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 12 'Distance' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 13 'Unit of measure for distance' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 14 'Route' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 15 'Billing Document' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 16 'Status' 'X' '' '' '' '' 000255000.

    w_row = 2.
    w_size = 10.

    LOOP AT it_output WHERE selkz EQ 'X'.
    w_row = w_row + 1.

    PERFORM fill_ccell
    USING w_row 1 it_output-vbeln 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 2 it_output-audat 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 3 it_output-netwr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 4 it_output-waerk 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 5 it_output-kunnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 6 it_output-name1 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 7 it_output-posnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 8 it_output-matnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 9 it_output-maktx 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 10 it_output-vbeln1 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 11 it_output-tknum 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 12 it_output-distz 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 13 it_output-medst 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 14 it_output-route 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 15 it_output-vbeln2 'X' '' '' '' '' 111111111.

    * IF it_output-status EQ icon_red_light.
    * it_output-status = 0.
    * ELSEIF it_output-status EQ icon_yellow_light.
    * it_output-status = 1.
    * ELSEIF it_output-status EQ icon_green_light.
    * it_output-status = 2.
    * ENDIF.

    * PERFORM fill_ccell
    * USING w_row 16 it_output-status '' '' '' '' '' 111111111.

    CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
    **
    IF it_output-status EQ icon_red_light.
    PERFORM set_color USING w_row 16 w_row 16 'RD'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ELSEIF it_output-status EQ icon_green_light.
    PERFORM set_color USING w_row 16 w_row 16 'GR'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ELSEIF it_output-status EQ icon_yellow_light.
    PERFORM set_color USING w_row 16 w_row 16 'YL'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ENDIF.

    * PERFORM fill_ccell
    * USING w_row 16 it_output-status 'X' '' '' '' '' 111111111.

    ENDLOOP.

    ENDFORM. " add_worksheet
    *&---------------------------------------------------------------------*
    *& Form fill_ccell
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1481 text
    * -->P_1482 text
    * -->P_1483 text
    * -->P_1484 text
    * -->P_1485 text
    * -->P_1486 text
    * -->P_200 text
    *----------------------------------------------------------------------*
    FORM fill_ccell USING i_row TYPE i
    i_col TYPE i
    i_value
    i_fontbold
    i_digit
    i_wraptext
    i_horizon_align
    i_vertical_align
    i_color.

    DATA: l_str TYPE string.

    CALL METHOD OF w_excel 'Cells' = w_cell
    EXPORTING
    #1 = i_row
    #2 = i_col.

    PERFORM err_hdl.

    SET PROPERTY OF w_cell 'VALUE' = i_value.
    PERFORM err_hdl.

    IF i_fontbold = 'X'.
    GET PROPERTY OF w_cell 'Font' = w_font.
    SET PROPERTY OF w_font 'Bold' = 1.
    SET PROPERTY OF w_font 'Size' = w_size.
    SET PROPERTY OF w_font 'Color' = i_color.
    ENDIF. " IF i_fontbold = 'X'.

    IF NOT i_wraptext IS INITIAL.
    SET PROPERTY OF w_cell 'WrapText' = 1.
    ENDIF. " IF NOT i_wraptext IS INITIAL.

    IF NOT i_horizon_align IS INITIAL.

    IF i_horizon_align = 'L'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlleft.
    ELSEIF i_horizon_align = 'R'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlright.
    ELSEIF i_horizon_align = 'C'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlcenter.
    ENDIF. " IF i_horizon_align = 'L'.

    ENDIF. " IF NOT i_horizon_align IS INITIAL.

    IF NOT i_vertical_align IS INITIAL.

    IF i_vertical_align = 'T'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xltop.
    ELSEIF i_vertical_align = 'B'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xlbottom.
    ELSEIF i_vertical_align = 'C'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xlcenter.
    ENDIF. " IF i_vertical_align = 'T'.

    ENDIF. " IF NOT i_vertical_align IS INITIAL.

    * To set number format for cell
    IF i_digit <> ''.

    IF i_value IS INITIAL AND i_digit <> '%'.
    SET PROPERTY OF w_cell 'VALUE' = ''.
    ELSE.

    IF i_digit = '1'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,###.0 '.
    ELSEIF i_digit = '2'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00 '.
    ELSEIF i_digit = '%'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00% '.
    ELSE.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,### '.
    ENDIF.

    ENDIF. " IF i_value IS INITIAL AND i_digit <> '%'.

    ENDIF. " IF i_digit <> ''.

    ENDFORM. " fill_ccell
    *&---------------------------------------------------------------------*
    *& Form merge_cell
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    *----------------------------------------------------------------------*
    FORM merge_cell USING i_row1 i_col1
    i_row2 i_col2.
    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    CALL METHOD OF w_range 'Merge'.
    ENDFORM. " merge_cell
    *&---------------------------------------------------------------------*
    *& Form set_border
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    * -->P_8 text
    * -->P_1503 text
    *----------------------------------------------------------------------*
    FORM set_border USING i_row1
    i_col1
    i_row2
    i_col2
    i_thickness
    i_allborders.

    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING
    #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    IF i_allborders IS INITIAL.

    CALL METHOD OF w_range 'BorderAround'
    EXPORTING #1 = 1 "Continuous line
    #2 = i_thickness. "Thickness: 1 - Normal, 4 - Thick
    ELSE.

    GET PROPERTY OF w_range 'Borders' = w_border.
    SET PROPERTY OF w_border 'LineStyle' = '1'.
    SET PROPERTY OF w_border 'Weight' = i_thickness.

    ENDIF. " IF i_allborders IS INITIAL.
    ENDFORM. " set_border
    *&---------------------------------------------------------------------*
    *& Form set_color
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    * -->P_1511 text
    *----------------------------------------------------------------------*
    FORM set_color USING i_row1
    i_col1
    i_row2
    i_col2
    i_color.

    DATA: l_colorindex TYPE i.

    CASE i_color.

    WHEN 'BK'.
    l_colorindex = 1. "Black

    WHEN 'BR'.
    l_colorindex = 53. "Brown

    WHEN 'OG'.
    l_colorindex = 52. "Olive Green

    WHEN 'DG'.
    l_colorindex = 51. "Dark Green

    WHEN 'DT'.
    l_colorindex = 49. "Dark Teal

    WHEN 'DB'.
    l_colorindex = 11. "Dark Blue

    WHEN 'ID'.
    l_colorindex = 55. "Indigo

    WHEN 'G4'.
    l_colorindex = 56. "Gray 80%

    WHEN 'DR'.
    l_colorindex = 9. "Dark Red

    WHEN 'OR'.
    l_colorindex = 46. "Orange

    WHEN 'DY'.
    l_colorindex = 12. "Dark Yellow

    WHEN 'GR'.
    l_colorindex = 10. "Green

    WHEN 'TL'.
    l_colorindex = 14. "Teal

    WHEN 'BL'.
    l_colorindex = 5. "Blue

    WHEN 'BY'.
    l_colorindex = 47. "Blue Gray

    WHEN 'G3'.
    l_colorindex = 16. "Gray 50%

    WHEN 'RD'.
    l_colorindex = 3. "Red

    WHEN 'LO'.
    l_colorindex = 45. "Light Orange

    WHEN 'LI'.
    l_colorindex = 43. "Lime

    WHEN 'SG'.
    l_colorindex = 50. "Sea Green

    WHEN 'AQ'.
    l_colorindex = 42. "Aqua

    WHEN 'LB'.
    l_colorindex = 41. "Light Blue

    WHEN 'VL'.
    l_colorindex = 13. "Violet

    WHEN 'G2'.
    l_colorindex = 48. "Gray 40%

    WHEN 'PK'.
    l_colorindex = 7. "Pink

    WHEN 'GD'.
    l_colorindex = 44. "Gold

    WHEN 'YL'.
    l_colorindex = 6. "Yellow

    WHEN 'BG'.
    l_colorindex = 4. "Bright Green

    WHEN 'TQ'.
    l_colorindex = 8. "Turquoise

    WHEN 'SB'.
    l_colorindex = 33. "Sky Blue

    WHEN 'PL'.
    l_colorindex = 54. "Plum

    WHEN 'G1'.
    l_colorindex = 15. "Gray 25%

    WHEN 'RS'.
    l_colorindex = 38. "Rose

    WHEN 'TN'.
    l_colorindex = 40. "Tan

    WHEN 'LY'.
    l_colorindex = 36. "Light Yellow

    WHEN 'LG'.
    l_colorindex = 35. "Light Green

    WHEN 'LT'.
    l_colorindex = 34. "Light Turquoise

    WHEN 'PB'.
    l_colorindex = 37. "Pale Blue

    WHEN 'LV'.
    l_colorindex = 39. "Lavender

    WHEN 'WH'.
    l_colorindex = 2. "White

    WHEN OTHERS.
    l_colorindex = 2. "White

    ENDCASE. " CASE i_color.

    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    * GET PROPERTY OF w_excel 'FormatConditions' = w_formatconditions.

    * SET PROPERTY OF w_formatconditions 'ReverseOrder' = 0.
    * SET PROPERTY OF w_formatconditions 'ShowIconOnly' = 1.
    * SET PROPERTY OF w_formatconditions 'IconSet' =
    * 'xl3trafficlights1'.
    *
    * SET PROPERTY OF w_formatconditions 'iconcriteria' = 2.
    * SET PROPERTY OF w_formatconditions 'Type' =
    *'xlconditionvaluenumber'.
    * SET PROPERTY OF w_formatconditions 'Value' = 2.
    * SET PROPERTY OF w_formatconditions 'Operator' = 5.


    GET PROPERTY OF w_range 'Interior' = w_interior.

    SET PROPERTY OF w_interior 'ColorIndex' = l_colorindex.

    ENDFORM. " set_color
    *&---------------------------------------------------------------------*
    *& Form err_hdl
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM err_hdl .
    IF sy-subrc <> 0.
    WRITE: / 'OLE-Automation Error:'(010), sy-subrc.
    STOP.
    ENDIF. " IF sy-subrc <> 0.
    ENDFORM. " err_hdl*&---------------------------------------------------------------------*
    *& Report ZBC_SHA_SALES_INFO_IN_EXCEL
    *&
    *&---------------------------------------------------------------------*
    *&
    *&
    *&---------------------------------------------------------------------*

    REPORT zbc_sha_sales_info_in_excel.
    INCLUDE <icon>.
    * EXCEL sheet using OLE automation.
    INCLUDE ole2incl.
    INCLUDE excel__c.
    *----------------------------------------------------------------------*
    * Customer Database Tables
    *----------------------------------------------------------------------*
    TABLES: vbak, "Sales Header
    vbap, "Sales Item
    likp, "Delivery Header
    lips, "SD document: Delivery: Item data
    vttk, "Shipment Header
    vttp, "Shipment Item
    vbrk, "Billing Document: Header Data
    vbrp, "Billing Document: Item Data
    kna1. "General Data in Customer Master
    *----------------------------------------------------------------------*
    * Internal Tables
    *----------------------------------------------------------------------*
    DATA: BEGIN OF it_output OCCURS 10 ,
    vbeln LIKE vbak-vbeln, "Sales Document
    audat LIKE vbak-audat, "Document Date (Date Received/Sent)
    netwr LIKE vbak-netwr, "Net Value of the Sales Order
    waerk LIKE vbak-waerk, "SD Document Currency
    kunnr LIKE vbak-kunnr, "Sold-to party
    name1 LIKE kna1-name1, "Name 1
    posnr LIKE vbap-posnr, "Sales Document Item
    matnr LIKE vbap-matnr, "material number
    maktx LIKE makt-maktx, "material desc.
    vbeln1 LIKE likp-vbeln, "Delivery
    tknum LIKE vttp-tknum, "Shipment Number
    distz LIKE vttk-distz, "Distance
    medst LIKE vttk-medst, "Unit of measure for distance
    route LIKE vttk-route, "Route
    vbeln2 LIKE vbrk-vbeln, "Billing Document
    status LIKE icon-name, "Status
    selkz(1).
    DATA: END OF it_output.

    DATA: BEGIN OF it_vbak OCCURS 10,
    vbeln LIKE vbak-vbeln,
    kunnr LIKE vbak-kunnr,
    waerk LIKE vbak-waerk,
    audat LIKE vbak-audat,
    netwr LIKE vbak-netwr,
    END OF it_vbak.

    DATA: BEGIN OF it_vbap OCCURS 10,
    vbeln LIKE vbap-vbeln,
    posnr LIKE vbap-posnr,
    matnr LIKE vbap-matnr,
    END OF it_vbap.

    DATA: BEGIN OF it_makt OCCURS 10,
    matnr LIKE makt-matnr,
    maktx LIKE makt-maktx,
    END OF it_makt.

    DATA: BEGIN OF it_kna1 OCCURS 10,
    kunnr LIKE kna1-kunnr,
    name1 LIKE kna1-name1,
    END OF it_kna1.

    DATA: BEGIN OF it_lips OCCURS 10,
    vbeln LIKE lips-vbeln,
    vgbel LIKE lips-vgbel,
    vgpos LIKE lips-vgpos,
    matnr LIKE lips-matnr,
    END OF it_lips.

    DATA: BEGIN OF it_likp OCCURS 10,
    vbeln LIKE likp-vbeln,
    END OF it_likp.

    DATA: BEGIN OF it_vttp OCCURS 10,
    vbeln LIKE vttp-vbeln,
    tknum LIKE vttp-tknum,

    END OF it_vttp.

    DATA: BEGIN OF it_vbrp OCCURS 10,
    vbeln LIKE vbrp-vbeln,
    vgbel LIKE vbrp-vgbel,
    END OF it_vbrp.

    DATA: BEGIN OF it_vbrk OCCURS 10,
    vbeln LIKE vbrk-vbeln,
    END OF it_vbrk.

    DATA: BEGIN OF it_vttk OCCURS 10,
    tknum LIKE vttk-tknum,
    route LIKE vttk-route,
    distz LIKE vttk-distz,
    medst LIKE vttk-medst,
    END OF it_vttk.

    DATA: it_output1 LIKE TABLE OF it_output WITH HEADER LINE.
    *&---------------------------------------------------------------------*
    *& Variables
    *&---------------------------------------------------------------------*
    DATA: w_excel TYPE ole2_object, "Holds the excel application
    w_wbooks TYPE ole2_object, "Holds Work Books
    w_wbook TYPE ole2_object, "Holds Work Book
    w_cell TYPE ole2_object, "Holds Cell
    w_cell1 TYPE ole2_object, "Holds Cell
    w_cell2 TYPE ole2_object, "Holds Cell
    w_format TYPE ole2_object, "Object for format
    w_font TYPE ole2_object,
    w_wsheets TYPE ole2_object, "Holds Active Sheet
    w_wsheet TYPE ole2_object, "Holds Active Sheet
    w_range TYPE ole2_object, "To select a range
    w_interior TYPE ole2_object,
    w_formatconditions TYPE ole2_object,
    w_border TYPE ole2_object,
    w_row TYPE i,
    w_col TYPE i,
    w_size TYPE i.
    *----------------------------------------------------------------------*
    * ALV GRID Variables
    *----------------------------------------------------------------------*
    TYPE-POOLS: slis.

    DATA: gd_title TYPE lvc_title,
    gd_fieldcat TYPE slis_t_fieldcat_alv ,
    wa_fieldcat TYPE slis_fieldcat_alv,
    gd_layout TYPE slis_layout_alv,
    gd_sort TYPE slis_t_sortinfo_alv,
    wa_sort LIKE LINE OF gd_sort,
    gd_events TYPE slis_t_event,
    gd_extab TYPE slis_t_extab WITH HEADER LINE,
    wa_extab TYPE slis_t_extab WITH HEADER LINE,
    gd_event_exit TYPE slis_t_event_exit WITH HEADER LINE,
    wa_event TYPE slis_alv_event,
    wa_event_exit TYPE slis_t_event_exit,
    gd_save(1),
    gd_repid LIKE sy-repid,
    gd_dynnr LIKE sy-dynnr,
    gd_param1 LIKE sy-ucomm,
    gd_top TYPE slis_t_listheader,
    wa_top TYPE slis_listheader.
    *----------------------------------------------------------------------*
    * Selection screen
    *----------------------------------------------------------------------*
    SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
    SELECT-OPTIONS: s_vbeln FOR vbak-vbeln, " Sales Document
    s_vkorg FOR vbak-vkorg. " Sales Organization
    SELECTION-SCREEN END OF BLOCK b1.
    *----------------------------------------------------------------------*
    * MAIN PROGRAM
    *----------------------------------------------------------------------*
    gd_repid = sy-repid.

    PERFORM fetch_assign_data.

    IF it_output[] IS NOT INITIAL.

    PERFORM display_data.
    ENDIF.
    *&---------------------------------------------------------------------*
    *& Form fetch_assign_data
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM fetch_assign_data .
    SELECT vbeln
    kunnr
    waerk
    audat
    netwr FROM vbak
    INTO TABLE it_vbak
    WHERE vbeln IN s_vbeln
    AND vkorg IN s_vkorg.

    IF it_vbak[] IS NOT INITIAL.

    SELECT vbeln
    posnr
    matnr FROM vbap
    INTO TABLE it_vbap
    FOR ALL ENTRIES IN it_vbak
    WHERE vbeln EQ it_vbak-vbeln.

    SELECT kunnr
    name1 FROM kna1
    INTO TABLE it_kna1
    FOR ALL ENTRIES IN it_vbak
    WHERE kunnr EQ it_vbak-kunnr.

    IF it_vbap[] IS NOT INITIAL .

    SELECT matnr
    maktx FROM makt
    INTO TABLE it_makt
    FOR ALL ENTRIES IN it_vbap
    WHERE matnr EQ it_vbap-matnr.

    SELECT vbeln
    vgbel
    vgpos
    matnr FROM lips
    INTO TABLE it_lips
    FOR ALL ENTRIES IN it_vbap
    WHERE vgbel EQ it_vbap-vbeln
    AND vgpos EQ it_vbap-posnr.

    IF it_lips[] IS NOT INITIAL.

    SELECT vbeln FROM likp
    INTO TABLE it_likp
    FOR ALL ENTRIES IN it_lips
    WHERE vbeln EQ it_lips-vbeln.

    SELECT vbeln
    tknum FROM vttp
    INTO TABLE it_vttp
    FOR ALL ENTRIES IN it_lips
    WHERE vbeln EQ it_lips-vbeln.

    IF it_vttp[] IS NOT INITIAL .

    SELECT tknum
    route
    distz
    medst FROM vttk
    INTO TABLE it_vttk
    FOR ALL ENTRIES IN it_vttp
    WHERE tknum EQ it_vttp-tknum.

    ENDIF.

    SELECT vbeln
    vgbel FROM vbrp
    INTO TABLE it_vbrp
    FOR ALL ENTRIES IN it_lips
    WHERE vgbel EQ it_lips-vbeln.

    IF it_vbrp[] IS NOT INITIAL.

    SELECT vbeln FROM vbrk
    INTO TABLE it_vbrk
    FOR ALL ENTRIES IN it_vbrp
    WHERE vbeln EQ it_vbrp-vbeln
    AND vbtyp EQ 'M'.
    ENDIF. " IF it_vbrp[] IS NOT INITIAL.

    ENDIF. " IF it_lips[] IS NOT INITIAL.

    ENDIF. " IF it_vbap[] IS NOT INITIAL .

    ENDIF . " IF it_vbak[] IS NOT INITIAL.

    CLEAR it_output[].

    LOOP AT it_vbap.

    CLEAR it_output.
    it_output-status = icon_red_light.
    it_output-posnr = it_vbap-posnr. "Sales Doc. Item
    it_output-matnr = it_vbap-matnr. "Material No.

    READ TABLE it_makt WITH KEY matnr = it_vbap-matnr.
    IF sy-subrc EQ 0.
    it_output-maktx = it_makt-maktx. "Material Description
    ENDIF.

    READ TABLE it_vbak WITH KEY vbeln = it_vbap-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln = it_vbak-vbeln. "Sales doc. no.
    it_output-kunnr = it_vbak-kunnr. "Sold To Party
    it_output-waerk = it_vbak-waerk. "SD Doc. Currency
    it_output-audat = it_vbak-audat. "Document Date
    it_output-netwr = it_vbak-netwr. "Net Value

    READ TABLE it_kna1 WITH KEY kunnr = it_vbak-kunnr.
    IF sy-subrc EQ 0.
    it_output-name1 = it_kna1-name1. "Customer
    ENDIF.

    READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
    vgpos = it_vbap-posnr
    matnr = it_vbap-matnr.
    IF sy-subrc EQ 0.
    LOOP AT it_lips WHERE vgbel EQ it_vbap-vbeln AND
    vgpos EQ it_vbap-posnr AND
    matnr EQ it_vbap-matnr.

    * READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
    * vgpos = it_vbap-posnr
    * matnr = it_vbap-matnr.

    * IF sy-subrc EQ 0.
    it_output-status = icon_yellow_light.
    * Read Delivery Header
    READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln1 = it_likp-vbeln. "Delivery

    ** loop at shipment item
    LOOP AT it_vttp WHERE vbeln EQ it_likp-vbeln.

    * READ TABLE it_vttp WITH KEY vbeln = it_likp-vbeln.

    * IF sy-subrc EQ 0.

    * Read Shipment Header
    READ TABLE it_vttk WITH KEY tknum = it_vttp-tknum.
    IF sy-subrc EQ 0.
    it_output-tknum = it_vttk-tknum. "Shipment
    it_output-route = it_vttk-route. "Route
    it_output-distz = it_vttk-distz. "Distance
    it_output-medst = it_vttk-medst. "Unit Of Distance
    ENDIF.

    ENDLOOP.
    * ENDIF.
    ** Loop At Billing Item
    LOOP AT it_vbrp WHERE vgbel EQ it_lips-vbeln.

    * READ TABLE it_vbrp WITH KEY vgbel = it_lips-vbeln.
    *
    * IF sy-subrc EQ 0.

    * Read Billing Header
    READ TABLE it_vbrk WITH KEY vbeln = it_vbrp-vbeln.

    IF sy-subrc EQ 0.
    it_output-vbeln2 = it_vbrk-vbeln. "Billing
    ENDIF.

    IF it_output-vbeln1 IS NOT INITIAL AND
    it_output-vbeln2 IS NOT INITIAL.
    it_output-status = icon_green_light.
    ENDIF.

    * ENDIF.
    ENDLOOP."Loop it_vbrp

    ENDIF. "sy-subrc EQ 0.

    APPEND it_output.
    ENDLOOP.

    ELSE.
    APPEND it_output.
    ENDIF.
    ENDIF.
    ENDLOOP.

    ENDFORM. " fetch_assign_data
    *&---------------------------------------------------------------------*
    *& Form display_data
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM display_data .
    CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
    i_program_name = gd_repid
    i_internal_tabname = 'IT_OUTPUT'
    i_inclname = gd_repid
    CHANGING
    ct_fieldcat = gd_fieldcat.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

    PERFORM manipulate_events.
    PERFORM manipulate_fieldcatlog.
    PERFORM manipulate_layout.

    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
    i_callback_program = gd_repid
    i_callback_pf_status_set = 'PF_STATUS'
    i_callback_user_command = 'USER_COMMAND'
    is_layout = gd_layout
    it_fieldcat = gd_fieldcat
    TABLES
    t_outtab = it_output.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.
    ENDFORM. " display_data
    *&---------------------------------------------------------------------*
    *& Form manipulate_events
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_events .
    CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
    EXPORTING
    i_list_type = 0
    IMPORTING
    et_events = gd_events
    EXCEPTIONS
    list_type_wrong = 1
    OTHERS = 2.
    IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

    ELSE.

    READ TABLE gd_events WITH KEY name = 'USER_COMMAND' INTO wa_event.
    wa_event-form = 'USER_COMMAND1'.
    MODIFY gd_events FROM wa_event INDEX sy-tabix.

    ENDIF. "sy-subrc <> 0
    ENDFORM. " manipulate_events
    *&---------------------------------------------------------------------*
    *& Form manipulate_fieldcatlog
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_fieldcatlog .

    LOOP AT gd_fieldcat INTO wa_fieldcat.

    CASE wa_fieldcat-fieldname.

    WHEN 'VBELN' OR "Sales Doc. No.
    'VBELN1' OR "Delivery
    'VBELN2' OR "Billing
    'KUNNR' OR "Sold To Party
    'TKNUM' OR "Shipment
    'MATNR'. "Material No.

    wa_fieldcat-hotspot = 'X'.

    WHEN 'SELKZ'.
    wa_fieldcat-no_out = 'X'.

    WHEN 'NETWR'.
    IF sy-ucomm EQ 'EDIT'.
    wa_fieldcat-edit = 'X'.
    ENDIF.
    ENDCASE.

    MODIFY gd_fieldcat FROM wa_fieldcat.

    ENDLOOP.
    ENDFORM. " manipulate_fieldcatlog
    *&---------------------------------------------------------------------*
    *& Form manipulate_layout
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM manipulate_layout .
    gd_layout-box_fieldname = 'SELKZ'.
    gd_layout-box_tabname = 'IT_OUTPUT'.
    gd_layout-colwidth_optimize = 'X'.
    ENDFORM. " manipulate_layout
    * Form user_command
    *&---------------------------------------------------------------------
    *
    * text
    *----------------------------------------------------------------------
    *
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------
    FORM user_command USING param1 TYPE sy-ucomm
    param2 TYPE slis_selfield.

    CASE param1.

    WHEN '&IC1'.
    IF param2-fieldname = 'VBELN'.
    SET PARAMETER ID 'AUN' FIELD param2-value.
    CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN'.

    IF param2-fieldname = 'KUNNR'.
    SET PARAMETER ID 'KUN' FIELD param2-value.
    CALL TRANSACTION 'XD03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'KUNNR'.

    IF param2-fieldname = 'VBELN1'.
    SET PARAMETER ID 'VL' FIELD param2-value.
    CALL TRANSACTION 'VL03N' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN1'.

    IF param2-fieldname = 'TKNUM'.
    SET PARAMETER ID 'TNR' FIELD param2-value.
    CALL TRANSACTION 'VT03N' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'TKNUM'.

    IF param2-fieldname = 'VBELN2'.
    SET PARAMETER ID 'VF' FIELD param2-value.
    CALL TRANSACTION 'VF03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'VBELN2'.


    IF param2-fieldname = 'MATNR'.
    SET PARAMETER ID 'MAT' FIELD param2-value.
    CALL TRANSACTION 'MM03' AND SKIP FIRST SCREEN.
    ENDIF. "param2-fieldname = 'MATNR'.

    WHEN 'EDIT'.
    READ TABLE it_output WITH KEY selkz = 'X'.
    DELETE it_output[] WHERE selkz NE 'X'.
    it_output1[] = it_output[].
    PERFORM display_data.

    WHEN 'SAVE'.

    LOOP AT it_output.


    UPDATE vbak SET netwr = it_output-netwr
    WHERE vbeln EQ it_output-vbeln
    AND kunnr EQ it_output-kunnr.
    IF sy-subrc EQ 0.
    MESSAGE 'Value Updated Sucessfully' TYPE 'S'.
    ELSE.
    MESSAGE 'Double click on the changed row' TYPE 'I'.
    ENDIF.
    ENDLOOP.

    WHEN 'EXCEL'.

    * READ TABLE it_output WITH KEY selkz = 'X'.
    * IF sy-subrc EQ 0.
    PERFORM open_excel.
    PERFORM add_worksheet.
    * ENDIF.

    WHEN 'BACK'.
    LEAVE TO SCREEN 1000.

    ENDCASE. "param1

    param2-refresh = 'X'.
    ENDFORM. "user_command
    *&---------------------------------------------------------------------*
    *& Form pf_status
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->RT_EXTAB text
    *----------------------------------------------------------------------*
    FORM pf_status USING rt_extab TYPE slis_t_extab.

    DATA: gd_index TYPE i.

    wa_extab-fcode = 'EDIT'.
    APPEND wa_extab TO gd_extab.
    CLEAR wa_extab.

    wa_extab-fcode = 'EXCEL'.
    APPEND wa_extab TO gd_extab.
    CLEAR wa_extab.

    IF sy-ucomm EQ 'EDIT' .

    SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

    ELSEIF sy-index EQ 0 AND sy-ucomm EQ ' '.

    SET PF-STATUS 'ZSTATUS'.

    ELSEIF sy-ucomm EQ 'BACK'.
    SET PF-STATUS 'ZSTATUS'.


    ELSEIF sy-index EQ 1 OR sy-ucomm EQ 'SAVE'.

    SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

    ENDIF. " IF sy-ucomm EQ 'EDIT' .
    ENDFORM. "pf_status
    *&---------------------------------------------------------------------*
    *& Form open_excel
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM open_excel .
    DATA: l_cnt TYPE i.

    CREATE OBJECT w_excel 'EXCEL.APPLICATION'.
    IF sy-subrc NE 0.
    WRITE : / 'Error CREATE OBJECT'.
    ENDIF. " IF sy-subrc NE 0.

    SET PROPERTY OF w_excel 'Visible' = 1.
    IF sy-subrc NE 0.
    WRITE : / 'Error MAKE EXCEL VISIBLE'.
    ENDIF. " IF sy-subrc NE 0.

    CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
    IF sy-subrc NE 0.
    WRITE : / 'Error GET WORKBOOKS'.
    ENDIF. " IF sy-subrc NE 0.

    CALL METHOD OF w_wbooks 'ADD' = w_wbook.
    IF sy-subrc NE 0.
    WRITE : / 'Error ADD TEMPLATE'.
    ENDIF. " IF sy-subrc NE 0.

    GET PROPERTY OF w_wbook 'Worksheets' = w_wsheets.
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    SET PROPERTY OF w_excel 'DISPLAYALERTS' = 0.

    GET PROPERTY OF w_wsheets 'Count' = l_cnt.

    l_cnt = l_cnt - 1.

    * Delete unwanted worksheets
    DO l_cnt TIMES.
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    CALL METHOD OF w_wsheet 'DELETE'.
    ENDDO. " DO l_cnt TIMES.
    ENDFORM. " open_excel
    *&---------------------------------------------------------------------*
    *& Form add_worksheet
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM add_worksheet .
    GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
    CALL METHOD OF w_wsheets 'Add' = w_wsheet.

    w_size = 15.

    PERFORM fill_ccell USING 1 8 'Sales Details' 'X' '' '' '' '' 200.

    PERFORM merge_cell USING 1 8 1 9.

    PERFORM set_border USING 1 8 1 9 10 'X'.

    PERFORM set_color USING 1 8 1 9 'LV'.

    "Distance
    "Unit of measure for distance
    "Route
    "Billing Document
    "Status


    PERFORM fill_ccell
    USING 2 1 'Sales Document' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 2 'Document Date' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 3 'Net Value ' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 4 'SD Document Currency' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 5 'Sold-to party' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 6 'Name 1' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 7 'Sales Document Item' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 8 'Material number' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 9 'Material Desc.' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 10 'Delivery' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 11 'Shipment Number' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 12 'Distance' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 13 'Unit of measure for distance' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 14 'Route' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 15 'Billing Document' 'X' '' '' '' '' 000255000.

    PERFORM fill_ccell
    USING 2 16 'Status' 'X' '' '' '' '' 000255000.

    w_row = 2.
    w_size = 10.

    LOOP AT it_output WHERE selkz EQ 'X'.
    w_row = w_row + 1.

    PERFORM fill_ccell
    USING w_row 1 it_output-vbeln 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 2 it_output-audat 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 3 it_output-netwr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 4 it_output-waerk 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 5 it_output-kunnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 6 it_output-name1 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 7 it_output-posnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 8 it_output-matnr 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 9 it_output-maktx 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 10 it_output-vbeln1 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 11 it_output-tknum 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 12 it_output-distz 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 13 it_output-medst 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 14 it_output-route 'X' '' '' '' '' 111111111.

    PERFORM fill_ccell
    USING w_row 15 it_output-vbeln2 'X' '' '' '' '' 111111111.

    * IF it_output-status EQ icon_red_light.
    * it_output-status = 0.
    * ELSEIF it_output-status EQ icon_yellow_light.
    * it_output-status = 1.
    * ELSEIF it_output-status EQ icon_green_light.
    * it_output-status = 2.
    * ENDIF.

    * PERFORM fill_ccell
    * USING w_row 16 it_output-status '' '' '' '' '' 111111111.

    CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
    **
    IF it_output-status EQ icon_red_light.
    PERFORM set_color USING w_row 16 w_row 16 'RD'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ELSEIF it_output-status EQ icon_green_light.
    PERFORM set_color USING w_row 16 w_row 16 'GR'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ELSEIF it_output-status EQ icon_yellow_light.
    PERFORM set_color USING w_row 16 w_row 16 'YL'.
    PERFORM set_border USING w_row 16 w_row 16 10 'X'.
    ENDIF.

    * PERFORM fill_ccell
    * USING w_row 16 it_output-status 'X' '' '' '' '' 111111111.

    ENDLOOP.

    ENDFORM. " add_worksheet
    *&---------------------------------------------------------------------*
    *& Form fill_ccell
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1481 text
    * -->P_1482 text
    * -->P_1483 text
    * -->P_1484 text
    * -->P_1485 text
    * -->P_1486 text
    * -->P_200 text
    *----------------------------------------------------------------------*
    FORM fill_ccell USING i_row TYPE i
    i_col TYPE i
    i_value
    i_fontbold
    i_digit
    i_wraptext
    i_horizon_align
    i_vertical_align
    i_color.

    DATA: l_str TYPE string.

    CALL METHOD OF w_excel 'Cells' = w_cell
    EXPORTING
    #1 = i_row
    #2 = i_col.

    PERFORM err_hdl.

    SET PROPERTY OF w_cell 'VALUE' = i_value.
    PERFORM err_hdl.

    IF i_fontbold = 'X'.
    GET PROPERTY OF w_cell 'Font' = w_font.
    SET PROPERTY OF w_font 'Bold' = 1.
    SET PROPERTY OF w_font 'Size' = w_size.
    SET PROPERTY OF w_font 'Color' = i_color.
    ENDIF. " IF i_fontbold = 'X'.

    IF NOT i_wraptext IS INITIAL.
    SET PROPERTY OF w_cell 'WrapText' = 1.
    ENDIF. " IF NOT i_wraptext IS INITIAL.

    IF NOT i_horizon_align IS INITIAL.

    IF i_horizon_align = 'L'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlleft.
    ELSEIF i_horizon_align = 'R'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlright.
    ELSEIF i_horizon_align = 'C'.
    SET PROPERTY OF w_cell 'HorizontalAlignment' = xlcenter.
    ENDIF. " IF i_horizon_align = 'L'.

    ENDIF. " IF NOT i_horizon_align IS INITIAL.

    IF NOT i_vertical_align IS INITIAL.

    IF i_vertical_align = 'T'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xltop.
    ELSEIF i_vertical_align = 'B'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xlbottom.
    ELSEIF i_vertical_align = 'C'.
    SET PROPERTY OF w_cell 'VerticalAlignment' = xlcenter.
    ENDIF. " IF i_vertical_align = 'T'.

    ENDIF. " IF NOT i_vertical_align IS INITIAL.

    * To set number format for cell
    IF i_digit <> ''.

    IF i_value IS INITIAL AND i_digit <> '%'.
    SET PROPERTY OF w_cell 'VALUE' = ''.
    ELSE.

    IF i_digit = '1'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,###.0 '.
    ELSEIF i_digit = '2'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00 '.
    ELSEIF i_digit = '%'.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00% '.
    ELSE.
    SET PROPERTY OF w_cell 'NumberFormat' = '#,### '.
    ENDIF.

    ENDIF. " IF i_value IS INITIAL AND i_digit <> '%'.

    ENDIF. " IF i_digit <> ''.

    ENDFORM. " fill_ccell
    *&---------------------------------------------------------------------*
    *& Form merge_cell
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    *----------------------------------------------------------------------*
    FORM merge_cell USING i_row1 i_col1
    i_row2 i_col2.
    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    CALL METHOD OF w_range 'Merge'.
    ENDFORM. " merge_cell
    *&---------------------------------------------------------------------*
    *& Form set_border
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    * -->P_8 text
    * -->P_1503 text
    *----------------------------------------------------------------------*
    FORM set_border USING i_row1
    i_col1
    i_row2
    i_col2
    i_thickness
    i_allborders.

    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING
    #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    IF i_allborders IS INITIAL.

    CALL METHOD OF w_range 'BorderAround'
    EXPORTING #1 = 1 "Continuous line
    #2 = i_thickness. "Thickness: 1 - Normal, 4 - Thick
    ELSE.

    GET PROPERTY OF w_range 'Borders' = w_border.
    SET PROPERTY OF w_border 'LineStyle' = '1'.
    SET PROPERTY OF w_border 'Weight' = i_thickness.

    ENDIF. " IF i_allborders IS INITIAL.
    ENDFORM. " set_border
    *&---------------------------------------------------------------------*
    *& Form set_color
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * -->P_1 text
    * -->P_6 text
    * -->P_1 text
    * -->P_7 text
    * -->P_1511 text
    *----------------------------------------------------------------------*
    FORM set_color USING i_row1
    i_col1
    i_row2
    i_col2
    i_color.

    DATA: l_colorindex TYPE i.

    CASE i_color.

    WHEN 'BK'.
    l_colorindex = 1. "Black

    WHEN 'BR'.
    l_colorindex = 53. "Brown

    WHEN 'OG'.
    l_colorindex = 52. "Olive Green

    WHEN 'DG'.
    l_colorindex = 51. "Dark Green

    WHEN 'DT'.
    l_colorindex = 49. "Dark Teal

    WHEN 'DB'.
    l_colorindex = 11. "Dark Blue

    WHEN 'ID'.
    l_colorindex = 55. "Indigo

    WHEN 'G4'.
    l_colorindex = 56. "Gray 80%

    WHEN 'DR'.
    l_colorindex = 9. "Dark Red

    WHEN 'OR'.
    l_colorindex = 46. "Orange

    WHEN 'DY'.
    l_colorindex = 12. "Dark Yellow

    WHEN 'GR'.
    l_colorindex = 10. "Green

    WHEN 'TL'.
    l_colorindex = 14. "Teal

    WHEN 'BL'.
    l_colorindex = 5. "Blue

    WHEN 'BY'.
    l_colorindex = 47. "Blue Gray

    WHEN 'G3'.
    l_colorindex = 16. "Gray 50%

    WHEN 'RD'.
    l_colorindex = 3. "Red

    WHEN 'LO'.
    l_colorindex = 45. "Light Orange

    WHEN 'LI'.
    l_colorindex = 43. "Lime

    WHEN 'SG'.
    l_colorindex = 50. "Sea Green

    WHEN 'AQ'.
    l_colorindex = 42. "Aqua

    WHEN 'LB'.
    l_colorindex = 41. "Light Blue

    WHEN 'VL'.
    l_colorindex = 13. "Violet

    WHEN 'G2'.
    l_colorindex = 48. "Gray 40%

    WHEN 'PK'.
    l_colorindex = 7. "Pink

    WHEN 'GD'.
    l_colorindex = 44. "Gold

    WHEN 'YL'.
    l_colorindex = 6. "Yellow

    WHEN 'BG'.
    l_colorindex = 4. "Bright Green

    WHEN 'TQ'.
    l_colorindex = 8. "Turquoise

    WHEN 'SB'.
    l_colorindex = 33. "Sky Blue

    WHEN 'PL'.
    l_colorindex = 54. "Plum

    WHEN 'G1'.
    l_colorindex = 15. "Gray 25%

    WHEN 'RS'.
    l_colorindex = 38. "Rose

    WHEN 'TN'.
    l_colorindex = 40. "Tan

    WHEN 'LY'.
    l_colorindex = 36. "Light Yellow

    WHEN 'LG'.
    l_colorindex = 35. "Light Green

    WHEN 'LT'.
    l_colorindex = 34. "Light Turquoise

    WHEN 'PB'.
    l_colorindex = 37. "Pale Blue

    WHEN 'LV'.
    l_colorindex = 39. "Lavender

    WHEN 'WH'.
    l_colorindex = 2. "White

    WHEN OTHERS.
    l_colorindex = 2. "White

    ENDCASE. " CASE i_color.

    CALL METHOD OF w_excel 'Cells' = w_cell1
    EXPORTING #1 = i_row1
    #2 = i_col1.

    CALL METHOD OF w_excel 'Cells' = w_cell2
    EXPORTING #1 = i_row2
    #2 = i_col2.

    CALL METHOD OF w_excel 'Range' = w_range
    EXPORTING #1 = w_cell1
    #2 = w_cell2.

    * GET PROPERTY OF w_excel 'FormatConditions' = w_formatconditions.

    * SET PROPERTY OF w_formatconditions 'ReverseOrder' = 0.
    * SET PROPERTY OF w_formatconditions 'ShowIconOnly' = 1.
    * SET PROPERTY OF w_formatconditions 'IconSet' =
    * 'xl3trafficlights1'.
    *
    * SET PROPERTY OF w_formatconditions 'iconcriteria' = 2.
    * SET PROPERTY OF w_formatconditions 'Type' =
    *'xlconditionvaluenumber'.
    * SET PROPERTY OF w_formatconditions 'Value' = 2.
    * SET PROPERTY OF w_formatconditions 'Operator' = 5.


    GET PROPERTY OF w_range 'Interior' = w_interior.

    SET PROPERTY OF w_interior 'ColorIndex' = l_colorindex.

    ENDFORM. " set_color
    *&---------------------------------------------------------------------*
    *& Form err_hdl
    *&---------------------------------------------------------------------*
    * text
    *----------------------------------------------------------------------*
    * --> p1 text
    * <-- p2 text
    *----------------------------------------------------------------------*
    FORM err_hdl .
    IF sy-subrc <> 0.
    WRITE: / 'OLE-Automation Error:'(010), sy-subrc.
    STOP.
    ENDIF. " IF sy-subrc <> 0.
    ENDFORM. " err_hdl

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.