Originally posted by: MchelLeo

Hi Alexfleischer,

I'm new beginner using CPLEX, so my the model is error, please post .mod and dat.

.mod

// Sets
   {string} cities = ...; 
   {string} Routes = ...; 
   int Time = ...;
   range Time = 1..NbPeriods;
  float d[i in cities,j in cities]; 
  
 // Variables
    dvar float B[j in cities, r in Routes, t in Time];
    dvar float R[i in cities, r in Routes, t in Time];
    
   
     tuple flows {
     string Rounds;
     string Times;
     float Vehicles;  
    }
     setof (flows) flow =...;
    dvar float F[r in Routes, t in Periods] =flow{<Vehicles>};
    subject to  {
    forall (i,j in cities, r in Routes, t in Periods) 
           B[j][r][t] == B[i][r][t] + R[i][r][t] - F[r][t]*d[i][j];

    }
 

.Dat

SheetConnection my_sheet("Data.xls");
cities from SheetRead(my_sheet,"'Travel_Distance'!A2:A4");
NbPeriods from SheetRead(my_sheet,"'Vehicles_Period'!A2:A6");
Routes from SheetRead(my_sheet,"'Route_traveling Vehicles'!A1:C1");
d from SheetRead(my_sheet,"'Travel_Distance'!B2:D4"); 
F from SheetRead(my_sheet,"'Vehicles_Period'!B2:D6, "); 

Hi,

I fixed your errors and you could start from there:

.mod

int NbPeriods=5;

 // Sets
   {string} cities = ...;
   {string} Routes = ...;
   //int Time = ...;
   range Time = 1..NbPeriods;
   range Periods = 1..NbPeriods;
    
  float d[i in cities,j in cities]=...;
 
     
 // Variables
    dvar float B[j in cities, r in Routes, t in Time];
    dvar float R[i in cities, r in Routes, t in Time];
    
   
     tuple flows {
     string Rounds;
     string Times;
     float Vehicles;  
    }
     //setof (flows) flow =...;
    float F[r in Routes, t in Periods]=...; // =flow{<Vehicles>};
    
 
    subject to  {
    forall (i,j in cities, r in Routes, t in Periods)
           ct:B[j][r][t] == B[i][r][t] + R[i][r][t] - F[r][t]*d[i][j];
           
   
    }

.dat

SheetConnection my_sheet("Data.xlsx");
cities from SheetRead(my_sheet,"'Travel_Distance'!A2:A4");
//NbPeriods from SheetRead(my_sheet,"'Vehicles_Period'!A2:A6");
Routes from SheetRead(my_sheet,"'Route_traveling Vehicles'!A1:C1");
d from SheetRead(my_sheet,"'Travel_Distance'!B2:D4");
F from SheetRead(my_sheet,"'Vehicles_Period'!B2:D6");

regards

PS:

Some links at https://www.ibm.com/developerworks/community/forums/html/threadTopic?id=0d0b2396-3b48-4638-b032-3b9ea74f1a11&ps=25

Originally posted by: JSFL

I am unable get SheetConnection to work, even with the Oil example

I am running CPLEX version 12.9.0.0 (Build id: 201902191339) and "Microsoft Excel for Office 365 MSO 16.0.10730.20334) 64-bit on Windows 10

I have modified the Windows path variable to include  ... cplexhome\cplex\bin\x64_win64\ directory, to no avail. Since the aim is to read data from Excel, however, and the call is being made FROM cplex, I don't understand why the concern would be a pointer to the cplex1290.dll file. One would think that the calling application would be well enough aware of that file's location, and that the problem might instead be in accessing a component that enables opening the Excel file. (I say this without having any great technical expertise on the subject, admittedly.)

Could you offer any ideas regarding how to make SheetConnection work?

Sincere thanks!

Hi,

have you tried to do that within the IDE ?

Do you get any error message ?

regards

Originally posted by: JSFL

Alex, Thanks for your reply.

Yes, I've tried to run the Oil model in the IDE. (In fact, that's the only environment in which I've learned to work with CPLEX, so far.) The error message I receive is "Internal Error: Contact IBM"

Cheers, John

Ok then what about trying to use oplrun ?

https://www.ibm.com/support/knowledgecenter/SSSA5P_12.9.0/ilog.odms.ide.help/OPL_Studio/refoplrun/topics/oplrun_TOP.html

regards

Originally posted by: JSFL

I am able to use oplrun for other example files, but not with oil.mod, as it produced the following errors:

'Just a thought: Might there be some sort of dependency on a Visual Studio or Java component that I am missing? Again, these are technologies with which I'm not really conversant.

Continuing thanks for your consideration of this problem!

Hi,

you got that error because you did not give the .dat to oplrun.

Try

oplrun oil.mod oilsheet.dat

regards

Originally posted by: JSFL

'Regrets for my misunderstanding about needing to pass .dat file to oplrun*

So having now done so, the error I receive in the Win Cmd shell is:

Com Exception:
Code = -2,147,467,261
Code meaning = Invalid pointer
xlsFile = \\..\ExamplesWorkDir\oil\oilSheet.xls

and just to be sure if you do

oplrun oil.mod oil.dat

?

regards

Originally posted by: JSFL

The simple version "oplrun oil.mod oil.dat" works fine … It is when attempting (the 'Date from Spreadsheet' run configuration) to read from the oilSheet.xls file using the oilSheet.dat file that the previous Com Exception arises. 

Originally posted by: FredericDelhoume

I suspect you do not have an installation of Excel that allows using the COM component.

Please try the following sample :

excelcom.exe oilSheet.xls

and tell us the result.

On a machine without any Excel installation it fails when creating the COM component.

C:\Users\delho\Downloads\ExcelCOM>excelcom.exe oilSheet.xls
Error opening Excel COM: -2147221005

That is com error 800401f3 : Invalid Class String : see https://docs.microsoft.com/en-us/windows/desktop/com/com-error-codes-1

if we do not check the result and try to access the Excel object we have the same Invalid Pointer error you have :

Excel::WorkbooksPtr books = pXL->Workbooks;

gives Invalid Pointer.

It is a pure Windows code executable but it does the same than OPL code (OPL code does not check result so

we have Invalid Pointer, we should check also result to display Invalid Class String, the error is more explicit).

Originally posted by: FredericDelhoume

https://www.autohotkey.com/boards/viewtopic.php?t=4794

seems to have the same issue, their solution is to register again the COM component of Excel.