Problem with Command Script using GET XLS CONSTANTS

Use this forum to post Vensim related questions.
Post Reply
Tomi
Junior Member
Posts: 4
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Problem with Command Script using GET XLS CONSTANTS

Post by Tomi » Tue Oct 08, 2019 11:46 am

Hello,

I'm trying to run simulations using command scripts and getting the constants from excel. I found this old post while seeking solutions to my problem. I'm having similar problems, so I thought I'll continue from here.

Here is my command script:

Code: Select all

SIMULATE>RUNNAME|S01.vdf
SIMULATE>SETVAL|ExcelSheet :is: 'Sheet1'
MENU>RUN

SIMULATE>RUNNAME|S02.vdf
SIMULATE>SETVAL|ExcelSheet :is: 'Sheet2'
MENU>RUN
Here is the model:
model.JPG
model.JPG (10.31 KiB) Viewed 143 times
constant.JPG
constant.JPG (104.86 KiB) Viewed 143 times
excelsheet.JPG
excelsheet.JPG (99.66 KiB) Viewed 143 times
The excel file "Test.xls" has 2 sheets, "Sheet1" and "Sheet2". They both have values only on cell A1. I'm getting the correct values from excel if I change the string 'Sheet1' to 'Sheet2' manually in the model.

The command script runs change the ExcelSheet variable to 'Sheet1' and 'Sheet2' as it should. But the variable constant stays the same, that is the value from the excel Sheet1. So it seems that even though the ExcelSheet is changing, the GET XLS CONSTANTS function does not notice this change. Is there any solution to this? This way would be very convenient for me to handle simulation parameters as I could handle the parameters from excel and run the simulations using the cmd file.

And I'm using at the moment Vensim DSS 6.2.

Appreciate all the help,
Tomi

Administrator
Super Administrator
Posts: 3361
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator » Tue Oct 08, 2019 12:04 pm

Can you upload anything so we can test?
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

Tomi
Junior Member
Posts: 4
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi » Wed Oct 09, 2019 6:22 am

Yes, all the files are as attachements. (Commant script in a zip-file, because could not upload it otherwise)
Attachments
CMDxlsTest2.zip
(274 Bytes) Downloaded 10 times
Test.xls
(25.5 KiB) Downloaded 10 times
TestXlsConstants.mdl
(1.3 KiB) Downloaded 8 times

Administrator
Super Administrator
Posts: 3361
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator » Wed Oct 09, 2019 6:42 am

Thanks. I'm seeing the same issue.

I do recall that the Excel values are imported first (so possibly before any values are set such as the sheet name). I'll look into this and see if I can fix it.
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: 2759
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid » Wed Oct 09, 2019 1:28 pm

Note the provision in the docs for SETVAL:
If you are using the SETVAL command to set a string variable do not include the surrounding single quotes. For example:

SIMULATE>SETVAL|productname:IS:Metronone
However, that alone is not sufficient to resolve the problem - it still seems to fail silently.

tomfid
Administrator
Posts: 2759
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid » Wed Oct 09, 2019 1:32 pm

The .cin file trick also fails.

Tomi
Junior Member
Posts: 4
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi » Thu Oct 10, 2019 4:42 am

Thanks for testing the model. So, it seems like there is no easy fix to that problem.

Administrator
Super Administrator
Posts: 3361
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator » Thu Oct 10, 2019 6:53 am

There is no easy fix I'm sorry to say.

The external data/constants (any GET_XLS.. functions) are executed first, so any changes from CIN files/SETVAL do not get applied to the GET... functions. I'm working on it though.
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

Tomi
Junior Member
Posts: 4
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi » Wed Oct 16, 2019 4:35 pm

Alright, then I have to find another solution for controlling my simulations. Thank you for your help!

And if you get it fixed somehow let me know.

tomfid
Administrator
Posts: 2759
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid » Wed Oct 16, 2019 6:19 pm

How many datasets do you have? You could create a data variable for each, then switch the data used numerically, rather than switching the source name. It's a little inefficient, but it won't matter unless the dataset is large.

Post Reply