running Vensim from Excel

[chriswatson]

Hi,

I have a model in Vensim that reads data from Excel using the GET XLS CONSTANTS function. I have now been asked whether I could put a button in Excel which automatically runs the model, rather than going into Vensim.

I have saved the model as a vmf, and tested my code to open the vensim model using a simpler model - which worked well (using result = vensim_command("special>loadmodel|mymodelname.vmf")).

However, when I try using this code to open the model that reads from Excel it freezes on this line of code. No error - the vensim_command line of code is not completed. The only way I have found of exiting Vensim when this happens is to kill it through the task manager. I think that it may be something to do with the Vensim model trying to open or read from the input data spreadsheet that is referenced throughout the model.

the model runs correctly when opened independently, and the code works when I use it to open a model that does not reference Excel.

Any hints / tips would be greatly appreciated!

Thanks,

Chris

[chriswatson]

I should probably also have said that I am using Version 5.3.

I am now not sure whether it is the fact that Excel is being referenced. I created a simple model (rate in, rate out and a level) and read some data from Excel. When trying to open this model it did open! Maybe because I have a lot of variables in my model, and the Excel file is also large it just takes too long to open (?).

[Administrator]

I don't think you can open a model from within Excel and link back to it using the GET_XLS functions. From memory (it's been a while), DDE processes can not usually link back to themselves.

I'll create a simple demo later on today and see if I can get it to work.

Tony.

[chriswatson]

Thanks Tony,

I left the model to try and open last night, and it eventually did open, but took 50 minutes! It then tried to open the Excel input file as many times as it was referenced in the model (quite a lot in this case).

One potential solution I can think of is to replace the references in Vensim by code in VBA (so no links left in Vensim) but this will take a long time to complete, and may not actually solve the problem.

We have copies of the new version of Vensim here, so if there is any new functionality (not read any documentation yet) it may help.

Thanks,

Chris

[Administrator]

Looks like I was wrong. I've created a simple demo that works fine with Vensim 5.7a (some VBA opening a model that references the spreadsheet that opens it using GET XLS DATA).

Can you try it and see if it works on the older version of Vensim?

Tony.

[chriswatson]

Hi Tony,

I tried your model and it didn't work with version 5.3. However, I now have the latest version installed, and it worked perfectly! My model used to take some time getting data from Excel, and now runs immediately, and the VBA I was using works perfectly.

Thanks for your help!

Chris