Purpose
Read and write Excel interactively

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

Example
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".

Video

 

Show video in full screen


.zip file with InputScript, JavaScript and sample Excel file


InputScript

GuiXT
// Select Excel file
SelectFile name="xlsfile" _
    filter="*.xls;*.xlsx" _
     title="Choose Excel file with new prices" _
     directory="C:\temp"

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

// 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
  endif

  // 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
  endif

  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" 
 
  Enter  onError="Continue"
  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]"
 
  Enter onError="Continue"
  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
Screen SAPLMGMM.4000
  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
Set V[m1] "S: &V[k] materials, "
Set V[m2] "&V[k_ok] changed, "
Set V[m3] "&V[k_skip] skipped, "
Set V[m4] "&V[k_err] errors"

Message "&V[m1]&V[m2]&V[m3]&V[m4]" -statusline


Enter "/N"


JavaScript function

JavaScript

Components
InputAssistant + Controls