Unable to use GET functions that call excel in Vensim DSS 7.0

Use this forum to post Vensim related questions.
Post Reply
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

When I try to use any of the functions that call data from Excel (e.g. GET XLS CONSTANTS), Vensim asks if I want to ask if I want to add the file name (without the file extension) to the model. It is as if Vensim expects there to be variables with causal links for all function arguments as it does for the arithmetic equations. The pop up message reads "Do you want to add 'Fleet Model Inputs' to the model?" Then the Error line in the equation editor dialogue box reads Use - GET... CONSTANTS('lit1', 'lit2'...) cannot include other expressions." In addition, Vensim replaces the period before the file extension (first argument of the function) with a zero, "0," when I reopen the equation editor dialogue box.

I have followed the documentation in the user guide for this function. I am experiencing the problem in an older model that was working before, in addition to the newer one I am building now. The one change I can think of is that I was working in the previous version of Vensim before, but I have Vensim DSS 7.0 now, which just came out in May. I get the same errors with other GET functions that call excel. Could there be a bug in the new edition? Please advise.
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

Can you upload the model? Or take some screenshots of what you are entering into the equation editor?

It sounds as if you don't have quotes around the Excel file name.
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: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by tomfid »

Did you enclose the strings in single quotes? ( ' )
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by tomfid »

Double quotes " or no quotes will be interpreted as a variable name.
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

Thank you for your replies. I have attached screenshots in Archive.zip of what I see. (1) I use single quotes, as per the documentation and confirmed by your messages. When I attempt to Close or Check Syntax, I get a (2) popup asking if I want to add Fleet Model Inputs to the to the model. I also get (3) the error shown in the box at the bottom of the equation editor screen. When I hit close again, and then reopen, I see (4) Vensim has replaced the period before the file extension with a zero.

I also tried adding the excel files under Model > Settings and using ?Inputs as the tag to call the file, but that had the same issues.
Attachments
Archive.zip
(540.71 KiB) Downloaded 183 times
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

Try using ' instead of `, does that work?
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
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

Wow. That seems to have taken care of the error. I feel silly asking, but how do you type the vertical single quote? (I copied and pasted it from the forum to try it as a solution.)

Thank you!
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

It depends on the keyboard (mine are all standard UK keyboards). On my Mac, it's the key two to the right of the "L" key.
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
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

Thanks!

Though the vertical, single quote seems to fix the problem for a moment, the quotes return to the slanted quote and changes the period before the file extension to a zero when I close and reopen the equation editor. This triggers the same issues as before. Is there a way to get vensim to recognize the vertical and slanted quotes as the same?
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

PS: I am attaching the model in case that helps.
Attachments
Fleet CoFLows Car Truck Gas Electric0.2.mdl
(10.69 KiB) Downloaded 189 times
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by tomfid »

The single quote is on the same key as the double quote on an English keyboard. Are you using a different language? What language is your operating system?
Sam001
Junior Member
Posts: 6
Joined: Tue Jul 25, 2017 2:11 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Sam001 »

Everything I have is in English. My single quote button is two to the right of the L (Mac Air keyboard).

Any ideas why Vensim would change the quote style and the pre-file extension period to zero?
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by tomfid »

I can't think of a reason; it works fine on my machine at the moment. It's possible that one of the subscript errors causes a parsing problem, but that seems like a long shot. Will investigate further.

In the meantime, I can suggest a workaround:

Instead of the filename, enter '?input' or something like that. Then, when you run, it will query you for the file name (once). Subsequently, you can manage the mapping of the ? keyword to the filename via Model>Settings>XLS Files.
Martym
Newbie
Posts: 1
Joined: Thu Aug 03, 2017 1:06 pm
Vensim version: PLE+

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Martym »

I am having the exact same problem. Is this issue resolved or still just hanging out there? I just installed 7.1 and there is no change. VENTANA CAN YOU HELP US?
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by tomfid »

We haven't managed to replicate this on our side. Can you run the models attached to the GET functions in the Help system? Have you tried the '?file' option?
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

Martym wrote:I am having the exact same problem. Is this issue resolved or still just hanging out there? I just installed 7.1 and there is no change. VENTANA CAN YOU HELP US?
Are you using a Mac 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
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

Sam001 wrote:Any ideas why Vensim would change the quote style and the pre-file extension period to zero?
There is no code within Vensim to modify keyboard input (we just get what the operating system gives us).

My first guess if something like this were happening would be to run a virus/malware scan (malware will often monitor keyboard input).
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
hcpark
Newbie
Posts: 1
Joined: Mon Sep 11, 2017 6:47 pm
Vensim version: DSS

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by hcpark »

DSS Mac Version 7.1 Double Precision

Has anyone been able to resolve this? I am also noticing that the thread is recent (July 2017). Could this be a v7.x issue?


I am having the same issues described above, specifically:

1. Excel filename is being changed after I “OK” and “close” the Edit Equation dialog box. I type in:

:= GET DIRECT DATA(‘Filename.xlsx’, ‘Patients’, ‘1’, ‘e5’ )

and it remains so as I “close”. When I reopen the variable, I get:

:= GET DIRECT DATA(‘Filename0xlsx’, ‘Patients’, ‘1’, ‘e5’ )


2. I am also trying to use the Indirect Reference.

:= GET DIRECT DATA(‘?patientInputs’, ‘Patients’, ‘1’, ‘e5’ )
returns “Incorrect/Incomplete Equation”


3. I tried using “non-slanted” quotes. They turn into slanted quote as I type or if they remain straight quotes, they have turned into slanted quotes when I reopen the Edit Equation dialog.
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

This is now happening on my Mac as well, I'll ask our Mac programmer to take an urgent look.
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
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: Unable to use GET functions that call excel in Vensim DSS 7.0

Post by Administrator »

I've uploaded a new build of Vensim to our website, if you download and install it, the problem should be fixed.
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
Post Reply