GET XLS SUBSCRIPT : vdf conversion ?

Use this forum to post Vensim related questions.
Post Reply
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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
Attachments
GET XLS SUBS - Location trial.mdl
(2.51 KiB) Downloaded 210 times
Excel essai import.xlsx
(12.77 KiB) Downloaded 213 times
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by Administrator »

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.
Excel essai import.xlsx
(12.25 KiB) Downloaded 213 times
GET XLS SUBS - Location trial.mdl
(2.49 KiB) Downloaded 221 times
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by Administrator »

Try this. It reads the values in the spreadsheet using GET XLS CONSTANTS, and then looks up the rental value based on this.
GET XLS SUBS - Location trial.mdl
(2.53 KiB) Downloaded 277 times
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by Administrator »

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.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

Here we go !
Attachments
Tableur données.xls
(222.5 KiB) Downloaded 200 times
Location.xlsx
(12.25 KiB) Downloaded 202 times
GET XLS SUBS - Location trial 3.mdl
(3.08 KiB) Downloaded 197 times
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by Administrator »

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

What happens for you?
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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"
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by Administrator »

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.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by tomfid »

Also, the problem could be Excel or your OS. Shutting down all Excel instances or rebooting might help.
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by tomfid »

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.
oOLauraOo
Member
Posts: 41
Joined: Wed Mar 22, 2017 5:05 pm
Vensim version: PRO

Re: GET XLS SUBSCRIPT : vdf conversion ?

Post by oOLauraOo »

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

Laura
Post Reply