Returns time series data from Microsoft Excel for a Data variable or a vector of Data variables.
Restrictions: Must appear directly following the data equals sign := and not be followed by anything.
The GET XLS DATA function is invoked during simulation setup, before the active simulation begins. 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 DATA 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). This can be an Excel file or any other type of file Microsoft Excel is capable of opening. If no directory is specified (best practice) Vensim will append directory information for the current model. 'tab' names the tab that contains the Data. This function will not work with older versions of Excel that do not support tabs. 'time row or col' is either the number of the row containing Time values (Time running across) or the letter of the column containing Time values (Time running down). Note that the spreadsheet file must contain values for Time and these values must be Time values and not those of an alternate Time Base. 'cell' names the cell that the first Data value is on. Vensim will read values for different times across or down depending on the 'time row or col' argument. Vensim will also read additional subscript elements in the other direction.
NOTES
| • | If Microsoft Excel is not running Vensim will attempt to start it. |
| • | You can substitute named Excel ranges for 'time row or col' and 'cell'. To do this just create the named ranges in Excel and use those names in place of the Letter/Number designators. |
| • | Vensim will continue to read the Time row or column to the end of the spreadsheet, ignoring any blank or nonnumeric cells. If you want to have it read only a specific range use a named range. |
| • | Changes in String Variables from .cin files (or from Venapp Commands) will be used when determining file, tab, time and cell. |
Units: GET XLS DATA is not part of units checking. Specify units for the left hand side variable.
Example
profit := GET XLS DATA('test.xls','Sheet2','1','B2') ~~|
shape sales[shape] := GET XLS DATA('test.xls','Sheet2','1','B3') ~~|
sales[round,shape] := GET XLS DATA('test.xls','Sheet2','1','B5') ~~|
sales[square,shape] := GET XLS DATA('test.xls','Sheet2','1','B8')~~|
The contents of the spreadsheet are shown below. Note that the variable names do not need to match and that the order of subscripts determines how the data in the spreadsheet are interpreted.
|
|
A |
B |
C |
D |
E |
|
1 |
Time |
0 |
10 |
80 |
100 |
|
2 |
Profit |
8 |
9 |
6 |
12 |
|
3 |
Sales by Shape[round] |
30 |
44 |
55 |
42 |
|
4 |
Sales by Shape[square] |
22 |
13 |
77 |
30 |
|
5 |
Sales[round,small] |
10 |
12 |
11 |
20 |
|
6 |
Sales[round,med] |
10 |
12 |
11 |
15 |
|
7 |
Sales[round,large] |
10 |
10 |
33 |
7 |
|
8 |
Sales[square,small] |
5 |
10 |
30 |
10 |
|
9 |
Sales[square,med] |
5 |
10 |
30 |
12 |
|
10 |
Sales[square,large] |
12 |
24 |
17 |
8 |
NOTE GET XLS DATA is the same as GET DIRECT DATA on the Macintosh.
Availability: Not PLE.
See also: GET DIRECT DATA
Sample model: GET XLS DATA.mdl and GET XLS DATA[ss].mdl in FunctionExamples
