|
GET XLS SUBSCRIPT('file','tab',firstcell,'lastcell', 'prefix') GET SUBSCRIPT elements from Excel |
Scroll Prev Top Next More |
When used in a subscript equation this populates the subscript with values from Excel.
Restrictions: Must appear directly following the : and not be followed by anything other than a mapping.
GET XLS SUBSCRIPT is invoked when a model is checked. This will happen automatically when a .mdl file is open but only in response to an explicit model check (Ctrl+T or Model>Check Model) for .vmf or .vpm files. This function is not invoked as part of the simulation process.
If Microsoft Excel is not running Vensim will attempt to start it. If Microsoft Excel does not have the specified file open Vensim will try to get Microsoft Excel to open it. If Vensim cannot get values from Microsoft Excel it reports an error. If Vensim has trouble getting values with this function you should try opening the named file in Microsoft Excel first.
All of the arguments to GET XLS SUBSCRIPT must be literals (enclosed in single quotes ') or String Variables. 'file' names a file with complete extension to read from (use '?Tag' to indirectly reference a file and resolve the reference in Model>Settings>XLS Files). It should be in the same directory as the current mode. 'tab' names the tab that contains the Data. 'firstcell' is the location of the first subscript element. 'lastcell' is the location of the last subscript element. The standard spreadsheet notation of letter number (up to XFDnnnnn) is used to refer to locations. Values are read across and then down as a list of subscripts, with missing or empty locations simply ignored. Instead of a cell entry for 'lastcell' you can specify a row such as '4' or a column such as 'G' .If 'lastcell' is a letter then all rows in that column will be read (normally the same column as 'firstcell'). If 'lastcell' is a number all columns in that row will be read. Lastcell can be left blank '' with GET DIRECT SUBSCRIPTS but this will generally not work with GET XLS SUBSCRIPTS.
'prefix' is used to modify the contents of the file. This is useful when some entries in th file are numeric and it can prevent the necessity to use quotation marks and it can also be helpful to in insuring that all elements have unique names.
NOTES
| • If Microsoft Excel is not running Vensim will attempt to start it. |
| • | If Microsoft Excel does not have the specified file open Vensim will try to get Microsoft Excel to open it. |
| • | Changes to String Variables made in .cin files (or through Venapp commands) will not be used when determining file, tab or cell. |
| • | You can substitute named Excel ranges for 'firstcell'. In this case 'lastcell' is ignored. |
Units: Subscripts are not part of units checking.
Example
FromXLSEarly : GET XLS SUBSCRIPT('ExternalSubscripts.xls','Sheet1','A1','A19','Excel') -> FromXLSLate
FromXLSLate : GET XLS SUBSCRIPT('ExternalSubscripts.xls','Sheet1','A2','A20','Excel')
FromXLS : GET XLS SUBSCRIPT('ExternalSubscripts.xls','Sheet1','A1','A20','Excel')
TestVal[Excel100001] = 1
TestVal[FromXLSLate] = TestVal[FromXLSEarly] + 1
In the above example the cell A1 in the spreadsheet contains 100001, and the other subscript values are all in column A.
NOTE On the Macintosh GET XLS SUBSCRIPT and GET DIRECT SUBSCRIPT both open files directly.
Availability: Professional and DSS only. Models using this will work in the Model Reader but the source file is not required.
See also: GET DIRECT SUBSCRIPT, GET ODBC SUBSCRIPT
Sample model: GET XLS SUBSCRIPT.mdl in FunctionExamples
