Transfer Values from Spreadsheet into Formulas

Use

You use this function to let values flow into the cost estimate that you have not stored in data structures of the SAP System but instead in a spreadsheet. As a result, you can take account of values, where their transfer into the SAP System and subsequent evaluation is time-consuming or not at all possible.

Note Note

You create a cost estimate for a product for the production of which you require a new machine that was not procured at the time of the cost estimate. For this reason, you cannot determine the incurred machine hour rate from the SAP System, instead you must bring this cost estimate element into the calculation in a different way. To do this, you use an additional calculation that you store in a MS Excel file and the results of which you can include in a formula with the help of the functions presented here.

End of the note.

Prerequisites

You can only use the formula function described here if Microsoft Excel 97 (or higher) is installed on the PC on which you want to calculate the cost estimate. However, this restriction only applies when you actually want to calculate the cost estimate. If you only want to display the results of a cost estimate that has already been calculated then you do not need Microsoft Excel.

Features

You can access values that are saved in an Excel sheet in different ways. For each of these different ways of access the system offers you a function specially intended for that in the formula editor:

  • READ_EXCEL_SHEET_LOCAL ( Read from local Excel sheet ): You can access values of an Excel file that are in the file system of your PC or a linked network server with this function. If you want to use this function, you should make sure with suitable organizational measures that the Excel sheet is always in its storage location that you have transferred as a parameter to the function within the formula.

    This function is useful if you want to read data from a file that was often subject to value changes, but the cell positions (or named cell areas) remain constant. You can save yourself the constantly recurring import of the file into the SAP System by storing the file in a fixed defined directory of a file server. For example, this can be a sensible procedure if an employee who does not have any access authorization for the SAP System should make the updated file available.

  • READ_EXCEL_VIA_ATTACHMENT_OBJ ( Read from Excel sheet (attachment) in the respective object ): You can access values of an Excel file that you have added to an object as an attachment in the worklist with this function. This function is useful if you want to store additional data on an object (material, activity) that you want to access in a specific cost estimate.

You have different possibilities to address the desired value in the Excel file with both of these functions:

  • If several files were added to an object as an attachment, you enter the name of the desired file.

  • If several worksheets are contained within the Excel file and the desired value cannot be addressed using a uniquely named cell area, you also enter the name of the desired worksheet within the file.

  • If you have defined a named cell area for the desired value (with the Microsoft Excel function Start of the navigation path Insert Next navigation step Name Next navigation step Define End of the navigation path ) then enter this to determine the value.Otherwise, specify the desired cell in the usual way (for example, “A1” for the cell in the upper left corner of the worksheet).

Activities

To use the functions named in a formula for a cost estimate, insert the desired function in the formula editor into the formula and determine the actual parameter, which you want to transfer to the function at runtime. The functions have the following parameters (in the sequence specified):

READ_EXCEL_SHEET_LOCALFunction

  • path

    Completely qualified path to the Excel file; character string (255 characters); mandatory

  • sheet

    Name of the worksheet within the Excel file; character string (255 strings); optional if the file only contains one worksheet otherwise mandatory

  • cell

    Description of cell area from which the value should be read; character string (255 characters); optional

  • alias

    named cell area from which the value should be read; character string (255 characters); optional

    Note: The parameters sheet , cell , and alias are in the following proportion to each other:

    • If you fill the parameter alias , you do not need to enter anything for sheet and cell .

    • If you do not fill the parameter alias , you have to transfer values for sheet and cell . You can omit the value for sheet if the file only contains one worksheet.

  • currency

    Description of a cell of the Excel file that contains a currency ID in the case of monetary amounts; character string; optional

  • value

    Amount for the currency transferred as currency ; numerical; mandatory

READ_EXCEL_VIA_ATTACHMENT_OBJFunction

  • sheet

    Name of the worksheet within the Excel file; character string (255 strings); optional if the file only contains one worksheet otherwise mandatory

  • object_id

    Technical key of the object; integer (10 digits); mandatory. The easiest way to supply this parameter is by calling up the variable OBJECT_ID .

  • cell

    Description of cell area from which the value should be read; character string (255 characters); optional

  • alias

    named cell area from which the value should be read; character string (255 characters); optional

    Note: The parameters sheet , cell , and alias are in the following proportion to each other:

    • If you fill the parameter alias , you do not need to enter anything for sheet and cell .

    • If you do not fill the parameter alias , you have to transfer values for sheet and cell . You can omit the value for sheet if the file only contains one worksheet.

  • file

    Name of the Excel file; character string (255 characters); optional if no other files are assigned to the object as attachments, otherwise mandatory

  • currency

    Description of a cell of the Excel file that contains a currency ID in the case of monetary amounts; character string; optional

  • value

    Amount for the currency transferred as currency ; numerical; mandatory

Example

You want to include values from an Excel file in a formula in accordance with the scenario outlined above and save the file in an external file server. To make the machine hour rate you are looking for visible in the working environment, you have included a key figure that is reserved for this hourly rate in the field list of the working environment. You can calculate the column for this in the working environment with the following formula:

IF (READ_EXCEL_SHEET_LOCAL('\\fileServer\machine\mcosts.xls' , 'hourly rate', ....'C32', '') <> 0, ....READ_EXCEL_SHEET_LOCAL('\\fileServer\machine\mcosts.xls' , 'hourly rate', ....'C32', ''), ....-1)

Here you instruct the system to open the "mcosts.xls" Excel file in the "machine" directory of the "fileServer" file server and to read the value contained in cell "C32" of the "hourly rate" worksheet. If the function returns the value 0, the formula assumes that an error has occurred. To make this clear the formula returns the value –1 in this case.

You then transfer the value determined by the formula for the hourly rate column into another column to let it flow into other calculations.