Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

OLE2 issue in converting excel macro to abap code

Go to solution
Former Member

‎2013 May 06 6:50 AM

0 Likes
2,352

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.

1 ACCEPTED SOLUTION
Read only

Go to solution
Former Member

‎2013 May 06 7:31 AM

0 Likes
1,843

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

Preview file
13 KB

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.

Reply
7 REPLIES 7
Read only

Go to solution
Former Member

‎2013 May 06 7:31 AM

0 Likes
1,844

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

Preview file
13 KB
Read only

Go to solution
Former Member
In response to Former Member

‎2013 May 06 1:02 PM

0 Likes
1,843

Hello Smruti,

Thanks for your reply,It worked for me.

I have one more query on the same,How can i send OLE generated object as attachement through an email.I am checking various posts insisting use converting it to SOLIX.

Read only

Go to solution
Former Member
In response to Former Member

‎2013 May 07 5:48 AM

0 Likes
1,843

Hi Aradhya,

First download your OLE generated object into Presentation Server(i.e. particular location) , then with the help of this FMs  ""SO_DOCUMENT_SEND_API1" or "SO_NEW_DOCUMENT_SEND_API1"   you can send Excel Sheet attached to your respective mail id . Search in SCN.SAP.COM  lot of Sample code you can find code and discussion on this FMs .

Regard's

Smruti

Read only

Go to solution
krishna_k19
Contributor
In response to Former Member

‎2013 May 07 5:57 AM

0 Likes
1,843

Hi smruti,

        is there any way to send a mail , in body can we attach text boxes.(attachment i know,but i'm asking in body).

  Please let me know if you know..

Regards,

Krishna

Read only

Go to solution
Former Member
In response to Former Member

‎2013 May 07 7:17 AM

0 Likes
1,843

Hello smruti,

Thanks for your reply,

I tried using FM SO_DOCUMENT_SEND_API1,problem is i am losing the format which was establisted using OLE. I am still searching for some FM which sends email without disturbing the format of excel.

Regrads

Aradhya.J.M.

Read only

Go to solution
Former Member
In response to Former Member

‎2013 May 07 8:03 AM

0 Likes
1,843

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

Read only

Go to solution
Former Member

‎2013 May 27 11:26 AM

0 Likes
1,843

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