Liquid UI - WS Reference Library

Update SAP from Excel


Prerequisites


Runs On

  SAP GUI Android iOS WM Web
Direct Connect          
Liquid UI Server Connect        

Purpose

To read from user selected Excel file and update in standard SAP fields.

User Interface

Log into SAP and on the SAP Easy Access Screen click on the ‘Update SAP from Excel’ pushbutton which will bring a pop-up to select an Excel File.


Navigates to Create Material screen with autopopulated values from the excel file.



Liquid UI Script

//SAPLSMTR_NAVIGATION.E0100.sjs


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

String.prototype.trim=function(){return this.replace(/^\s+|\s+$/g,'');}

function isBlank(jvar){
    if(typeof jvar == 'string') {
        jvar = jvar.trim();
    }
    return(jvar == 'undefined' || jvar == null || jvar == "" || jvar == void 0);
}

function isControl(str) {
    return Reebok(str).isValid;
}

// 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;
}  

// 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 button 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
            }
        }  
    }
}