Hi,
I'm trying to publish an application with an Excel interface using Vensim 5.8b. I can get everything working using the Model reader to start the .vpa file, thus launching the .xls interface file in Excel and allowing VBA macros to call the DLL, with one annoying problem.
The problem is that when calling MENU>RUN from VBA code, Vensim pops up a dialog asking "Do you want to overwrite the file D:\model.xls with the package file". Since the xls file is already open in Excel, answering Yes gives the error "Unable to overwrite D:\model.xls.", and answering No starts up the simulation alright, with no further errors.
How can I get rid of the dialog window? Vensim shouldn't have any need to overwrite the .xls file since the Model reader has already unpacked the .xls file from the .vpa by this stage.
Any help would be appreciated.
Problem with Excel interface and Model reader
-
bob@vensim.com
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
Thanks for pointing this out - it is a problem we can fix for the next release.
There is not, as far as I can see, an easy workaround. The Vensim Model Reader unpacks the file then passes control over to Excel which calls the DLL and the DLL does not know the file has been unpacked. That is the source of the problem. If you put the code calling Vensim in a separate spreadsheet this problem will go away, but you will have two excel files open which might confuse the user.
If you can figure out a way for Excel to rename the file it has opened on the fly you can change the excel file name before you package it and then have it open and rename itself to the file name expected by the model (presumably a Save>As followed by a Delete of the old name). Since that name is different from the one in the package Vensim will only look to disk to find the file.
I hope that is helpful.
There is not, as far as I can see, an easy workaround. The Vensim Model Reader unpacks the file then passes control over to Excel which calls the DLL and the DLL does not know the file has been unpacked. That is the source of the problem. If you put the code calling Vensim in a separate spreadsheet this problem will go away, but you will have two excel files open which might confuse the user.
If you can figure out a way for Excel to rename the file it has opened on the fly you can change the excel file name before you package it and then have it open and rename itself to the file name expected by the model (presumably a Save>As followed by a Delete of the old name). Since that name is different from the one in the package Vensim will only look to disk to find the file.
I hope that is helpful.
Thanks. For some reason I imagined that you could only call the DLL from the .xls file named in the Vensim publish command.
What I did to solve the issue was to create an empty Excel workbook called "startup.xls", and set up the following procedure for the workbook Open event (following http://office.microsoft.com/en-us/excel ... 81033.aspx ):
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Path + "\model.xls", 0
Workbooks("startup.xls").Close
End Sub
[Edited on 2009-5-20 by Hardy]
What I did to solve the issue was to create an empty Excel workbook called "startup.xls", and set up the following procedure for the workbook Open event (following http://office.microsoft.com/en-us/excel ... 81033.aspx ):
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Path + "\model.xls", 0
Workbooks("startup.xls").Close
End Sub
[Edited on 2009-5-20 by Hardy]