We will now make use of the Wireless Widgets transportation model introduced in Getting Started with LINGO to illustrate in more detail the use of the @OLE function. The model is reproduced below with changes listed in bold type.

! A 6 Warehouse 8 Vendor Transportation Problem;

SETS:

! Import warehouses and vendors from Excel;

  WAREHOUSES: CAPACITY;

  VENDORS   : DEMAND;

  LINKS( WAREHOUSES, VENDORS): COST, VOLUME;

ENDSETS

! The objective;

  MIN = @SUM( LINKS( I, J):

   COST( I, J) * VOLUME( I, J));

! The demand constraints;

  @FOR( VENDORS( J):

   @SUM( WAREHOUSES( I):

    VOLUME( I, J)) = DEMAND( J));

! The capacity constraints;

  @FOR( WAREHOUSES( I):

   @SUM( VENDORS( J): VOLUME( I, J))

    <= CAPACITY( I));

DATA:

! Import the data from Excel;

  WAREHOUSES, VENDORS, CAPACITY, DEMAND, COST =

   @OLE( '\LINGO\SAMPLES\WIDGETS.XLS',

    'WAREHOUSES', 'VENDORS', 'CAPACITY',

     'DEMAND', 'COST');

ENDDATA

Model: WIDGETS3

Instead of explicitly listing the data in the text of the model, we are now importing it entirely from the WIDGETS.XLS spreadsheet. Below is an illustration of the WIDGETS.XLS:

In addition to inputting the data into this sheet, we also had to define range names for the cost, capacity, demand, vendor name, and warehouse name regions. Specifically, we defined the following range names:

To define a range name in Excel:

1.        select the range by dragging over it with the mouse with the left button down,

2.        release the mouse button,

3.        select the Insert|Name|Define command,

4.        enter the desired name, and

5.        click the OK button.

We use the following instance of the @OLE function in the data section of our model to import the data from Excel:

WAREHOUSES, VENDORS, CAPACITY, DEMAND, COST =

@OLE( 'C:\LINGO\SAMPLES\WIDGETS.XLS',

 'WAREHOUSES', 'VENDORS', 'CAPACITY',

  'DEMAND', 'COST');

Note that because the model objects are all either primitive sets or set attributes, and they have the same names as their corresponding spreadsheet ranges, we could have dropped the range name arguments and used the equivalent, but shorter, version:

WAREHOUSES, VENDORS, CAPACITY, DEMAND, COST =

@OLE( 'C:\LINGO\SAMPLES\WIDGETS.XLS');

As an aside, note that we used a single @OLE function call to read all the data for this model. This is not a requirement, however. For clarity, you may choose to use multiple @OLE function calls—perhaps one for each model object.

When we solve this model, LINGO will load Excel (assuming it isn't already running), load the WIDGETS worksheet, and then pull the values for the set attributes CAPACITY, COST, and DEMAND from the worksheet, along with the members of the WAREHOUSES and VENDORS sets. Excerpts from the solution appear below:

Global optimal solution found.

Objective value:                              664.0000

Total solver iterations:                            15

 

       Variable      Value   Reduced Cost

VOLUME( WH1, V2)   19.00000      0.0000000

VOLUME( WH1, V5)   41.00000      0.0000000

VOLUME( WH2, V4)   32.00000      0.0000000

VOLUME( WH2, V8)   1.000000      0.0000000

VOLUME( WH3, V2)   12.00000      0.0000000

VOLUME( WH3, V3)   22.00000      0.0000000

VOLUME( WH3, V7)   17.00000      0.0000000

VOLUME( WH4, V6)   6.000000      0.0000000

VOLUME( WH4, V8)   37.00000      0.0000000

VOLUME( WH5, V1)   35.00000      0.0000000

VOLUME( WH5, V2)   6.000000      0.0000000

VOLUME( WH6, V6)   26.00000      0.0000000

VOLUME( WH6, V7)   26.00000      0.0000000