Ventana software support forum

Hi !

I'm in trouble using GET XLS SUBSCRIPT function... Is it well a function to give the values of a subscripted variable that I want to populate ? If yes, I'll need your help to manage this...

My first question is related to the VDF conversion box, opening when importing the dataset. I can't understand how to fill the "Time" section and the "Subs" section. As I want to populate a subscripted variable, I guess I shouldn't be related to "Time", but this option doesn't seem to exist. What sould I do here ? Then, I don't understand the documentation concerning Subs...

Then, in the equation editor, I obtain an error message when checking the equation "Unable to understand the right side of the equation", like Vensim doesn't recognize the function. Is it because of a bad VDF conversion ?

Can you please help me (again !!) ? I put in attachment the .xlsx file and a partial model containing the "location" variable I'm trying to populate with the 15 xls values.

Thanks a lot,

laura

Have a look at the attached model and Excel file. Using a random number to generate the subscripts is probably a really bad idea so I converted them to values.

I'm not sure what you are trying to do with the lookup, if you explain, I'm sure we can help.

You're right, using a random function in Excel was not one of my best ideas... oops ! Thanks for that !

Thanks to the model you sent me, I now better understand what GET XLS SUBSCRIPTS is made for. I thought it was to do something like this :

location[Building1,Building2,Building3]=x1,x2,x3 where xn are the value to pick in Excel. (Is it also possible to do that ? Perhaps with GET XLS CONSTANT ?)

In the partial model I sent you, I want to apply a different price/m² for each building depending on its location.
French departments are numbered from 1 to 95, which explains why I wanted to use random numbers between 1 and 95.
Once upon a time, when I was desperate, I modeled price/m² as a lookup depending on the location, in order to obtain a rental value which is the result of the lookup price/m²(location). Does it make sense ?

What would be the best option to do that ?

Thank you a lot for your help,

Laura

Try this. It reads the values in the spreadsheet using GET XLS CONSTANTS, and then looks up the rental value based on this.

It works very well !
I'm trying now to do the same for "price/m²" with GET XLS LOOKUP. But I keep having error messages "Failed to connect to Excel". The two useful documents are opened in Excel, I saved them in the same file and updated the path in Model>Settings, but still I can't make it work anymore !
Do you know why ?

Thanks !

Laura

Do you know why ?

No, but if you upload the model and Excel file, I'm sure I can tell you what you've done wrong.

Here we go !

This model works for me, and reads in the values from column E in the spreadsheet correctly.

What happens for you?

When I run the simulation, the correct file opens in Excel, but Vensim keeps sending the error message "Failed to connect to Excel to get data from Location.xlsx" - "Unable to get constant values"

It works fine for me. Can you upload the files you are working on?

If you have changed the Vensim settings, you can experiment with those. Tools->Options->Settings, there is an entry for GET XLS PROTOCOL, try DDE instead of OLE and see if that works.

Also try in Excel, Settings, under "General" there is a tickbox to "Ignore other applications that use Dynamic Data Exchange". Try that as well.

Also, the problem could be Excel or your OS. Shutting down all Excel instances or rebooting might help.

Hi,
It might be a problem of Excel version : with a computer using Excel 2010, the model works perfectly. With my usual computer using Excel 2016, I get error messages such as "Unable to connect to Excel". I've tried your suggestions (registering the document as .xls [97-2003] ; changing the model settings (get xls protocol) from OLE to DDE ; rebooting the system ; ticking boxes in Excel settings...) but it's not working !
Do you have any idea about what's happening ?
Thanks,

Laura

That could be a 32 vs 64 bit issue. There's supposed to be a 32-bit connector process for 64-bit Excel, but it may not be working.

If you use GET DIRECT instead of GET XLS, you don't have to have Excel in the loop.

Hi,
Thank you a lot for your help, it's working !!

Laura