Scripting: Uploading an Excel Spreadsheet on SAP GUI for windows 7.60

Introduction

For posting shipping invoice in tcode MIRO/MIR7, user must select condition type manually which valid for that vendor invoice. Imagine if the user had to select dozens of rows (condition types) from hundreds of rows. Unfortunately, no BAPI/FM for do that. So, impossible for create ABAP program for speed up that process.

SAP Screen Personas scripting can speed up that process. Very easy for upload excel file on web gui using method in wiki – Scripting: Uploading an Excel Spreadsheet, but that method can not running on SAP GUI for Windows.

This blog will explain how to upload excel/txt file using SAP Screen Personas (3.0 sp 10) scripting on SAP GUI for Windows 7.60.

Note:

• Search help to search for the file name from local drive not working properly on SAP GUI for Windows 7.50.
• The security configuration for uploading from a local file must allow it to avoid the ‘allow / deny’ pop-up window.
• FM to read excel / txt file from local drive only runs on SAP GUI for Windows (both version 7.50 sp 10 and 7.60), but will get error if running on webgui because security configuration to upload from local file can’t allow in webgui. So, the javascript debugger on webgui doesn’t allow.

Main

Create search help for get file name

Step 1.

Refer to Personas 3.0 Search Help Scenario: Code triggered F4 Help pop-up by Krishna Kishor Kammaje (thank you for great blog), create FM (copy from FM F4IF_SHLP_EXIT_EXAMPLE) to search for the file name from local drive with coding as follows:

IF CALLCONTROL-STEP = 'DISP'.
*   PERFORM AUTHORITY_CHECK TABLES RECORD_TAB SHLP_TAB
*                           CHANGING SHLP CALLCONTROL.
    DATA: w_file_tab TYPE filetable,
          w1 LIKE LINE OF w_file_tab,
          w_rc TYPE i,
          ls_record_tab type SEAHLPRES.

    CALL METHOD cl_gui_frontend_services=>file_open_dialog
      EXPORTING
        default_extension       = 'txt'
        file_filter = |{ cl_gui_frontend_services=>FILETYPE_EXCEL } { cl_gui_frontend_services=>FILETYPE_TEXT }|
      CHANGING
        file_table              = w_file_tab
        rc                      = w_rc
      EXCEPTIONS
        file_open_dialog_failed = 1
        cntl_error              = 2
        error_no_gui            = 3
        not_supported_by_gui    = 4
        OTHERS                  = 5.
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
    READ TABLE w_file_tab INTO w1 INDEX 1.
    IF sy-subrc = 0.
      ls_record_tab-string = w1-filename.
      append ls_record_tab to record_tab.
    ENDIF.
    EXIT.
  ENDIF.
Step 2.

Create search help and assign the above FM as the search help ex

Step 2.

Create search help and assign the above FM as the search help exit.

Step 3.

Assign that custom search help using method in wiki – Using Search Helps.

Create RFC FM for read data from Excel/Txt file

Step 1.

with coding as follows:

FUNCTION ZRFC_FILE_TO_INTERNAL_TABLE.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(P_FILENAME) LIKE  RLGRAP-FILENAME
*"     VALUE(P_COL) TYPE  I
*"     VALUE(P_ROW_DATA_START) TYPE  I DEFAULT 2
*"  TABLES
*"      LT_DATA STRUCTURE  MEDISPLCHLONG
*"----------------------------------------------------------------------
FIELD-SYMBOLS : <gt_data>  TYPE STANDARD TABLE,
                <ls_data>  TYPE any,
                <lv_field> TYPE any.

data: lv_filename          TYPE string,
      lv_data              TYPE MEDISPLCHLONG,
      lv_filelength        TYPE i,
      lt_records           TYPE solix_tab,
      lv_record            TYPE SOLIX,
      lv_headerxstring     TYPE xstring,
      lv_numberofcolumns   TYPE i,
      lv_date_string       TYPE string,
      lv_target_date_field TYPE datum,
      lt_file_data         TYPE table of text4096,
      lv_file_data         TYPE text4096.

                    .

  lv_filename = P_FILENAME.
  break ab_andy.

  if lv_filename cp '*.TXT'.
    CALL FUNCTION 'GUI_UPLOAD'
      EXPORTING
        FILENAME                      = lv_filename
       FILETYPE                      = 'ASC'
*     IMPORTING
*       FILELENGTH                    =
      TABLES
        DATA_TAB                      = lt_file_data
     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
       OTHERS                        = 17
              .
*    break ab_andy.
    IF SY-SUBRC <> 0.
*     Implement suitable error handling here
    else.
      loop at lt_file_data into lv_file_data.
        if sy-tabix < P_ROW_DATA_START.
          continue.
        endif.
        lv_data-tabtext = lv_file_data.
        append lv_data to lt_data.
      endloop.
    ENDIF.
  else.
    CALL FUNCTION 'GUI_UPLOAD'
      EXPORTING
        filename                = lv_filename
        filetype                = 'BIN'
      IMPORTING
        filelength              = lv_filelength
        header                  = lv_headerxstring
      TABLES
        data_tab                = lt_records
      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
        OTHERS                  = 17.

    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
      EXPORTING
        input_length = lv_filelength
      IMPORTING
        buffer       = lv_headerxstring
      TABLES
        binary_tab   = lt_records
      EXCEPTIONS
        failed       = 1
        OTHERS       = 2.

    IF sy-subrc <> 0.
      "Implement suitable error handling here
    ENDIF.

    DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .

    TRY .
        lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
                                document_name = lv_filename
                                xdocument     = lv_headerxstring ) .
      CATCH cx_fdt_excel_core.
        "Implement suitable error handling here
    ENDTRY .
    "Get List of Worksheets
    lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING
        worksheet_names = DATA(lt_worksheets) ).

    IF NOT lt_worksheets IS INITIAL.
      READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.

      DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
                                               lv_woksheetname ).
      "now you have excel work sheet data in dyanmic internal table
      ASSIGN lo_data_ref->* TO <gt_data>.
    ENDIF.

    "you could find out number of columns dynamically from table <gt_data>
    lv_numberofcolumns = p_col.

    LOOP AT <gt_data> ASSIGNING <ls_data> FROM P_ROW_DATA_START.

      "processing columns
      clear lv_data.
      DO lv_numberofcolumns TIMES.

        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_field> .
        IF sy-subrc = 0 .
          if lv_data-tabtext is initial.
            lv_data-tabtext = <lv_field>.
          else.
            concatenate lv_data-tabtext <lv_field> into lv_data-tabtext separated by cl_abap_char_utilities=>horizontal_tab.
          endif.
        ENDIF.
      ENDDO .
      if lv_data is not initial.
        append lv_data to lt_data.
      endif.
    ENDLOOP .
  endif.





ENDFUNCTION.

Step 2.

Assign RFC FM using method in wiki – How to call Remote Enabled ABAP Function Modules in Personas 3.0.

Create Button Script

For process file, create ‘upload’ script button

with coding as follows:

var input = session.findById("wnd[0]/usr/ctxtPersonas_161000174728578").text;
input = input.replace(/\\/g, "\\\\");
var oRFC = session.createRFC("ZRFC_FILE_TO_INTERNAL_TABLE");
oRFC.setParameter("P_COL", "28"); //number of column
oRFC.setParameter("P_FILENAME", input);
oRFC.setParameter("P_ROW_DATA_START", "2"); //start of row data
oRFC.setParameter("LT_DATA", [{"TABTEXT":""}]);
oRFC.requestResults(["LT_DATA"]);
oRFC.send();
var _LT_DATA = oRFC.getResultObject("LT_DATA");
 
for (var index = 0; index < _LT_DATA.length; index++) {
var data = _LT_DATA[index].TABTEXT.split('\t');
//Input Invoice Date
		session.findById("wnd[0]/usr/subHEADER_AND_ITEMS:SAPLMR1M:6005/tabsHEADER/tabpHEADER_TOTAL/ssubHEADER_SCREEN:SAPLFDCB:0010/ctxtINVFO-BLDAT").text = data[1];		

session.findById("wnd[0]/usr/subHEADER_AND_ITEMS:SAPLMR1M:6005/tabsHEADER/tabpHEADER_TOTAL/ssubHEADER_SCREEN:SAPLFDCB:0010/ctxtINVFO-BLDAT").setFocus();
		session.findById("wnd[0]").sendVKey(0);

// continue coding for the next entry to another fields using the data in the data[] array
...........
}

Conclusion

Apart from using webgui, the SAP Screen Personas script can upload excel / txt files using the SAP GUI for Windows which is faster than the process on webgui.