In a previous section, @OLE Importing in a Transportation Model, we used the Wireless Widgets transportation model to demonstrate the use of the @OLE function for importing data from Excel. At the time, we did not use @OLE to export the solution back to the spreadsheet file. We will now extend the model in order to have it export the solution back to the spreadsheet. The model is reproduced below with changes in the data section 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');

! Export the solution back to Excel;

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

   'VOLUME') = VOLUME;

ENDDATA

Model: WIDGETS5

We now use the @OLE function to send the decision variables contained in the VOLUME attribute back to the Excel file WIDGETS.XLS with the statement:

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

'VOLUME') = VOLUME;

Note, since the attribute name is identical to the range name, we could have omitted the range name in the @OLE function and simply used the following:

@OLE( '\LINGO\SAMPLES\WIDGETS.XLS') = VOLUME;

We will now need to add a range titled VOLUME for receiving the solution values in the WIDGETS spreadsheet. Here is the range as it appears after adding it to the sheet:

We have also used the Insert|Name|Define command in Excel to assign the range name VOLUME to the receiving range of C16:J21. 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 (VOLUME in this case), and

5.        click the OK button.

When we solve this model, LINGO will load Excel (assuming it isn't already running), load the WIDGETS worksheet, and then pull the data for WAREHOUSES, VENDORS, CAPACITY, COST, and DEMAND from the worksheet. Once the solver has found the optimal solution, LINGO will send the values for the VOLUME attribute back to the worksheet storing them in the range of the same name and the updated range will appear as follows:

Now, suppose we had a transportation model with many cities.  Such a model could have thousands of shipping routes.  Furthermore, only a relatively small handful of the routes will tend to have nonzero shipping volumes.  Given this, we might want to export just the nonzero shipping volumes.  Here's how one might use the @WRITEFOR function to modify our example to do just that:

DATA:

! Import the data from Excel;

  WAREHOUSES, VENDORS, CAPACITY, DEMAND, COST =

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

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

     'DEMAND', 'COST');

! Export the solution back to Excel;

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

   'SHIPMENTS') = @WRITEFOR( LINKS( I, J) |

    VOLUME( I, J) #GT# 0: WAREHOUSES( I),

     VENDORS( J), VOLUME( I, J));

ENDDATA

If you examine the Sheet2 tab of the WIDGETS workbook, you'll find a three-column range called Shipments.  If you make the above modification to the WIDGETS workbook and rerun it, you'll find that the first column of the range receives the shipping warehouse, the second column receives the name of the target vendor, and the third column receives the nonzero shipping volume.  We would also recommend that you run the Solver|Options command, select the Interface tab, and enable the Fill Out Ranges and Tables option before running this example.  Enabling this option causes LINGO to blank out any additional, unused cells in the target range, thereby clearing out any left over solution from a run of a previous model with a larger set of routes with nonzero volume.  Doing this, you will find that the Shipment range resembles the following: