Launching Mosel from Excel using VBA

This example demonstrates how to launch Xpress Mosel from a VBA script within Microsoft Excel and retrieve the results of running a model.

When a spreadsheet is open within Excel, Excel places a read-only lock on the file such that the ODBC driver cannot write to the spreadsheet. This means that if you execute a model within Mosel, either from the command line console or via Xpress Workbench, the spreadsheet must be closed if you wish to export data from the model to the spreadsheet via ODBC.

This example shows how to avoid this problem by retrieving the results using the VBA interface to Mosel, and then manually writing the data to the spreadsheet. ODBC is used by the model to read the input data from the spreadsheet, but is NOT used to output the results.

(!******************************************************
   Mosel Example Problems
   ====================== 

   file excelmosel1.mos
   ````````````````````
   Small MIP problem reading data from Excel.
   
   (c) 2008 Fair Isaac Corporation
       
*******************************************************!)

model "excelmosel1"
 uses  "mmxprs"

 parameters
  DATA_XLS = 'excelmosel1.xls'
 end-parameters

 declarations
  ROWS= 1..4
  COLS= 1..4
  COEFFS: array(ROWS, COLS) of real     ! Constraint coefficients
  COSTS: array(COLS) of real            ! Objective coefficients
  RHS_p,RHS_v: array(ROWS) of real      ! RHS terms
  vars: array(COLS) of mpvar            ! Decision variables
  sol: array(COLS) of real              ! Solution values
 end-declarations
 
 forall(j in COLS)
  vars(j) is_integer

 setparam("XPRS_VERBOSE", true)
 setparam("XPRS_LOADNAMES", true)

! Retrieve data from Excel
 initialisations from 'mmsheet.excel:'+ DATA_XLS
  COSTS as 'noindex;costs'
  COEFFS as 'coeffs'
  RHS_p as 'noindex;rhs_p'
  RHS_v as 'noindex;rhs_v'
 end-initialisations

! Define the optimization problem
 MaxObj := sum(j in COLS) COSTS(j)*vars(j)
 forall(i in ROWS) 
  sum(j in COLS) COEFFS(i,j) * vars(j) <= (RHS_p(i) + RHS_v(i)) * 10

! Solve the problem
 maximize(MaxObj)

 writeln("MaxObj: ", getsol(MaxObj))

! Save and display the solution 
 forall (j in COLS) do
  sol(j) := getsol(vars(j))
  writeln(sol(j))
 end-do
	
end-model