The syntax for using @OLE to import data in both the data and init sections is:

object_list = @OLE( ['spreadsheet_file'] [, range_name_list]);

The object_list is a list of model objects, optionally separated by commas, which are to be initialized from the spreadsheet. Object_list may contain any combination of set names, set attributes, and scalar variables.

The spreadsheet_file is the name of the Excel spreadsheet file to retrieve the data from.  If the name is omitted, LINGO defaults to using whatever workbook is currently open in Excel.

The range_name_list is the list of named ranges in the sheet to retrieve the data from. The ranges must contain exactly one element for each member in the object_list. There are three options available in how you specify the ranges. First, you can omit the range arguments entirely. In which case, LINGO defaults to using a list of range names identical to the list of object names in object_list. Second, you can specify a single range name to retrieve all the data from. In which case, all the objects in object_list must be defined on the same set, and LINGO reads the data as if it was a table. When specifying a single range name for multiple objects, all the objects must be of the same data type. Furthermore, you can’t mix set members (text) with set attributes (numeric). Finally, you can specify one range name for each object in object_list. In which case, the objects do not have to be defined on the same set and can be of differing data types. Examples of these three methods for using @OLE follow:

Example 1:        COST, CAPACITY = @OLE();

In this example we specify no arguments to the @OLE() function.  In which case, LINGO will supply the default arguments.  Given that no workbook name was specified, LINGO will use whatever workbook is currently open and active in Excel.  In addition, no range names were specified, so LINGO defaults to using the names of the model objects. Thus, COST and CAPACITY are initialized to the values found, respectively, in the ranges COST and CAPACITY in the currently open workbook in Excel.

Example 2:        COST, CAPACITY = @OLE( 'SPECS.XLS', 'DATATABLE');

In this example, we are specifying a single range to initialize both COST and CAPACITY. Assuming the range DATATABLE has two columns, LINGO initializes COST to the data in column 1 and CAPACITY to the data in column 2. Note, in order for this method to work, both COST and CAPACITY must be defined on the same set. Furthermore, they must both be either sets or set attributes—mixed types aren’t allowed using this form.

Example 3:        COST, CAPACITY = @OLE( 'SPECS.XLS', 'COST01', 'CAP01');

In this example, we are specifying individual ranges to initialize both COST and CAPACITY. COST will be initialized with the data in the COST01 range and CAPACITY will receive the values in the CAP01 range.

As a final note, it is important to know that derived sets may be imported from either a single range or from n ranges, where n is the dimension of the derived set.  Here are two examples to illustrate:

Example 4:        ARCS, COST = @OLE( 'TRAN.XLS', 'ARCS', 'COST');

You might find something similar to this example in a transportation model. ARCS is the set of shipping arcs and COST is an attribute for storing the cost of shipping one unit down each arc.  Typically, ARCS would be a 2-dimensional derived set of from-to coordinate pairs.  In this example, we've chosen to bring the list of arcs in from a single workbook range.  Suppose there are 10 shipping arcs in our model, then the ARCS range would have 20 cells, with the first column of 10 cells being the ship-from points and the second column of 10 cells being the ship-to points.  Since we are using the single range method to input a derived set, the two columns are adjacent and are both contained in the single range call ARCS.  This is in contrast to the following example…

Example 5:        ARCS, COST = @OLE( 'TRAN.XLS', 'FROM', 'TO', 'COST');

…where we use two ranges to store the two-dimensional ARCS set.  The first range, FROM, would contain 10 ship-from points, while the second range, TO, would contain 10 ship-to points.  These two ranges may lie in different areas of the workbook and need not be adjacent.