GET XLS SUBSCRIPT : vdf conversion ?
GET XLS SUBSCRIPT : vdf conversion ?
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
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
-
- Super Administrator
- Posts: 4573
- Joined: Wed Mar 05, 2003 3:10 am
Re: GET XLS SUBSCRIPT : vdf conversion ?
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.
I'm not sure what you are trying to do with the lookup, if you explain, I'm sure we can help.
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
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Re: GET XLS SUBSCRIPT : vdf conversion ?
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
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
-
- Super Administrator
- Posts: 4573
- Joined: Wed Mar 05, 2003 3:10 am
Re: GET XLS SUBSCRIPT : vdf conversion ?
Try this. It reads the values in the spreadsheet using GET XLS CONSTANTS, and then looks up the rental value based on this.
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
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Re: GET XLS SUBSCRIPT : vdf conversion ?
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
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
-
- Super Administrator
- Posts: 4573
- Joined: Wed Mar 05, 2003 3:10 am
Re: GET XLS SUBSCRIPT : vdf conversion ?
No, but if you upload the model and Excel file, I'm sure I can tell you what you've done wrong.Do you know why ?
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
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Re: GET XLS SUBSCRIPT : vdf conversion ?
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
-
- Super Administrator
- Posts: 4573
- Joined: Wed Mar 05, 2003 3:10 am
Re: GET XLS SUBSCRIPT : vdf conversion ?
This model works for me, and reads in the values from column E in the spreadsheet correctly.
What happens for you?
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
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Re: GET XLS SUBSCRIPT : vdf conversion ?
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"
-
- Super Administrator
- Posts: 4573
- Joined: Wed Mar 05, 2003 3:10 am
Re: GET XLS SUBSCRIPT : vdf conversion ?
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.
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
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Units are important!
http://www.bbc.co.uk/news/magazine-27509559
Re: GET XLS SUBSCRIPT : vdf conversion ?
Also, the problem could be Excel or your OS. Shutting down all Excel instances or rebooting might help.
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Re: GET XLS SUBSCRIPT : vdf conversion ?
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
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
Re: GET XLS SUBSCRIPT : vdf conversion ?
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.
If you use GET DIRECT instead of GET XLS, you don't have to have Excel in the loop.
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Re: GET XLS SUBSCRIPT : vdf conversion ?
Hi,
Thank you a lot for your help, it's working !!
Laura
Thank you a lot for your help, it's working !!
Laura