Data exceeds range

Archive of Gamsworld Google Group
Post Reply
Archiver
User
User
Posts: 7876
Joined: 2 years ago

Data exceeds range

Post by Archiver » 7 years ago


Hello everyone,

I tried to write data from a .gdx file to a .xlsm file (xlsx with vba
macro code) and it failed and gave me a 'data exceeds range for x'
error instead. The data has about about ~ 100k rows, which would be
too large for an old .xls file but should be ok for a 'new' xlsx and
xlsm file. Also, it works when exporting the data to a .xlsx file, but
not to a .xlsm file. Is there any workaround or solution to that ?
thanks,
Matthes



Archiver
User
User
Posts: 7876
Joined: 2 years ago

Re: Data exceeds range

Post by Archiver » 7 years ago


Matthes,

I believe this problem was fixed in our latest build of version 23.6.5. You can download that version from the gams download page.

--Paul

On Wed, May 4, 2011 at 12:43 PM, Mat wrote:

Hello everyone,

I tried to write data from a .gdx file to a .xlsm file (xlsx with vba
macro code) and it failed and gave me a 'data exceeds range for x'
error instead. The data has about about ~ 100k rows, which would be
too large for an old .xls file but should be ok for a 'new' xlsx and
xlsm file. Also, it works when exporting the data to a .xlsx file, but
not to a .xlsm file. Is there any workaround or solution to that ?
thanks,
Matthes

--
To post to this group, send email to gamsworld@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.




--
Paul van der Eijk
GAMS Development Corporation
Tel : (202) 342-0180 Fax : (202) 342-0181
Email: pvandereijk@gams.com
Web : http://www.gams.com

--
To post to this group, send email to gamsworld@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.

arianna.sorrentino
User
User
Posts: 3
Joined: 5 months ago

Re: Data exceeds range

Post by arianna.sorrentino » 5 months ago

Hi guys,
I'm struggling with the same error message "Data exceeds range" when I try to export a variable from a gdx to excel. I have the last gams version and the variable I'm trying to export contains more or less 20k values.
Any idea on how to fix the problem?
Many thanks

Fred
Posts: 74
Joined: 2 years ago

Re: Data exceeds range

Post by Fred » 5 months ago

Hi,

Can you share some more details? How do you try to export the data (what is the exact GDXXRW call) and what is the full error message?
In order to let others reproduce the issue, it may also be helpful if you could share the gdx file.

Best,
Fred

arianna.sorrentino
User
User
Posts: 3
Joined: 5 months ago

Re: Data exceeds range

Post by arianna.sorrentino » 5 months ago

Sure.
My code is:
execute 'gdxxrw.exe results_opt.gdx o=results_opt.xlsx var=Q_bb rng=a1'

Q_bb is a time dependent variable that, in this case, has 17,518 values according to the generated gdx file.
I tried the same code for a lower number of values (e.g. 11 ) and it works but I havo no idea why now I have an error message.

Error message is:
*** Data exceeds range for Q_bb
Any changes made to the spreadsheet were not saved

Fred
Posts: 74
Joined: 2 years ago

Re: Data exceeds range

Post by Fred » 5 months ago

By default, for a one dimensional variable, GDXXRW will set cdim=1 and rdim=0, which means that you would get 17518 columns which is beyond Excels limit of at most 16,384 columns.
You can change the format of how the variable is exported to Excel by setting redim and cdim manually. Try for example

Code: Select all

gdxxrw.exe results_opt.gdx o=results_opt.xlsx var=Q_bb rng=a1 rdim=1 cdim=0
yYou can read more about this in the docuemntation of GDXXRW: https://www.gams.com/latest/docs/T_GDXXRW.html
I hope this helps!

Fred

arianna.sorrentino
User
User
Posts: 3
Joined: 5 months ago

Re: Data exceeds range

Post by arianna.sorrentino » 5 months ago

It works!!
Many thanks for the help I spent the whole day on that code line.

best,
Arianna

Post Reply