Tip: How to Build Your Own SAP Accelerator

Since a few month are very interesting Accelerators for SAP at UiPath Marketplace available. In my opinion is the approach of mapping technical interfaces into Excel spreadsheets extremely interesting. That is why I have extended my RFM code generator so that it can also generate a corresponding Excel workbook from the interface description of an remote enabled function module, aka RFC. This provides an easy way to create a frame for SAP API automation. And precisely this aspect I would like to light on in this post.

Preparation

First we create the necessary prerequisites in an SAP system. Two tables with a foreign key relation. The first table ZUIPATH_NAMES contains the last and first name. The second table ZUIPATH_EMAILS contains the type of the e-mail address and the e-mail address. They have a relationship via the key field ID_NAMES.

image

To fill this tables with data we need an RFM, in this case we create Z_SET_DATA. The import parameters are the first and last name and a table with e-mail addresses. The export parameter is a table with the BAPIRET2 structure, a common return value in the context of BAPIs.

image

The ABAP code is easy to understand. At first the last ID number for the names is detected. Then the names are written into the table. If successful an S message is written to the return structure (S = Success), otherwise E (E = Error). Now the last ID number for the e-mails is detected. Then the e-mail addresses are written into the table. If successful an S message is written, otherwise E.

FUNCTION Z_SET_DATA.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(LAST_NAME) TYPE  CHAR64
*"     VALUE(FIRST_NAME) TYPE  CHAR64
*"     VALUE(EMAIL_ADDRESSES) TYPE  Z_TAB_EMAILS
*"  EXPORTING
*"     VALUE(RETURN) TYPE  BAPIRET2_TAB
*"----------------------------------------------------------------------

DATA:
  ls_names TYPE ZUIPATH_NAMES,
  lv_max_name_id TYPE ZUIPATH_NAMES-ID_NAMES,
  lv_max_email_id TYPE ZUIPATH_EMAILS-ID_EMAILS,
  lt_emails TYPE Z_TAB_EMAILS,
  lv_return TYPE BAPIRET2.

FIELD-SYMBOLS:
  <ls_emails> TYPE ZUIPATH_EMAILS.

SELECT MAX( ID_NAMES ) FROM ZUIPATH_NAMES INTO lv_max_name_id.
lv_max_name_id = lv_max_name_id + 1.

ls_names-LAST_NAME = LAST_NAME.
ls_names-FIRST_NAME = FIRST_NAME.
ls_names-ID_NAMES = lv_max_name_id.
INSERT ZUIPATH_NAMES FROM ls_names.

IF sy-subrc = 0.
  lv_return-TYPE = 'S'.
  lv_return-MESSAGE = 'Add name data successfully.'.
  APPEND lv_return TO RETURN.

  SELECT MAX( ID_EMAILS ) FROM ZUIPATH_EMAILS INTO lv_max_email_id.

  lt_emails = EMAIL_ADDRESSES.
  LOOP AT lt_emails ASSIGNING <ls_emails>.
    lv_max_email_id = lv_max_email_id + 1.
    <ls_emails>-ID_EMAILS = lv_max_email_id.
    <ls_emails>-ID_NAMES = lv_max_name_id.
  ENDLOOP.
  INSERT ZUIPATH_EMAILS FROM TABLE lt_emails.

  IF sy-subrc = 0.
    lv_return-TYPE ='S'.
    lv_return-MESSAGE = 'Add E-Mail data successfully.'.
  ELSE.
    lv_return-TYPE ='E'.
    lv_return-MESSAGE = 'Error at add E-Mail data.'.
  ENDIF.
  APPEND lv_return TO RETURN.

ELSE.
  lv_return-TYPE = 'E'.
  lv_return-MESSAGE = 'Error at add name data.'.
  APPEND lv_return TO RETURN.
ENDIF.

ENDFUNCTION.

Now all preparations are done in the SAP system.

Generating Excel Mapping

With the RFM Code Generator it is now possible to generate a corresponding Excel Mapping. Select and click - it could not be simpler.

image

From Excel Into SAP

To write the data from the Excel workbook into the SAP system I use the Invoke Code activity. It contains a combination of the generated code from the RFM Code Generator with Excel access. This code looks complicated, but it is not. At first we set the SAP connection parameters and create the function Z_SET_DATA. Then we create an Excel instance and open our document. We loop over Z_SET_DATA sheet to read the names and over EMAIL_ADDRESSES sheet to read the corresponding e-mail addresses. When we have all the data we invoke the RFM. Last but not least we get the information from the return value and write it back into the sheet RETURN. When all records have been processed, the Excel workbook is closed.

//----------------------------------------------------------------------

try {

  RfcConfigParameters cfgParams = new RfcConfigParameters();
  cfgParams.Add("NAME", "Test");
  cfgParams.Add("ASHOST", "4711");
  cfgParams.Add("CLIENT", "099");
  cfgParams.Add("USER", "BCUSER");
  cfgParams.Add("PASSWD", "secret");
  cfgParams.Add("LANGUAGE", "EN");

  RfcDestination destination = RfcDestinationManager.GetDestination(cfgParams);
  IRfcFunction rfcFunction = destination.Repository.CreateFunction("Z_SET_DATA");

  IRfcTable tblEMAIL_ADDRESSES = rfcFunction.GetTable("EMAIL_ADDRESSES");

  //-Create Excel application-------------------------------------------
  Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
  //xlApp.Visible = true;

  //-Open generated Excel file------------------------------------------
  string xlPath = @"c:\Users\bambi\Documents\UiPath\SAPTest\SRC_Z_SET_DATA.xlsx";
  Microsoft.Office.Interop.Excel.Workbook wkbSRC_Z_SET_DATA = xlApp.Workbooks.Open(xlPath);
  
  Microsoft.Office.Interop.Excel.Worksheet wksZ_SET_DATA = (Microsoft.Office.Interop.Excel.Worksheet)wkbSRC_Z_SET_DATA.Sheets["Z_SET_DATA"];
  Microsoft.Office.Interop.Excel.Worksheet wksEMAIL_ADDRESSES = (Microsoft.Office.Interop.Excel.Worksheet)wkbSRC_Z_SET_DATA.Sheets["EMAIL_ADDRESSES"];
  Microsoft.Office.Interop.Excel.Worksheet wksRETURN = (Microsoft.Office.Interop.Excel.Worksheet)wkbSRC_Z_SET_DATA.Sheets["RETURN"];

  int wksRETURN_Row = 5;

  //-Loop over Z_SET_DATA sheet to set the data of the RFC function-----
  for (int wksZ_SET_DATA_Row = 8; wksZ_SET_DATA_Row <= wksZ_SET_DATA.UsedRange.Rows.Count; 
       wksZ_SET_DATA_Row++) {

    string wksZ_SET_DATA_Number =
  ((Microsoft.Office.Interop.Excel.Range)wksZ_SET_DATA.Cells[wksZ_SET_DATA_Row, 1]).Text.ToString();
    //-Set LAST_NAME----------------------------------------------------
    rfcFunction.SetValue("LAST_NAME", 
  ((Microsoft.Office.Interop.Excel.Range)wksZ_SET_DATA.Cells[wksZ_SET_DATA_Row, 3]).Text.ToString());
    //-Set FIRST_NAME---------------------------------------------------
    rfcFunction.SetValue("FIRST_NAME",
  ((Microsoft.Office.Interop.Excel.Range)wksZ_SET_DATA.Cells[wksZ_SET_DATA_Row, 4]).Text.ToString());

    tblEMAIL_ADDRESSES.Clear();
    //-Loop over EMAIL_ADDRESSES sheet to set the table data------------
    for (int wksEMAIL_ADDRESSES_Row = 5; wksEMAIL_ADDRESSES_Row <= wksEMAIL_ADDRESSES.UsedRange.Rows.Count; wksEMAIL_ADDRESSES_Row++) {
  string wksEMAIL_ADDRESSES_Number = ((Microsoft.Office.Interop.Excel.Range)wksEMAIL_ADDRESSES.Cells[wksEMAIL_ADDRESSES_Row, 1]).Text.ToString();
      if(wksZ_SET_DATA_Number == wksEMAIL_ADDRESSES_Number) {
        tblEMAIL_ADDRESSES.Append();
        //-Set EMAIL_TYPE-----------------------------------------------
        tblEMAIL_ADDRESSES.SetValue("EMAIL_TYPE",
  ((Microsoft.Office.Interop.Excel.Range)wksEMAIL_ADDRESSES.Cells[wksEMAIL_ADDRESSES_Row, 6]).Text.ToString());
        //-Set EMAIL_ADDRESS--------------------------------------------
        tblEMAIL_ADDRESSES.SetValue("EMAIL_ADDRESS",
  ((Microsoft.Office.Interop.Excel.Range)wksEMAIL_ADDRESSES.Cells[wksEMAIL_ADDRESSES_Row, 7]).Text.ToString());
      }
    }

    //-Invoke RFC function----------------------------------------------
    rfcFunction.Invoke(destination);

    //-Read return value from BAPIRET2 table...-------------------------
    IRfcTable tblRETURN = rfcFunction.GetTable("RETURN");
    foreach(var Record in tblRETURN) {
      //...and write the result into the RETURN worksheet---------------
      wksRETURN.Cells[wksRETURN_Row, 1] = wksZ_SET_DATA_Number;
      wksRETURN.Cells[wksRETURN_Row, 3] = Record.GetString("TYPE");
  wksRETURN.Cells[wksRETURN_Row, 6] = Record.GetString("MESSAGE");
    wksRETURN_Row += 1;
    }

  }

  //-Save and close Excel file and quit Excel application---------------
  wkbSRC_Z_SET_DATA.Close(true);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(wksZ_SET_DATA);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(wksEMAIL_ADDRESSES);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(wkbSRC_Z_SET_DATA);
  xlApp.Quit();
  System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

//----------------------------------------------------------------------

From Theory to Practice

Now it is time to test this realization. We fill the sheet Z_SET_DATA with four known persons.

Also we fill the sheet EMAIL_ADDRESSES.

We create a simple sequence with only one Invoke Code activity and execute it.

image

Nothing happens here, but we can find the result in the Excel sheet RETURN …

… and in the SAP system.

Conclusion

The mapping of an SAP RFM interface into a Microsoft Excel sheet is a great approach. With a little bit of coding you can create very compact and fast activities to use individual SAP APIs via RFMs. The RFM Code Generator offers the best conditions for this. Even when the whole representation still has a very technical focus, is this approach a big step to usability.

Perspectives

  • The RFM Code Generator is enhanced to generate corresponding code when creating an Excel workbook.

  • The mapping of the technical interface is certainly not the primary representation for the subject matter expert. A level of abstraction between this business perspective and the technical interface seems to be profitable here. In this case this could be an additional sheet containing the data (first name, last name, e-mail type and address) which will be automatically distributed to the corresponding sheets.

We will see what the future brings.

6 Likes

HI @StefanSchnell

Absolutely amazing idea which gives a lot of flexibility and easy of use for UiPath Accelerators.

Let me try it out by my own!

Thanks for the contribution!

Best regards, Lev

1 Like