to read a dynamic range from an excel file with .xlsm extension

[betsy.o...@pucp.pe]

Hello,

Can not access by ODBC an excel file with .xlsm extension ?? or maybe I'm doing something wrong?

vrp.run

----------

model vrp.mod;

table vhtype IN "ODBC" "AutoProgramaDeVueloV4.xlsm":

vehicle <- [Vehicle], type;

read table vhtype;

-----------------------------------------------------------------------------------------------------------

ampl: include vrp.run;

Error at _cmdno 3 executing "read_table" command

(file vrp.run, line 4, offset 100):

Error reading table vhtype with table handler odbc:

"AutoProgramaDeVueloV4.xlsm" does not end in any of

.accdb

.mdb

.xls

.xlsb

.xlsx

ampl:

The other problem is that when passing the data to an .xlsx file I can not read a dynamic range because I get the following message:

ampl: include vrp.run;

Error at _cmdno 3 executing "read_table" command

(file vrp.run, line 4, offset 82):

Error reading table vhtype with table handler odbc:

Table `vhtype` does not appear in "vrp.xlsx".

ampl: 

the formula I am using for the dynamic range is not very extensive: = DESF (Sheet1! $ A $ 1; 0; 0; 16; 2)

Regards,

Betsy Orellana

[betsy.o...@pucp.pe]

[Robert Fourer]

We have found that with some older Windows ODBC drivers, it is necessary to specify the driver explicitly in the AMPL "table" statement.  To find the name of your Excel ODBC driver, go to

   Control Panel / Administrative Tools / ODBC Data Sources (64-bit)

(if you are running 64-bit Excel; there are also 32-bit versions).  Then find the name of the driver in the small window that appears:



Specify the driver and filename explicitly like this:

   table vhtype IN "ODBC"
      "Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=AutoProgramaDeVueloV4.xlsm":

         vehicle <- [Vehicle], type;

However the driver name on your system may be different from the name shown above, and you will have to adjust the command accordingly.  (It might be necessary to do this even though .xlsm is mentioned in the driver name.)  If you continue to have trouble, a workaround is to save the file in .xlsb format instead.

We have observed that Excel doesn't expose dynamic ranges via ODBC.  However a suggestion for an alternative is described in this comment: https://groups.google.com/d/msg/ampl/QS30gef2Hnk/lm90jq2KRdkJ.

Bob Fourer
am...@googlegroups.com

[betsy.o...@pucp.pe]

Hi Bob,

Thanks for the answer, I followed your recommendation and as for the ranges for the moment I have left aside the formula of DESREF and I have set them manually, however I get this message:

Error at _cmdno 4 executing "read_table" command

(file vrp.run, line 25, offset 1207):

Error reading table Commodity with table handler odbc:

Table `Commodity` does not appear in "vrp2.xlsm".

#execute 0.125 173064 545544

Which is a message similar to the one that appeared when using dynamic ranges

Regards

Betsy Orellana

[Robert Fourer]

This error message says that your AMPL "table" statement has specified 'Commodity' as the name of the spreadsheet range containing your data -- but there is no range with that name in the spreadsheet vrp2.xlsm. It appears that you statement began

table Commodity ...

and did not specify any range name; in this case, the range name is assumed to be the same as the table name.

Perhaps your table statement needs to specify a different range name; this is done in a string that follows the specification of the Excel filename. Or it may be that your spreadsheet is using the wrong name for the range containing your data.

Bob Fourer
am...@googlegroups.com

=======