Ventana software support forum

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: Here is the model: 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

Can you upload anything so we can test?

Yes, all the files are as attachements. (Commant script in a zip-file, because could not upload it otherwise)

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.

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.

The .cin file trick also fails.

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

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.

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.

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.

Back to this one again.

So, there is roughly 30 parameters and 10 different scenarios that I would like to control from the excel sheet.

I hoped that I could define all the parameters and scenarios in the excel and then just control from the command script the scenario runs. This would have been a very convenient way to control the simulations and a way to handle all the important parameters in one excel sheet (in this way it is easy to play with different scenarios). Also, this would have made it easy to define different sensitivity runs for different scenarios using the command script.

Is there any other way I could handle the simulations from the excel using command scripts? Or some other similar way? And I would rather not use visual basic. I'm not sure what you mean by doing this with data variables.

See the excel file as an attancement.

Here is something that works, but it's a little "clunky".

You read in all the values from the "All" sheet, and then use another constant to switch between them.

Units will be a problem using this method as all constants read in at the same time can only be given the same units.

Thanks a lot, I can work with this!

I don't mind about the units. For me the drawback of this solution is that I can't use the synthesim mode with the parameters that are defined in excel.

Btw, is there some kind of best practice way for managing multiple simulation runs?