{ From user Dave, Model Functions_for_readin at 21-Apr-2015 2:28:35 AM, encoding="UTF-8" }
SoftwareVersion 4.6.0


{ System Variables with non-default values: }
UseTable := 0
TypeChecking := 1
Checking := 1
SaveOptions := 2
SaveValues := 0
{!40300|Sys_DomainSelfIndex := 1}
{!40400|Sys_AllNullTreatment := 1}




Model Functions_for_readin
Title: Functions for reading from Excel Spreadsheets
Description: Hidden within the new release of Analytica 4.1 are three new functions for reading values directly from Excel spreadsheets: OpenExcelFile, SpreadsheetCell, SpreadsheetRange. These provide an alternative to OLE linking and ODBC for reading data from spreadsheets, which may be more convenient, flexible and reliable in many situations. We have not yet exposed these functions on the Definitions menu or in the Users Guide in release 4.1, since they are still in an experimental stage. I would like know that they have been "beta-tested" in a variety of scenarios before we fully expose them (also, the symmetric functions for writing don't exist yet). In this webinar, I will introduce and demonstrate these functions, after which you can start using them with your own problems. ~
~
These functions require Analytica Enterprise
Author: Lonnie
Date: Thu, Apr 24, 2008 9:56 AM
SaveAuthor: Dave
SaveDate: Tue, Apr 21, 2015 2:28 AM
DefaultSize: 48,24
DiagState: 2,1,7,554,488,17
WindState: 2,18,31,525,296
FontStyle: Arial, 15
FileInfo: 0,Model Functions_for_readin,2,2,0,0,C:\Users\Dave\Desktop\rep6\Work new repo\models not in example models\Functions_for_Reading_Excel_Spreadsheets(1).ana
{!40400|Att_clearTypeFonts: 0}

Constant Solvsamp
Title: SolvSamp
Definition: "SolvSamp.xls"
NodeLocation: 88,56,1
NodeSize: 48,24

Variable Wb
Title: wb
Definition: SpreadsheetOpen( SolvSamp )
NodeLocation: 200,56,1
NodeSize: 48,24

Button Reread
Title: Reread
NodeLocation: 88,320,1
NodeSize: 48,24
Aliases: Alias Reread1
Script: ( Reset := Reset+1 )

Variable Reset
Title: Reset
Definition: 6
NodeLocation: 88,384,1
NodeSize: 48,24

Module Spreadsheet_cell_exa
Title: Spreadsheet Cell examples
Author: Lonnie
Date: Thu, Apr 24, 2008 12:35 PM
DefaultSize: 48,24
NodeLocation: 200,136,1
NodeSize: 48,32
DiagState: 2,1,0,720,430,17

Variable Product_price
Title: Product price
Definition: reset ; SpreadsheetCell( wb, "Quick Tour", "B", 18 )
NodeLocation: 88,48,1
NodeSize: 48,24

Index Quarter
Title: Quarter
Definition: reset ; SpreadsheetCell( wb, "Quick Tour", 2..5, 2 )
NodeLocation: 88,112,1
NodeSize: 48,24
ReformVal: [Self,Undefined]
{!40000|Att_PrevIndexValue: ['Q1','Q2','Q3','Q4']}

Variable Q_col
Title: Q_Col
Definition: Table(Quarter)('B','C','D','E')
NodeLocation: 88,176,1
NodeSize: 48,24
DefnState: 2,140,280,416,303,0,MIDM
ValueState: 2,136,139,416,303,0,MEAN

Variable Seasonality
Title: Seasonality
Definition: SpreadsheetCell( wb, "Quick Tour", Q_Col, 3 )
NodeLocation: 200,176,1
NodeSize: 48,24
ReformVal: [Quarter,Undefined]

Variable Seasonality2
Title: Seasonality2
Definition: SpreadsheetCell( wb, "Quick Tour", @Quarter+1 , 3 )
NodeLocation: 200,112,1
NodeSize: 48,24
NodeInfo: 1,1,1,1,1,1,0,0,0,0

Close Spreadsheet_cell_exa

Module Spreadsheet_range_ex
Title: Spreadsheet Range examples
Author: Lonnie
Date: Thu, Apr 24, 2008 12:35 PM
DefaultSize: 48,24
NodeLocation: 320,136,1
NodeSize: 48,32
DiagState: 2,1,7,550,422,17

Alias Reread1
Title: Reread
Definition: 1
NodeLocation: 96,48,1
NodeSize: 48,24
Original: Reread

Variable Product_cost
Title: Product cost
Definition: SpreadsheetRange( wb,"Product_cost")
NodeLocation: 88,128,1
NodeSize: 48,24

Variable Quarter2
Title: Quarter2
Definition: ComputedBy( Quarter_col )
NodeLocation: 232,48,1
NodeSize: 48,24
ValueState: 2,117,274,416,303,0,MIDM
ReformVal: [Self,Undefined]

Variable Quarter_col
Title: Quarter Col
Definition: Reset;~
var tmp := SpreadsheetRange(wb, "Quarter");~
Quarter2 := CopyIndex(tmp);~
tmp.Column [ @.Column = @Quarter2 ]
NodeLocation: 360,48,1
NodeSize: 48,24

Variable Sales_revenue
Title: Sales revenue
Definition: SpreadsheetRange( wb, "Sales_revenue", colIndex : Quarter2 )
NodeLocation: 232,128,1
NodeSize: 48,24
ReformVal: [Quarter2,Undefined]
NumberFormat: 2,I,4,2,1,1,4,0,$,0,"ABBREV",0

Variable Tour_table
Title: Tour Table
Definition: Reset ; SpreadsheetRange( wb, "Tour_table", howToIndex: ColHeads+RowHeads )
NodeLocation: 88,208,1
NodeSize: 48,24
ValueState: 2,23,46,526,370,0,MIDM
ReformVal: [Sys_LocalIndex('COLUMN'),Sys_LocalIndex('ROW')]

Variable Person
Title: Person
Definition: CopyIndex( Person_temp )
NodeLocation: 232,208,1
NodeSize: 48,24

Variable Person_temp
Title: Person Temp
Definition: reset ;SpreadsheetRange( wb,  "Person", howToIndex: ForceRow )
NodeLocation: 344,208,1
NodeSize: 48,24
ReformVal: [Sys_LocalIndex('ROW'),Sys_LocalIndex('COLUMN')]

Variable Age
Title: Age
Definition: SpreadsheetRange( wb, "Age", rowIndex: Person )
NodeLocation: 232,264,1
NodeSize: 48,24

Constant Forcecol
Title: ForceCol
Definition: 1
NodeLocation: 472,48,1
NodeSize: 48,24

Constant Forcerow
Title: ForceRow
Definition: 2
NodeLocation: 472,104,1
NodeSize: 48,24

Constant Rowheads
Title: RowHeads
Definition: 8
NodeLocation: 472,208,1
NodeSize: 48,24

Constant Colheads
Title: ColHeads
Definition: 4
NodeLocation: 472,264,1
NodeSize: 48,24

Module Caveats
Title: Caveats
Author: Lonnie
Date: Thu, Apr 24, 2008 12:35 PM
DefaultSize: 48,24
NodeLocation: 96,352,1
NodeSize: 48,24
DiagState: 2,28,36,555,402,17

Variable Person2
Title: Person2
Definition: SpreadsheetRange( wb,  "Person" )
NodeLocation: 112,72,1
NodeSize: 48,24
ValueState: 2,178,87,416,303,0,MIDM

Variable Age2
Title: Age2
Definition: SpreadsheetRange( wb, "Age" )
NodeLocation: 224,72,1
NodeSize: 48,24
ValueState: 2,606,87,416,303,0,MIDM

Variable Combination
Title: Combination
Definition: Person2 & " --> " & Age2
NodeLocation: 344,72,1
NodeSize: 52,24
ValueState: 2,601,434,416,303,0,MIDM
ReformVal: [Sys_LocalIndex('ROW'),Sys_LocalIndex('ROW')]

Variable Joined
Title: Joined
Definition: Join( Combination, Person2.Row, "," )
NodeLocation: 456,72,1
NodeSize: 48,24

Variable Age3
Title: Age3
Definition: SpreadsheetRange( wb,"Age", RowIndex: Person2.Row )
NodeLocation: 224,136,1
NodeSize: 48,24
ValueState: 2,343,220,416,303,0,MIDM

Variable Combination2
Title: combination2
Definition: person2 & " --> " & Age3
NodeLocation: 344,136,1
NodeSize: 48,24

Index Range_item
Title: Range item
Definition: ["Person","Age" ]
NodeLocation: 112,208,1
NodeSize: 48,24

Variable Get
Title: Get
Definition: SpreadsheetRange( wb, Range_item )
NodeLocation: 224,208,1
NodeSize: 48,24
ValueState: 2,538,47,416,303,0,MIDM
ReformVal: [Sys_LocalIndex('Row'),Range_item]

Index Distinction
Title: Distinction
Definition: ['Age','Next_year']
NodeLocation: 112,288,1
NodeSize: 48,24

Variable Read_full_array
Title: read full array
Definition: SpreadsheetRange( wb, Distinction, rowIndex: Person)
NodeLocation: 232,288,1
NodeSize: 48,24
ReformVal: [Person,Distinction]

Close Caveats

Close Spreadsheet_range_ex

Close Functions_for_readin