Skip to Content

1. OVERVIEW


Integrate Microsoft Office documents in SAP is not a common requirement but as Microsoft Suite has a wide implementation in business processes in some occasions we need to work with Microsoft Office documents in SAP.

We can work with SAP BDS (BC-SRV-BDS) as a repository and read the template from there to load in our program. But in this document I’m going to show how you can open an Excel document from your Local PC in a SAP control inplace.

2. TECHNICAL SPECIFICATIONS


I guess this development can be executed with many SAP GUI versions and Microsoft Office but you should check.

In my case:

SAP GUI: SAP Logon 720 (7200.3.11.1074)

SAP System: SAP ECC 6.0

Microsoft Excel: 14.0.6106.5005 (32-bit)

3. SCREEN SHOTS


Upload, save and activate program ZALF_EXCEL_INPLACE_LOCAL_PC  (see code in section 4. CODE)

Execute program

Capture1.JPG

Select Excel document from you Local PC

Capture2.JPG

Excel document is opened inplace.

Capture3.JPG

4. CODE

Important:

      • Create Dynpro 100 with a control inside called: ‘CTR_EXCEL’
      • Create PF-STATUS ‘100’, just with ‘EXIT’ function
      • Create TITLEBAR ‘100’ with some description

*&———————————————————————*

*& Report  ZALF_EXCEL_INPLACE_LOCAL_PC

*&

*&———————————————————————*

*& This program shows how to open an Excel file from your Local PC in

*& an “inplace” control

*&———————————————————————*

REPORT  zalf_excel_inplace_local_pc.

* Global definitions

DATA:

* OKCODE

       gv_okcode TYPE syucomm,

* SAP Desktop Office Integration Interfaces

       gc_container   TYPE REF TO cl_gui_custom_container,

       gc_control     TYPE REF TO i_oi_container_control,

       gc_document    TYPE REF TO i_oi_document_proxy,

       gc_spreadsheet TYPE REF TO i_oi_spreadsheet,

       gc_error       TYPE REF TO i_oi_error,

       gt_errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE,

       gv_initialized TYPE c.

* Start with the screen

SET SCREEN 100.

*&———————————————————————*

*&      Module  STATUS_0100  OUTPUT

*&———————————————————————*

* Status

*———————————————————————-*

MODULE status_0100 OUTPUT.

   SET PF-STATUS ‘100’.

   SET TITLEBAR ‘100’.

ENDMODULE.                 ” STATUS_0100  OUTPUT

*&———————————————————————*

*&      Module  EXIT  INPUT

*&———————————————————————*

* Exit program

*———————————————————————-*

MODULE exit INPUT.

   CASE gv_okcode.

     WHEN ‘EXIT’.

       LEAVE PROGRAM.

   ENDCASE.

ENDMODULE.                 ” EXIT  INPUT

*&———————————————————————*

*&      Module  CREATE_OBJECTS  OUTPUT

*&———————————————————————*

* Create Objects

*———————————————————————-*

MODULE create_objects OUTPUT.

* Local definitions

   TYPES:

          t_row(2048) TYPE x“long byte string

   DATA:

* Structure and table to retreive file from Local PC

         lw_file_table TYPE file_table,

         lt_file_table TYPE filetable,

* Variable for local PC file name

         lv_filename TYPE string,

* Table to upload file

         lt_upload TYPE STANDARD TABLE OF t_row,

* File length

         lv_filelength TYPE i,

* Return code

         lv_rc TYPE i,

* Return from Method open_document_from_table

         lf_retcode TYPE soi_ret_string,

* Spreadsheet available

         lv_spread_available TYPE i.

* First open a dialog window to select file from Local PC

   REFRESH lt_file_table.

   CLEAR lv_rc.

   CALL METHOD cl_gui_frontend_services=>file_open_dialog

     EXPORTING

       window_title            = ‘Open’

*     DEFAULT_EXTENSION       =

       default_filename        = ‘*.xls*’

       file_filter             = ‘*.xls*’

*     WITH_ENCODING           =

*     INITIAL_DIRECTORY       =

*     MULTISELECTION          =

     CHANGING

       file_table              = lt_file_table

       rc                      = lv_rc

*     USER_ACTION             =

*     FILE_ENCODING           =

     EXCEPTIONS

       file_open_dialog_failed = 1

       cntl_error              = 2

       error_no_gui            = 3

       not_supported_by_gui    = 4

       OTHERS                  = 5.

   IF sysubrc <> 0.

* Implement suitable error handling here

     LEAVE PROGRAM.

   ENDIF.

* Get the SAP DOI interface references.

* This work has just to be done once !

   CHECK gv_initialized IS INITIAL.

* First get the SAP DOI i_oi_container_control interface

   CALL METHOD c_oi_container_control_creator=>get_container_control

     IMPORTING

       control = gc_control

       error   = gc_error.

* Check no errors occured

   CALL METHOD gc_error->raise_message

     EXPORTING

       type = ‘E’.

* Create a control container as defined in dynpro 100

   CREATE OBJECT gc_container

     EXPORTING

       container_name = ‘CTR_EXCEL’.

* Initialize the SAP DOI Container, tell it to run in the container

* specified above and tell it to run Excel in-place

   CALL METHOD gc_control->init_control

     EXPORTING

       r3_application_name      = ‘R/3 Basis’                “#EC NOTEXT

       inplace_enabled          = ‘X’

       inplace_scroll_documents = ‘X’

       parent                   = gc_container

       register_on_close_event  = ‘X’

       register_on_custom_event = ‘X’

       no_flush                 = ‘X’

     IMPORTING

       error                    = gt_errors.

* Save error object in collection

   APPEND gt_errors.

* Ask the SAP DOI container for a i_oi_document_proxy for Excel

   CALL METHOD gc_control->get_document_proxy

     EXPORTING

       document_type      = ‘Excel.Sheet’

       no_flush           = ‘X’

*     REGISTER_CONTAINER = ‘X’

     IMPORTING

       document_proxy     = gc_document

       error              = gt_errors.

* Save error object in collection

   APPEND gt_errors.

* Upload file from Local PC to SAP

* Read file name user has choosen

   CLEAR: lw_file_table, lv_filename.

   READ TABLE lt_file_table INTO lw_file_table INDEX 1.

   lv_filename = lw_file_tablefilename.

   REFRESH lt_upload.

   CLEAR lv_filelength.

   CALL METHOD cl_gui_frontend_services=>gui_upload

     EXPORTING

       filename                = lv_filename

       filetype                = ‘BIN’

*     HAS_FIELD_SEPARATOR     = SPACE

*     HEADER_LENGTH           = 0

*     READ_BY_LINE            = ‘X’

*     DAT_MODE                = SPACE

*     CODEPAGE                = SPACE

*     IGNORE_CERR             = ABAP_TRUE

*     REPLACEMENT             = ‘#’

*     VIRUS_SCAN_PROFILE      =

     IMPORTING

       filelength              = lv_filelength

*     HEADER                  =

     CHANGING

       data_tab                = lt_upload

     EXCEPTIONS

       file_open_error         = 1

       file_read_error         = 2

       no_batch                = 3

       gui_refuse_filetransfer = 4

       invalid_type            = 5

       no_authority            = 6

       unknown_error           = 7

       bad_data_format         = 8

       header_not_allowed      = 9

       separator_not_allowed   = 10

       header_too_long         = 11

       unknown_dp_error        = 12

       access_denied           = 13

       dp_out_of_memory        = 14

       disk_full               = 15

       dp_timeout              = 16

       not_supported_by_gui    = 17

       error_no_gui            = 18

       OTHERS                  = 19.

   IF sysubrc <> 0.

* Implement suitable error handling here

     LEAVE PROGRAM.

   ENDIF.

* Now use Method OPEN_DOCUMENT_FROM_TABLE

   CLEAR lf_retcode.

   CALL METHOD gc_document->open_document_from_table

     EXPORTING

       document_size    = lv_filelength

       document_table   = lt_upload

       document_title   = ‘Excel inplace’

       no_flush         = ‘X’

       open_inplace     = ‘X’

*     OPEN_READONLY    = ‘ ‘

*     PROTECT_DOCUMENT = ‘ ‘

*     ONSAVE_MACRO     = ‘ ‘

*     STARTUP_MACRO    = ”

     IMPORTING

       error            = gt_errors

       retcode          = lf_retcode.

   APPEND gt_errors.

* Check if our document proxy can serve a spreadsheet interface  data:

   CLEAR lv_spread_available.

   CALL METHOD gc_document->has_spreadsheet_interface

     EXPORTING

       no_flush     = ‘X’

     IMPORTING

       is_available = lv_spread_available

       error        = gt_errors.

   APPEND gt_errors.

   CALL METHOD gc_document->get_spreadsheet_interface

     EXPORTING

       no_flush        = ‘ ‘

     IMPORTING

       sheet_interface = gc_spreadsheet

       error           = gt_errors.

* Save error object in collection

   APPEND gt_errors.

   LOOP AT gt_errors.

     CALL METHOD gt_errors->raise_message

       EXPORTING

         type = ‘E’.

   ENDLOOP.

   FREE gt_errors.

   gv_initialized = ‘X’.

ENDMODULE.                 ” CREATE_OBJECTS  OUTPUT


To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Curtis

    Could this technique be used to serve a copy of the current-released Excel file to users?

    Rather than placing the newer copy on a shared drive for users to find.

    Working in a medical device company.

    Required to have all excel files, used to make quality decision, shall be validated. 

    When Document Control releases updates in SAP, to the Excel Template, want users to stop using old copies and start using new versions.

    (0) 
    1. Alfonso Rodríguez Pérez Post author

      Hi Mark,

      I had the same requirement, we had to confirm a central Excel file for all users. I solved this using Business Document Service (BDS), basically transaction OAOR.

      I stored the central Excel file in BDS and SAP transaction retrieved this in a similar way I’ve explained in my post but instead to upload from local PC the Excel file was uploaded from BDS.


      In my case we had to check Excel data with SAP: WBS codes, currencies, cost center, etc


      In order to get/set data between SAP an Excel file I used interface i_oi_spreadsheet with methods set_ranges_data, get_ranges_data


      I managed an internal version of Excel document saving in a protected cell the current version number. An additional requirement was give the option to the users to save in local PC the file they were working on and later on have the option to upload in SAP from local PC and continue working with the file. Comparing the version of BDS file and user file we were able to confirm that version file was OK.

      I hope this help you.


      Regards,

      Alfonso Rodriguez

      SAP ABAP Consultant

      (0) 
  2. Dren Selimi

    Hi Alfonso,

    I had a question about integrating Macros in this report,

    I try to call the macro with the execute_macro method but it is not working, maybe the place where I put it is the problem or I have to write extra code,

    the macro exists in the excel file I just want to execute it

    do you have any idea regarding this matter

    Regards,

    Dren

    (0) 
    1. Alfonso Rodríguez Pérez Post author

      Hi Dren,

      I think you should have to do it without problems.

      The method belongs to the proxy no to the spreadsheet object.

      Check method I_OI_DOCUMENT_PROXY->EXECUTE_MACRO

      in my example would be a method of object gc_document.

      Kind regards,

      Alfonso.

      (0) 
      1. Dren Selimi

        Hi Alfonso,

        Yes I am using that but I have the problem that when I pass parameters to the Macro I dont know how to ‘accept’ the parameters in my Macro

        Regards,

        Dren

        (0) 
        1. Alfonso Rodríguez Pérez Post author

          Hi Dren,

          Sorry, I don’t catch you when you say ‘accept’ parameters in my Macro…

          If you call method I_OI_DOCUMENT_PROXY->EXECUTE_MACRO and the macro in Excel is triggered I have to think that the problem must be in Macro code… I’m not expert in Macro develop… what I would do is simplify first the Macro just populating the parameters sent from SAP using MsgBoX (parameter) if parameters arrive to the Macro code the “problem” must be in Macro code itself.

          Good luck.

          Alfonso.

          (0) 
  3. Marco Sposa

    hello is it possible to open an excel focusing on a specific row? i though i could manage it with the method show_rows but i couldn’t .

    Regards

    (0) 

Leave a Reply