Sample Scripts

EXCEL_CALCULATE (VB stack function)

This sample illustrates how to use objects available on your system. In particular how you can use existing excel worksheets and integrate them in your exchange logic.

Suppose you have a worksheet that calculates prices of articles for you based on the ordered quantity. Like our wine sample below.

Basically in this sheet you have your articles listed in the D-column, and in the E to H column you specify the price depending on the quantity range. By entering an article in cell A2, and a quantity in B2 the sheet gives you the price in cell A4. This is because cell A4 has the instruction VLOOKUP(\$A\$2;D2:H10;\$B\$2/5+2;FALSE).

Integrating this in your PRODBX script means you need to provide 2 values to the sheet. One will go directly as InputValue, the other through the Stack (value written to the stack by a previously interpreted field). Once this is done the script will return the result of the sheet.

When calling the function we pass 3 parameters:

• Parameter 1: path and filename of the excel sheet
• Parameter 2: cell InputValue / cell Stack / cell result  (in our example A2/B2/A4)
• Parameter 3: the sheet number

Function ExcelCalculate(InputValue,Stack,Parameter1,Parameter2,Parameter3,Parameter4)

'Get the object
Set xlbook = GetObject(Parameter1)
'Go to the sheet specified
Set xlsheet = xlbook.Worksheets(Parameter3)

'Split Parameter 2 in its cells
cellInput = Mid(Parameter2, 1, InStr(1, Parameter2, "/") - 1)
cellStack = Mid(Parameter2, Instr(1, Parameter2, "/") + 1, _
Instr(Instr(1, Parameter2, "/") + 1, Parameter2, "/") - Instr(1, Parameter2, "/") - 1)
cellResult = Mid(Parameter2,len(cellInput & cellStack) + 3)

'Define the cells to work with
Set Inputxlrange = xlsheet.Range(cellInput)
Set Stackxlrange = xlsheet.Range(cellStack)
Set Resultxlrange = xlsheet.Range(cellResult)

'Enter the values
Inputxlrange.value = InputValue
Stackxlrange.value = Stack

'Get the result
ExcelCalculate = Resultxlrange.value

End Function

When testing the function in the configuration manager, our example could look like:

Results in: