{ From user Lonnie, Model Spreadsheet_Helper_L at 9-Oct-2012 9:14:37 AM }
SoftwareVersion 4.4.4



LinkLibrary Spreadsheet_Helper_L
Title: Spreadsheet Helper Lib
Description: This library contains some spreadsheet functions.~
~
The one of most interest is SpreadsheetOpenEx, which can be used instead of SpreadsheetOpen.  The difference is that it can remember what file the user selected, so he doesn't have to re-select it every time.
Author: Evan Sherwin and Lonnie Chrisman~
Lumina Decision Systems
Date: Tue, Oct 9, 2012 7:41 AM
SaveAuthor: Lonnie
SaveDate: Tue, Oct 9, 2012 9:14 AM
DiagState: 2,1,0,659,518,17
WindState: 2,308,245,476,224
FontStyle: Arial,15
FileInfo: 0,LinkLibrary Spreadsheet_Helper_L,2,2,0,0,W:\Testmodels\Spreadsheet Helper Lib.ana

Variable wb_object
Title: wb object
Definition: spreadsheetopenEx(Spreadsheet_filename,showDialog:1 )
NodeLocation: 240,72,1
NodeSize: 48,24
ValueState: 2,726,151,416,303,0,MIDM

Variable Spreadsheet_filename
Title: Spreadsheet filename
Definition: "ss2.xls"
NodeLocation: 96,72,1
NodeSize: 56,24

Function Worksheets(wb : ExcelWorkbookType atom)
Title: Worksheets
Description: Returns a list of the names of worksheets in an Excel workbook.
Definition: CopyIndex(SpreadsheetCell(wb,"*",1,1).Sheet)
NodeLocation: 88,224,1
NodeSize: 56,24

Function Spreadsheet_Filepath(wb : ExcelWorkbookType atom)
Title: Spreadsheet Filepath
Description: Returns the full filepath to the current Excel workbook.  If the file isn't yet saved, returns the empty text.
Definition: var sheets := Worksheets(wb);~
var sheet := Slice(sheets,size(sheets));~
var prev := SpreadsheetCell(wb,sheet,"Z",10001,what:"formula");~
spreadsheetSetCell(wb,Sheet,"Z",10001,'=Cell("filename")'); ~
var cellresult := SpreadsheetCell(wb,Sheet,"Z",10001);~
spreadsheetSetCell(wb,Sheet,"Z",10001,prev); ~
TextReplace(cellresult,"^(.*)\[(.*)\].*","\1\2",re:1)
NodeLocation: 224,224,1
NodeSize: 64,24
WindState: 2,46,445,811,265

Function Filename_part(filepath)
Title: Filename part
Description: Returns the file name part of a file path.
Definition: FindInText("[^\\]*$",filepath,return:"S",re:1)
NodeLocation: 96,280,1
NodeSize: 64,24

Function Directory_part(filepath)
Title: Directory part
Description: Given a full filepath, returns just the directory part.
Definition: FindInText(".*\\",filepath,return:"S",re:1)
NodeLocation: 224,280,1
NodeSize: 64,24

Function SpreadsheetOpenEx(filename:atomic text; ~
showDialog : optional atomic boolean;~
filenameVar : hidden optional Variable = filename)
Title: SpreadsheetOpenEx
Description: Like SpreadsheetOpen, except that the first parameter must be a variable identifier that holds the filename.  After opening the workbook, it extracts the filename (which may be different if the user has used the showDialog to find it) and sets the definition of the variable to hold this filename, so that the next time it is evaluated, it remembers which file the user selected.
Definition: var dataDir := CurrentDataDirectory();~
var wb := SpreadsheetOpen(filename,showDialog);~
CurrentDataDirectory(dataDir);~
var filepath := Spreadsheet_Filepath(wb);~
if filepath<>null then begin~
  var fn := Filename_part(filepath);~
  var dir := Directory_part(filepath);~
  if not( filepath=filenameVar or (fn=filenameVar and dir=dataDir) ) then (~
      { The filename opened is not the same as the one in the variable }~
      if dir=dataDir Then ~
           EvaluateScript("Definition of " & (identifier of FilenameVar) & ":""" & fn & """")~
      else~
            EvaluateScript("Definition of " & (identifier of FilenameVar) & ":""" & filepath & """");~
      filenameVar  { Ensure evaluated }  ~
  )~
end;~
wb
NodeLocation: 120,136,1
NodeSize: 88,24
WindState: 2,456,11,764,525

Close Spreadsheet_Helper_L