Purpose
Upload Excel to an SAP table control and show the result in Excel

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

In the InputScript we can either press "Enter" in SAP for each row entered, or first fill in all rows available on the screen and then press enter for a block of new rows.
If performance is not really critical we suggest that you choose the "row by row" approach, since this allows you to return any row-related error message to Excel and have it assigned to the right Excel row. Also, the scrolling becomes more complex when you use blocks of rows.

Example
In transaction MF42N we add an "Upload" button which allows the user to upload an Excel file into the SAP table. If any error occurs we skip the erroneous row and save the SAP error message in the Excel file. If no error occurs we set a status "OK" into the Excel row.

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

In the transaction MF42N it is a bit difficult to continue after erroneous inputs. If you can correct the errors, that's not a problem, but the InputScript doesn't know how to correct an error and must let the newly made entries be canceled before the next rows (if any) are entered. We solve this problem by pressing F12 (Cancel) in the SAP transaction after each error, which clears the new rows, but allows us to continue in the transaction by selecting "No" in the cancellation warning popup.


Video

 

Show video in full screen


.zip file with InputScript, JavaScript and sample Excel file


GuiXT Script
"saplbarm.e0500.txt"

Pushbutton (toolbar) "Upload" process="mf42n_upload.txt"


InputScript
"mf42n_upload.txt"

// select a file
SelectFile name="xfile" _
  title="Select the Excel file to upload" _
  filter="*.xlsx" _
  directory="E:\mf42n"
 
// any file selected? else no action
if not Q[ok]
  return "S: No file selected" -statusline
endif
 
// open Excel file via JavaScript
CallJS XL_open "&V[xfile]" "Uploading: &V[_title]"
 
// skip Excel header row
Set V[k] 1
 
// number of correct rows
Set V[n] 0
 
label next_item
Screen SAPLBARM.0500
 
 
label skip_item
  Set V[k] &V[k] + 1
  CallJS XL_getrow &V[k]
  if not V[rm61b_matnr]
    Enter "/ScrollToLine=1" table=T[SAPLBARM_TC500]
    Leave
  endif
 
  // skip Excel rows with OK-status (already entered)
  if V[itemstatus=OK]
   goto skip_item
  endif
 
  // fille table row 2 (empty row after scrolling)
  Set cell[SAPLBARM_TC500,RM61B-MATNR,2] "&V[rm61b_matnr]"
  Set cell[SAPLBARM_TC500,RM61B-ERFMG,2] "&V[rm61b_erfmg]"
  Set cell[SAPLBARM_TC500,RM61B-VERID,2] "&V[rm61b_verid]"
  Set cell[SAPLBARM_TC500,RM61B-WERKS,2] "&V[rm61b_werks]"
  Set cell[SAPLBARM_TC500,RM61B-PLWERK,2] "&V[rm61b_plwerk]"
  Set cell[SAPLBARM_TC500,RM61B-ALORT,2] "&V[rm61b_alort]"
 
 
// we need to press "cancel" if an error occurs
  Enter  onError="/12"
 
Screen SAPLBARM.0500
 
  // increase number of entered items
  Set V[n] &V[n] + 1

  // Excel feedback
  Set V[itemstatus] "OK"
  Set V[itemerror] ""
  CallJS XL_updaterow &V[k]

  Enter "/ScrollToLine=&V[n]" table=T[SAPLBARM_TC500]
  goto next_item
 
// "Cancel" popup after error
Screen SAPLSPO1.0200

 
// Excel feedback
  Set V[itemstatus] "ERR"
  Set V[itemerror]  "&V[_lasterror]"
  CallJS XL_updaterow &V[k]
  Clear V[_lasterror]

  Enter "=NO"      // "No" since we want to continue
 
Screen SAPLBARM.0500
  Enter "/ScrollToLine=&V[n]" table=T[SAPLBARM_TC500]
  goto next_item


JavaScript functions

// Excel interface object
var XL = null;
 
function XL_open(filename, caption) {
 
    XL = guixt.CreateObject(
"Excel.Application");
 
   
// show Excel in the foregound
    XL_window_foreground();
 
   
// display Excel window
    XL.Visible = true;
 
   
// set caption
    XL.caption = caption;
 
   
// open file
    XL.WorkBooks.Open(filename);
 
};
 

// Read Excel row values into GuiXT variables
function XL_getrow(k) {
    // Read Excel row into GuiXT variables
    guixt.Set("rm61b_matnr", XL.Cells(k, 1).Value);
    guixt.Set("rm61b_erfmg", XL.Cells(k, 2).Value);
    guixt.Set("rm61b_verid", XL.Cells(k, 3).Value);
    guixt.Set("rm61b_werks", XL.Cells(k, 4).Value);
    guixt.Set("rm61b_plwerk", XL.Cells(k, 5).Value);
    guixt.Set("rm61b_alort", XL.Cells(k, 6).Value);

   
// item status OK or ERR or Space
    guixt.Set("itemstatus", XL.Cells(k, 7).Value);
};
 
// Update Excel row from GuiXT variables
function XL_updaterow(k) {
 
   
// status OK or Error
    XL.Cells(k, 7).Value = guixt.Get("itemstatus");

   
// color depending on status
    if (XL.Cells(k, 7).Value == "OK") {
        XL.Cells(k, 7).Interior.ColorIndex = 2;
    }
   
else {
        XL.Cells(k, 7).Interior.ColorIndex = 46
    };

   
// date and time
    var today = new Date();
    XL.Cells(k, 8).Value = today.toLocaleDateString() + " " +                                      today.toLocaleTimeString();

    XL.Cells(k, 9).Value = guixt.Get("itemerror");

 };

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

 

Components
InputAssistant + Controls