No GET XLS CONSTANTS in version 5.10.a

[ppmvanhooff]

When i upgrade from 5.10.x to 5.10.a, Vensim won't read in values from an xls-file (using GET XLS LOOKUPS). The message is that there might not be a 'live connection'. When I return to a previous version all works well again.
Is this a bug, or am I missing something that has been added or changed?

[bob@vensim.com]

There is a known problem if you are using a named range that begins with 2 letters followed by a nonletter. What is the call that is causing the problem for you?

[ppmvanhooff]

GET XLS CONSTANTS('srdata.xls', 'dieselstad', 'pr_ltr_d')

So the problem is the underscore after <pr>.

Will this issue be solved in the near future?

[Steve Taff]

I've got the same problem:
GET XLS DATA('Biofuel All States.xlsx', 'Corn_Soybean Ratio', '8', 'C9')

So it's more than just "2 letters followed by a nonletter" in a named range. I get rid of the underscore in the command and in the tab itself: still won't read in 5.10a.

[bob@vensim.com]

The first example demonstrates the problem and yes it will be fixed in 5.10b which will probably be available later this month.

The second of these works when I copy and paste the equation (and create a spreadsheet with time in row 8 and some numbers in row 9 and the names used) - what error messages are you getting?

[ppmvanhooff]

Thanks Bob!

[Steve Taff]

Hi Bob,

Here's what I get in 5.10a for GET XLS DATA('Biofuel All States.xlsx', 'Corn_Soybean Ratio', '8', 'C9'):

ERROR: Insufficient data for needed variable corn soybean ratio[AL,corn] in file Biofuel All States.xlsx sheet Corn_Soybean Ratio.
ERROR: Unable to get data values for corn soybean ratio[AL,corn].
ERROR: Failed to make live links to data - aborting simulation.
ERROR: Unable to correctly load in exogenous values - aborting.

Same GET XLS command works in 5.10.

Maybe the problem is that this is a variable with several "levels" (forgetting the right word)? Here's the full version:

corn soybean ratio[state,corn]:=
GET XLS DATA('Biofuel All States.xlsx', 'Corn_Soybean Ratio', '8', 'C9') ~~|
corn soybean ratio[state,soybeans]:=
GET XLS DATA('Biofuel All States.xlsx', 'Corn_Soybean Ratio', '8', 'C59') ~~|
corn soybean ratio[state,stover]:=
GET XLS DATA('Biofuel All States.xlsx', 'Corn_Soybean Ratio', '8', 'C9') ~~|
corn soybean ratio[state,switchgrass]=
1 ~~|
corn soybean ratio[state,wheatstraw]=
1 ~~|
corn soybean ratio[state,woodchips]=
1 ~~|
corn soybean ratio[state,other]=
1
~ Dmnl
~ |

Thanks for your help.

[bob@vensim.com]

Hi Steve,

I has to have something to do with the way the data are laid out in the Spreadsheet. I notice that corn and stover have the same equation, though I have to admit I don't know what stover is.

- actually I was able to replicate this after another try so no need to send the model.

[Edited on 2010-9-12 by bob@vensim.com]

[Steve Taff]

Thanks, Bob. Glad I wasn't hallucinating--although this means more work for you to find and fix the problem.

BTW, "stover" is jargon for corn stalks, formerly used (if at all) just for livestock bedding but now eyed as a biomass feedstock.

[ppmvanhooff]

Hi,

The problem didn't go away in 5.10b.
I still cannot read data from an Excelsheet using named ranges when there is an underscore in the name.
In Version 5.10x it works ok.

Kind Regards,

Paul

[bob@vensim.com]

What is the name of the range causing problems?

[ppmvanhooff]

GET XLS LOOKUPS(sheet, 'dieselstad', 'tijd1', 'g_a_d_b_t')

This worked well in 5.9 and in 5.10x3, but in 5.10a and b it is not working anymore.

sheet is a string: the name of of the xls file.

[ppmvanhooff]

Hello,

When I use the Mac-version, I get an error that says that the named range is incorrect or non-contiguous. In the error message the named range is printed with capitals (PR_LTR_D), while the named range in the spreadsheet is pr_ltr_d. Is this maybe the cause of the error?


I also noticed that views with names that begin with a hyphen ("-") are not shown on a Mac.

Paul
Paul

[bob@vensim.com]

Thanks - I will take a look at this. I suspect it is the first entry 'tijd1' that is causing problems as it has the format of regular cell references (though column beyond XFD are not supported in Excel).

Named ranges are not currently supported on the Mac.

[ppmvanhooff]

Hi,

Maybe, but tijd1 is a range with the same 'length' as the other range with the underscores.

Paul

[ppmvanhooff]

Hi Bob,

I have been looking for a solution, but found none.
First of all I removed all the underscores in Vensim and renamed the named ranges in xls.
When I run my model in Vensim 5.10b the error message is: "If startpos is a named range the value for time row/col must also be a named range".
I have no idea what this message means.
In version 5.10x (or earlier versions) it all works just fine, with or without underscores in names of named ranges.

I have no idea what is going on.

Kind regards,

Paul

[bob@vensim.com]

The issue is the one that I stated in my previous message. tijd1 has the same form as XAV1 and is being interpreted to mean that and not to refer to a named range. We are changing this so that for .xls file the resultant column must be between 1 and 256 and for .xlsx files it must be between 1 and 16384 of the name will be treated as a range.

If you rename, or add a synonym, for tijd1 as something such as tijd_1 it should work fine in 5.10b.

[ppmvanhooff]

solved!

Thanks a lot!

[Steve Taff]

Now working for my problem (above), too. Thanks, Bob.