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: