Read and write Excel interactively

Call up JavaScript functions that use the "Excel.Application" object to read and write spreadsheet cells while executing an InputScript.

We start with an Excel file which in each row contains a material number, a new standard price and a date.

For each row we update the standard price in SAP via transaction MM02. The current price is updated in the Excel file. We set a status column in the Excel file to "OK" or "Error". In case of "Error" we save the error message into the current Excel row.

If the InputScript is run again for the same file we omit all rows with status "OK".



Show video in full screen

.zip file with InputScript, JavaScript and sample Excel file


// Select Excel file
SelectFile name="xlsfile" _
"*.xls;*.xlsx" _
title="Choose Excel file with new prices" _

// done?
if not Q[ok]
  Return "S: No file selected"  -statusline

// Open file in Excel
CallJS XL_open "&V[xlsfile]" "Price changes"

// Row number
Set V[k] 0

// Count updated materials
Set V[k_ok] 0
Set V[k_skip] 0
Set V[k_err] 0

label next_material

Enter "/NMM02"

// Change Material (Initial Screen)
Screen SAPLMGMM.0060

  // Change screen title during processing
  Title "Please wait.... changing material &V[MM02_matnr]"

label read_row_from_excel
  Set V[k] &V[k] + 1
  CallJS XL_getrow  &V[k]
  if not V[MM02_matnr]
    goto all_done

  // status ok? then skip this one, already done
  if  V[MM02_stats=OK]
    Set V[k_skip] &V[k_skip] + 1
    goto read_row_from_excel

  Set F[RMMG1-MATNR]    "&V[MM02_matnr]"

  // Clear variables for material text and current price
  Set V[MM02_maktx]  ""
  Set V[MM02_stprs]  ""

  // View selection
  Enter "/5"    OnError="Continue"
  goto error     

// Select View(s) 
Screen SAPLMGMM.0070 

  // Select 1st view
Set cell[Table,0,1] "X" 
  goto error     


Screen SAPLMGMM.4004
  // goto Accounting 1
Enter "=SP24"   OnError="Continue"
  goto error     


// Organizational Levels
Screen SAPLMGMM.0081
  Set F[RMMG1-WERKS]    "&V[MM02_werks]"
  goto error

// error popup for wrong plant
Screen SAPMSDYP.0010

   // error message on screen in this case
   Set V[_lasterror] "&F[MESSTXT1]"
   goto error

// Change standard price
  Set  F[MBEW-ZKPRS_1] "&V[MM02_zkprs]"     
  Set  F[MBEW-ZKDAT_1]  "&V[MM02_zkdat]"

  // Material text and current price
  Set V[MM02_maktx]  "&F[MAKT-MAKTX]"
  Set V[MM02_stprs]  "&F[MBEW-STPRS]"

  // Save changes
  Enter "/11"  OnError="Continue" 
goto error

// Change Material (Initial Screen)
Screen SAPLMGMM.0060

  // Set status fields
  Set V[MM02_stats] "OK"
  Set V[MM02_error] ""
  CallJS XL_updaterow &V[k]

  // increase ok count
  Set V[k_ok] &V[k_ok] + 1

   // process next material
goto next_material

// error occured   

label error
  Set V[MM02_stats] "Error"
  Set V[MM02_error] "&V[_lasterror]"
  CallJS XL_updaterow &V[k]

  // increase error count
  Set V[k_err] &V[k_err] + 1
  goto next_material

label all_done
Set V[k] &V[k] - 1
Message "S: &V[k] materials,  &V[k_ok] changed, &V[k_skip] skipped, &V[k_err] errors" -statusline

Enter "/N"

JavaScript function

// Excel interface object 
 var XL = null;

function XL_open(filename, caption) {

    XL = guixt.CreateObject("Excel.Application");

    // show Excel in the foregound 

    // display Excel window
    XL.Visible = true;

    // set caption
    XL.caption = caption;

    // open file


// Read Excel row values into GuiXT variables
function XL_getrow(k) {
    // Read Excel row into GuiXT variables
    guixt.Set("MM02_matnr", XL.Cells(k, 1).Value); // material
    guixt.Set("MM02_werks", XL.Cells(k, 2).Value); // plant
    guixt.Set("MM02_zkprs", XL.Cells(k, 3).Value); // price
    guixt.Set("MM02_zkdat", XL.Cells(k, 4).Value); // date
    guixt.Set("MM02_stats", XL.Cells(k, 5).Value); // status


// Update Excel row from GuiXT variables
function XL_updaterow(k) {

    // status OK or Error
    XL.Cells(k, 5).Value = guixt.Get("MM02_stats");

    // color depending on status
    if (XL.Cells(k, 5).Value == "OK") {
        XL.Cells(k, 5).Interior.ColorIndex = 2;
    else {
        XL.Cells(k, 5).Interior.ColorIndex = 46
     // format date
    var today = new Date();
    var dd = today.getDate();
    var mm = today.getMonth() + 1; //January is 0!
    var yyyy = today.getFullYear();

    XL.Cells(k, 6).Value = dd + "." + mm + "." + yyyy
    XL.Cells(k, 7).Value = guixt.Get("MM02_maktx");
    XL.Cells(k, 8).Value = guixt.Get("MM02_stprs");
    XL.Cells(k, 9).Value = guixt.Get("MM02_error");


function XL_window_foreground() {

    // constants for WindowState from MS documentation
    var xlMinimized = -4140;
    var xlNormal = -4143;

    // change window state to minimized and back to normal
    XL.WindowState = xlMinimized;
    XL.WindowState = xlNormal;


InputAssistant + Controls