Downtime Announcement: Please note that SAP Community Wiki will be unavailable due to a system upgrade on Tuesday, August 11th between 7 and 11 AM CEST
Code Gallery
Skip to end of metadata
Go to start of metadata

Author: Matt Hall
Submitted: 5/6/2015
Related Links:

Many ABAP programs are designed to load data from an Excel template.  An issue with using Excel templates is that you never know if the user of the program is using the latest version of the Excel template.  The following steps allow you to upload an Excel template to SAP and migrate using a CTS.  Only a couple of simple statements need to be added to your program to provide options for the user of the program to download or open the most recent Excel template.

 

Upload an Excel template to SAP

Run transaction SMW0.  Click on the Binary data for WebRFC application radio button and press ENTER.

Search for templates that begin with Z

Create new Template

 

Save new template in a CTS and migrate as needed.

Example of selection-screen with buttons to download Excel template and open an Excel template

Download Excel template button

 

Afterwards, open excel template and add data as needed by application.

Open Excel template button

 

How to add buttons to selection-screen of program to provide user options to save
Excel template and open Excel template

Run transaction SE38 to Edit program.  Insert code as shown below.

 

 

Function Z_EXPORT_TEMPLATE

FUNCTION Z_EXPORT_TEMPLATE.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(P_OBJECT_NAME) TYPE  W3OBJID DEFAULT 'ZMWRKCTR01'
*"  EXPORTING
*"     REFERENCE(LS_RETURN) TYPE  BAPIRET2
*"----------------------------------------------------------------------
  DATA: LS_WWWDATA_TAB LIKE WWWDATATAB.

  CLEAR: LS_RETURN.

*Make sure template exists in SMw0 as binary object
  SELECT * FROM WWWDATA INNER JOIN TADIR
                ON WWWDATA~OBJID = TADIR~OBJ_NAME
                INTO  CORRESPONDING FIELDS OF LS_WWWDATA_TAB UP TO 1 ROWS
                WHERE WWWDATA~SRTF2 = 0
                AND   WWWDATA~RELID = 'MI'
                AND   TADIR~PGMID    = 'R3TR'
                AND   TADIR~OBJECT   = 'W3MI'
                AND   TADIR~OBJ_NAME = P_OBJECT_NAME.
  ENDSELECT.
  IF SY-SUBRC <> 0.
    PERFORM FILL_RETURN USING
                              'E'
                              'OO'
                              '000'
                              'Template'
                              P_OBJECT_NAME
                              'does not exist in'
                              'transaction SMW0'
                        CHANGING LS_RETURN.
    EXIT.
  ENDIF.

  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      KEY = LS_WWWDATA_TAB.

ENDFUNCTION.

Function Z_OPEN_TEMPLATE

FUNCTION Z_OPEN_TEMPLATE.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(P_OBJECT_NAME) TYPE  W3OBJID DEFAULT 'ZMWRKCTR01'
*"     REFERENCE(P_MTYPE) TYPE  W3CONTTYPE DEFAULT 'Excel'
*"     REFERENCE(P_APPLI) TYPE  W3FILENAME DEFAULT 'Excel.exe'
*"  EXPORTING
*"     REFERENCE(LS_RETURN) TYPE  BAPIRET2
*"----------------------------------------------------------------------
  DATA: LS_WWWDATA_TAB LIKE WWWDATATAB.

  DATA: L_OBJID  LIKE WWWDATAID,
        MIMEAPPS LIKE W3MIMEAPPL OCCURS 10 WITH HEADER LINE.

  CLEAR: LS_RETURN.

*Make sure template exists in SMw0 as binary object
  SELECT * FROM WWWDATA INNER JOIN TADIR
                ON WWWDATA~OBJID = TADIR~OBJ_NAME
                INTO  CORRESPONDING FIELDS OF LS_WWWDATA_TAB UP TO 1 ROWS
                WHERE WWWDATA~SRTF2 = 0
                AND   WWWDATA~RELID = 'MI'
                AND   TADIR~PGMID    = 'R3TR'
                AND   TADIR~OBJECT   = 'W3MI'
                AND   TADIR~OBJ_NAME = P_OBJECT_NAME.
  ENDSELECT.
  IF SY-SUBRC <> 0.
    PERFORM FILL_RETURN USING
                              'E'
                              'OO'
                              '000'
                              'Template'
                              P_OBJECT_NAME
                              'does not exist in'
                              'transaction SMW0'
                        CHANGING LS_RETURN.
    EXIT.
  ENDIF.

  CONCATENATE 'mimeappl' SY-UNAME INTO L_OBJID-OBJID.
  IMPORT MIMEAPPS FROM DATABASE WWWDATA(ST) ID L_OBJID.
  IF SY-SUBRC = 0.
    READ TABLE MIMEAPPS WITH KEY MTYPE = P_MTYPE.
    IF SY-SUBRC <> 0.
      MIMEAPPS-MTYPE = P_MTYPE.
      MIMEAPPS-APPLI = P_APPLI.
      APPEND MIMEAPPS.
      EXPORT MIMEAPPS TO DATABASE WWWDATA(ST) ID L_OBJID.
    ENDIF.
  ELSE.
    MIMEAPPS-MTYPE = P_MTYPE.
    MIMEAPPS-APPLI = P_APPLI.
    APPEND MIMEAPPS.
    EXPORT MIMEAPPS TO DATABASE WWWDATA(ST) ID L_OBJID.
  ENDIF.

  CALL FUNCTION 'SHOW_WEB_OBJECT'
    EXPORTING
      KEY               = LS_WWWDATA_TAB
    EXCEPTIONS
      CANCELED_BY_USER  = 2
      PROGRAM_NOT_FOUND = 3.
  IF SY-SUBRC <> 0.
    PERFORM FILL_RETURN USING
                            'E'
                            'OO'
                            '000'
                            'Error opening template'
                            P_OBJECT_NAME
                            ''
                            ''
                      CHANGING LS_RETURN.
    EXIT.
  ENDIF.

ENDFUNCTION.

Include LZUTILF01

FORM FILL_RETURN  USING      P_TYPE
                             P_ID
                             P_NUMBER
                             P_MESSAGE_V1
                             P_MESSAGE_V2
                             P_MESSAGE_V3
                             P_MESSAGE_V4
                    CHANGING P_RETURN LIKE BAPIRET2.
  CLEAR: P_RETURN.

  P_RETURN-TYPE = P_TYPE.
  P_RETURN-ID = P_ID.
  P_RETURN-NUMBER = P_NUMBER.
  P_RETURN-MESSAGE_V1 = P_MESSAGE_V1.
  P_RETURN-MESSAGE_V2 = P_MESSAGE_V2.
  P_RETURN-MESSAGE_V3 = P_MESSAGE_V3.
  P_RETURN-MESSAGE_V4 = P_MESSAGE_V4.

  MESSAGE ID P_ID TYPE P_TYPE NUMBER P_NUMBER
          WITH P_MESSAGE_V1 P_MESSAGE_V2 P_MESSAGE_V3 P_MESSAGE_V4
     INTO P_RETURN-MESSAGE.

ENDFORM.                    " FILL_RETURN