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.
//
select a file
SelectFilename="xfile" _ title="Select the Excel file to upload" _ filter="*.xlsx" _ directory="E:\mf42n" //
any file selected? else no action ifnotQ[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
SetV[k] 1 //
number of
correct
rows
SetV[n] 0
label next_item
Screen SAPLBARM.0500 label skip_item SetV[k]&V[k] + 1 CallJS XL_getrow
&V[k] ifnotV[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 EnteronError="/12"
Screen SAPLBARM.0500 // increase number of entered items SetV[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; };