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