16.04 Update SAP fields with excel data

Prerequisites

• Products: Liquid UI WS, Liquid UI Server or Local DLL, Client Software
• Commands: load(), pushbutton(), set()

Purpose

In this article, you will learn how to read data from a user-selected Excel file and update it in the standard SAP fields on the Easy Access screen.

To achieve this, please follow the sequence.

1. Load wsoffice.dll file 
2. Add a toolbar push button
3. Add function to show file open dialog
4. Add function to open excel file from the file selection dialog

User Interface

//Create this file inside your script folder for customizing the SAP Easy Access screen: SAPLSMTR_NAVIGATION.E0100.sjs

//Now, let's start adding the Liquid UI script to the above file and save it.

1. Load the wsoffice.dll file and create a toolbar push button with the label “Update SAP from Excel” to execute the process called testUpdateSAPFromExcel, when clicked.

   // Need to load this file to display File Selection pop-up
   load('wsoffice');
     
   //create a toolbar pushbutton "Update SAP from Excel"
   pushbutton([TOOLBAR],"@8T@Update SAP from Excel", "/nMM01", {"process":testUpdateSAPFromExcel});

   
    
2. Add a function titled selectFileDialog to show file open dialog.
   
   

   // Function to Show File Open Dialog
   function selectFileDialog(szPrompt){  
       if(szPrompt==void 0) 
           szPrompt = 'Select Excel File';
       var dialog = new ActiveXObject('MsComDlg.CommonDialog');  
       // dialog.Filter='All Files(*.*)|*.*';  
       dialog.Filter='(*.xl*)|*.xl*';  // BD
       dialog.MaxFileSize=32767;  
       //dialog.AllowMultiSelect = true;
       dialog.DialogTitle=szPrompt;
       dialog.Flags=0x200|0x80000|0x800|0x4|0x200000  
       dialog.ShowOpen();  
       //var ret = dialog.FileTitle;
       var ret = dialog.FileName;
       dialog = void 0;
       return ret;
   }  

3. Add a function titled openExcel to open an excel file from the file selection dialog
    

   // Function to open Excel File From The File Selection Dialog
   function openExcel(filename) {
       if(excelObj == void 0)
           excelObj = new ActiveXObject('Excel.Application');
       excelBook = excelObj.Workbooks.Open(filename);
       excelObj.Visible = true;
       excelObj.ScreenUpdating = true;
   }
   
   function testUpdateSAPFromExcel(){
       OPEN_EXCEL_FILE:;
       if(excelObj == void 0) {
           excelFileName = selectFileDialog('Select Excel File');
           if(excelFileName.length) {
               openExcel(excelFileName);
               excelSheet = excelBook.ActiveSheet;
           } else {
               message('E: No Excel File Selected');
               return;                // If Problem opening selected excel file, stop the process
           }
       } else {                    // Excel is already open (Manually Opened or Re-run for Error Processing)
           try {
               excelSheet = excelBook.ActiveSheet;
               // Check to see if we can read cell value, if not then Re-Open Excel File
               var cellCheckValue = excelSheet.Cells(1, 2).Value;    
           } 
           catch(err) {
               delete excelObj;
               goto OPEN_EXCEL_FILE;
           }
       }
       
       onscreen 'SAPLMGMM.0060'    
           updateSAPFromExcel(excelSheet,2,1,4);    // Updates Industry sector, Material Type and Copy from Material
           enter();                                // Select Views pop-up is displayed    
   }
   
   // Applicable to only edit field and check box
   // For Table and Radio buttons different logic applies
   function updateSAPFromExcel(excelActiveSheet, nCurrentRow, nStartColumn, nEndColumn) {
       excelColumnSAPHeadingsRow = 1;
       var scrElementName = '';
       var scrElementValue = '';
           
       for(var currentCol = nStartColumn; currentCol < nEndColumn; currentCol++) {
           scrElementName = excelActiveSheet.Cells(excelColumnSAPHeadingsRow,currentCol).Value;
           scrElementValue = excelActiveSheet.Cells(nCurrentRow,currentCol).Value;
   
           println('\n------------ scrElementName:'+scrElementName+':');
           println('\n------------ scrElementValue1:'+scrElementValue+':\n');
   
           if(scrElementValue == undefined || typeof(scrElementValue) == 'undefined' || isBlank(scrElementValue)) {
               scrElementValue = '';
               continue;            // Next Element within for loop
           }
           
           if(isControl('F['+scrElementName+']')){
               // if(scrElementValue.trim() == 'NULL'){
               if(scrElementValue.toString().trim().toUpperCase() == 'NULL'){
                   set('F['+scrElementName+']','');                                    //set the value to blank
               } else {
                   set('F['+scrElementName+']','&V[scrElementValue]');                    //set the value to user entered value in excel
               }
           } else if(isControl('C['+scrElementName+']')){
               // if(scrElementValue.trim() == 'NULL'){
               if(scrElementValue.toString().trim().toUpperCase() == 'NULL'){
                   set('C['+scrElementName+']',' ');                                    //set the value to blank
               } else {
                   set('C['+scrElementName+']','X');                                    //set the value
               }
           }  
       }
   }
   
   

SAP Process

1. Logon to SAP. On the SAP Easy Access screen, you can see the “Update SAP from Excel” toolbar pushbutton. Click on the “Update SAP from Excel” pushbutton displays a pop-up to select an Excel file. Select the desired file and click on open, as shown below. 
    
   
    
2. Now, you will be navigated to the Create Material (Initial Screen) with auto-populated values from the selected Excel file, as shown below.