Originally posted by: qtbgo

Hi, I want to read data from Excel with dynamic range. My code is as follows.

1 in the .mod file:

int q = ... ;

string r_rangeInExcel;
execute {

r_rangeInExcel = "P13:P" + q.tostring() ;

}

int rhttp://1..q = ... ;

2 In the .dat file:
SheetConnection sheet("qtb.xls");
q from SheetRead(sheet,"my!h9");
r from SheetRead(sheet,r_rangeInExcel);

But an error says Excel range is unknown.
Any hints?
#CPOptimizer
#DecisionOptimization

Originally posted by: SystemAdmin

We are looking at this. Not sure there is a way to do this for reading. For writing it is OK (just search the forum about writing in excel).
#CPOptimizer
#DecisionOptimization

Hi,

what I would do is run two OPL models one after the other, the first one would compute the range string and write it in a .dat file, the second one would use it to do the excel read.
The call to those two models could be in a main block so everything could be done in a single OPL model.

Regards
#CPOptimizer
#DecisionOptimization

Originally posted by: qtbgo

Thank you very much. I'm a beginner in Cplex. Could you give an example code? Thanks again.
#CPOptimizer
#DecisionOptimization

Originally posted by: SystemAdmin

you could have a script like the following one:

main 
{ var src1 = 

new IloOplModelSource(
"ReadRange.mod"); var def1 = 

new IloOplModelDefinition(src1); var data1 = 

new IloOplDataSource(
"ReadRange.dat"); var cplex1 = 

new IloCplex(); var model1 = 

new IloOplModel(def1,cplex1); model1.addDataSource(data1); model1.generate(); var src2 = 

new IloOplModelSource(
"ReadData.mod"); var def2 = 

new IloOplModelDefinition(src2); var cplex2 = 

new IloCplex(); var model2 = 

new IloOplModel(def2,cplex2); var data2 = 

new IloOplDataElements(); data2.q = model1.q; data2.r_rangeInExcel=model1.r_rangeInExcel; model2.addDataSource(data2); var data = 

new IloOplDataSource(
"SheetRead_DynamicRange.dat"); model2.addDataSource(data); model2.generate(); 
}

I am attaching a small sample.
#CPOptimizer
#DecisionOptimization

Originally posted by: qtbgo

Dear vblanchard, I'm sorr to come back so late. Thank you very much for your help.

I add a row to your code to read r_rangeInExcel(see attachment), but it still doesn't work, do you know why?
#CPOptimizer
#DecisionOptimization