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.
- Load wsoffice.dll file
- Add a toolbar push button
- Add function to show file open dialog
- 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.
- 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}); - 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;
} - 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
- 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.
- Now, you will be navigated to the Create Material (Initial Screen) with auto-populated values from the selected Excel file, as shown below.