;; xlLib - a library of MS Excel interface functions
;;
;; A collection of UDFs to interface with MS Excel
;; Extensive testing with MS Office XP, limited testing with Office 2K3, 
;; otherwise untested! 
;;
;; This version is released for review, comments, and suggestions.
;;
;; Glenn Barnas
;; Version 1.0	2007/09/26	Updated xlRangeValue, added xlQuit
;; Version 0.8	2005/11/08	First release
;;
;;	xlInstantiateApp()	Create the Excel object reference
;;	xlNewBook()		Create a new workbook in the active object
;;	xlAddSheet()		Create a new worksheet
;;	xlSheetName()		Return the active sheet name, set the specified sheet's name
;;	xlRangeValue()		Read or write a worksheet range
;;	xlRangeClear()		Clear the specified worksheet range
;;	xlRangeFormat()		Apply formatting to the specified worksheet range
;;	xlFile()		Open, Save, or SaveAs file functions
;;	xlQuit()		Destroy the instantiated object

;;
;;======================================================================
;;
;;FUNCTION       xlInstantiateApp()
;;
;;ACTION         Instantiates the MS Excel application
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlInstantiateApp()
;;
;;PARAMETERS     None
;;
;;REMARKS        
;;
;;RETURNS        Pointer to Excel Application Object
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       $oXL = xlInstantiateApp()
;
Function xlInstantiateApp()

  $xlInstantiateApp = CreateObject('EXCEL.Application')
  Exit @ERROR

EndFunction



;;
;;======================================================================
;;
;;FUNCTION       xlNewBook()
;;
;;ACTION         Creates a new Excel Workbook
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlNewBook(Object_ID [, Sheets] , [Properties])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Sheets     - OPTIONAL, Number of sheets to create. Default is 1
;;		 Properties - OPTIONAL, Array of workbook document properties
;;
;;REMARKS        Creates a new workbook with the defined number of sheets.
;;		 Each sheet has the default name (Sheet#). The optional array
;;		 has 8 elements, defining the document properties.
;;
;;RETURNS        Workbook Object ID
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       $WBo = xlNewBook($oXL, 4, $aryProp)
;
Function xlNewBook($_ID, OPTIONAL $_Sheets, OPTIONAL $_aProp)

  Dim $_, $_Err

  $_Sheets = IIf(Val($_Sheets) = 0, 1, Val($_Sheets))
  $_ID.SheetsInNewWorkbook = $_Sheets

  $xlNewBook = $_ID.Workbooks.Add()		; add a new workbook
  $_Err = @ERROR

  If VarType($_aProp) > 8191
    ; Define optional properties from array
    If $_aProp[0] $xlNewBook.Title	= $_aProp[0] EndIf
    If $_aProp[1] $xlNewBook.Subject	= $_aProp[1] EndIf
    If $_aProp[2] $xlNewBook.Author	= $_aProp[2] EndIf
    If $_aProp[3] $xlNewBook.Manager	= $_aProp[3] EndIf
    If $_aProp[4] $xlNewBook.Company	= $_aProp[4] EndIf
    If $_aProp[5] $xlNewBook.Category	= $_aProp[5] EndIf
    If $_aProp[6] $xlNewBook.Keywords	= $_aProp[6] EndIf
    If $_aProp[7] $xlNewBook.Comments	= $_aProp[7] EndIf
  EndIf

  Exit $_Err

EndFunction



;;
;;======================================================================
;;
;;FUNCTION       xlAddSheet()
;;
;;ACTION         Adds a new worksheet to an Excel spreadsheet
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlAddSheet(Object_ID [, Position] [, Name] [, TabColor])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Position   - OPTIONAL, Position of new sheet
;;				Null     - placed before active sheet
;;				[-]name  - placed before named sheet
;;				+name    - placed after named sheet
;;		 Name       - OPTIONAL, Name of new worksheet
;;		 TabColor   - OPTIONAL, Color of new worksheet tab (Color Index #)
;;
;;REMARKS        
;;
;;RETURNS        Name of the new sheet
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       ; Add a new sheet called "may" after the "April" sheet
;;		 xlAddSheet($oXL, '+April', 'May', 33)
;
Function xlAddSheet($_ID, OPTIONAL $_Position, OPTIONAL $_Name, OPTIONAL $_TabColor)

  Dim $_, $_Err

  If Left($_Position, 1) = '+'
    $_Position = SubStr($_Position, 2)		; trim leading '+'
    $_ = $_ID.Worksheets.Add(, $_ID.Worksheets($_Position), 1)
  Else
    If $_Position
      If Left($_Position, 1) = '-'
        $_Position = SubStr($_Position, 2)	; trim leading '-'
      EndIf
      $_ = $_ID.Worksheets.Add($_ID.Worksheets($_Position), , 1)
    Else
      $_ = $_ID.Worksheets.Add
    EndIf
  EndIf

  $_Err = @ERROR				; save the creation status

  If $_Name
    $_ID.ActiveSheet.Name = $_Name		; define the name, if supplied
  EndIf

  If $_TabColor
    $_ID.ActiveSheet.Tab.ColorIndex = $_TabColor
  EndIf

  $xlAddSheet = $_ID.ActiveSheet.Name		; return the name of the new sheet
  Exit $_Err

EndFunction



;;
;;======================================================================
;;
;;FUNCTION       xlSheetName()
;;
;;ACTION         Renames the defined sheet
;;		 Returns the name of the active sheet
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlSheetName(Object_ID [, Old, New])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Old        - OPTIONAL, Old sheet name
;;		 New        - OPTIONAL, New sheet name
;;
;;REMARKS        If old name is not specified, the name of the active sheet is returned.
;;		 If the old name is specified, that sheet is changed to the new name. An
;;		 error is returned if OLD is specified and NEW is not.
;;
;;RETURNS        Namem of active sheet if no name args are present
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       $SheetName = xlSheetName($oXL)			; get current sheet name
;;		 xlSheetName($0XL, $SheetName, 'NewSheetName')	; change it!
;
Function xlSheetName($_ID, OPTIONAL $_Old, OPTIONAL $_New)

  ; if Old name not provided, return the active sheet name
  If $_Old = ''
    $xlSheetName = $_ID.ActiveSheet.Name
    Exit @ERROR
  EndIf

  ; complain if New name isn't provided
  If VarType($_New) = 0
    Exit 87
  EndIf

  ; Rename the sheet
  $_ID.Worksheets($_Old).Name = $_New
  Exit @ERROR

EndFunction



;;
;;======================================================================
;;
;;FUNCTION       xlRangeValue()
;;
;;ACTION         Reads / Writes a range of cells
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlRangeValue(Object_ID, Range [, Value] [, Sheet])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Range      - REQUIRED, A standard range specification (A1:C3)
;;		 Value      - OPTIONAL, The value to place into the range
;;		 Sheet      - OPTIONAL, The worksheet name to read/write
;;
;;REMARKS        Defaults to the active sheet if not specified.
;;		 To READ a range value, the Value parameter must not be
;;		 specified! Both values and formulas can be written.
;;
;;RETURNS        Variant containing the data from the defined cell range
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       READ:	$Val = xlRangeValue($XL, 'A1', , 'Sheet1')
;;		 WRITE:	xlRangeValue($XL, 'A1', '=Sum(A2:a9)', 'Sheet1')
;
Function xlRangeValue($_ID, $_Range, OPTIONAL $_Value, OPTIONAL $_Sheet)

  Dim $_Rv, $_X, $_Y, $_D

  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)

  If VarType($_Value) <> 0
    $_ID.WorkSheets($_Sheet).Range($_Range).Value = $_Value
  Else
    $Rv = $_ID.WorkSheets($_Sheet).Range($_Range).Value
    If VarType($Rv) < 8192		; return a straight value
      $xlRangeValue = $Rv
    Else			; return an array
      If UBound($Rv) = 1	; 1 row by x columns
        $xlRangeValue = Split(Join($Rv, Chr(31)), Chr(31))
      Else			; several rows of X columns
        Dim $aTmp[UBound($Rv) - 1]
        $_X = 0 $_Y = UBound($Rv)
        For Each $_ in $Rv
          $aTmp[$_X Mod $_Y] = $aTmp[$_X Mod $_Y] + $_D + $_
          $_X = $_X + 1
          If $_X = $_Y $_D = Chr(31) EndIf
        Next
        For $_ = 0 to UBound($aTmp)
          $aTmp[$_] = Split($aTmp[$_], Chr(31))
        Next
        $xlRangeValue = $aTmp
      EndIf
    EndIf
  EndIf

  Exit @ERROR

EndFunction



;;
;;======================================================================
;;
;;FUNCTION       xlRangeClear()
;;
;;ACTION         Clears a range of cells
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlRangeClear(Object_ID, Range [, Sheet])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Range      - REQUIRED, A standard range specification (A1:C3)
;;		 Sheet      - OPTIONAL, The worksheet name to reference
;;
;;REMARKS        Defaults to the active sheet if not specified.
;;
;;RETURNS        Nothing
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       
;
;; 
Function xlRangeClear($_ID, $_Range, OPTIONAL $_Sheet)

  Dim $_

  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)

  $_ = $_ID.WorkSheets($_Sheet).Range($_Range).Clear

  Exit @ERROR

EndFunction


;;
;;======================================================================
;;
;;FUNCTION       xlRangeFormat()
;;
;;ACTION         Sets format info for a range of cells
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlRangeFormat(Object_ID, Range, Format [, Sheet])
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Range      - REQUIRED, A standard range specification (A1:C3)
;;		 Format     - REQUIRED, The format array to set
;;		 Sheet      - OPTIONAL, The worksheet name to read/write
;;
;;REMARKS        Defaults to the active sheet if not specified. 
;;		 Format array is:
;;			0: FontName
;;			1: FontStyle   (Normal, Bold, Italic, Bold Italic)
;;			2: FontColor   (Index)
;;			3: FontSize
;;			4: CellColor   (Index)
;;
;;RETURNS        Variant containing the data from the defined cell range
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       xlRangeValue($XL, 'A1', $aFmt, 'Sheet1')
;
Function xlRangeFormat($_ID, $_Range, $_Format, OPTIONAL $_Sheet)

  Dim $_

  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)

  If $_Format[0]
    $_ID.WorkSheets($_Sheet).Range($_Range).Font.Name = $_Format[0]
  EndIf

  If $_Format[1]
    $_ID.WorkSheets($_Sheet).Range($_Range).Font.FontStyle = $_Format[1]
  EndIf

  If $_Format[2]
    $_ID.WorkSheets($_Sheet).Range($_Range).Font.ColorIndex = $_Format[2]
  EndIf

  If $_Format[3]
    $_ID.WorkSheets($_Sheet).Range($_Range).Font.Size = $_Format[3]
  EndIf

  If $_Format[4]
    $_ID.WorkSheets($_Sheet).Range($_Range).Interior.ColorIndex = $_Format[4]
  EndIf

  Exit

EndFunction


;;
;;======================================================================
;;
;;FUNCTION       xlFile()
;;
;;ACTION         Opens / Saves an Excel file
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlFile(Object_ID, Function, Filespec)
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;		 Function   - REQUIRED, 0=Open, 1=Save(as), 2=SaveAs
;;		 File	    - REQUIRED, Filespec of file to open/save
;;
;;REMARKS        If function is "1", it will save to the current file if 
;;		 the file was opened or previously saved, otherwise it will
;;		 save the file using the defined filespec. When function is
;;		 "2", it will always save the file using the defined filespec.
;;
;;RETURNS        Nothing
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       
;
Function xlFile($_ID, $_FN, $_File)

  Dim $_

  Select
    Case $_FN = 0			; Open
      If Exist($_File)			; if the file exists, open it
        ;$_ = $_ID.Open($_File, $_Links, $_RO, $_Fmt)
        $_ = $_ID.WorkBooks.Open($_File)
        Exit @ERROR
      Else
        Exit 2				; otherwise complain that is isn't found
      EndIf

    Case $_FN = 1			; Save(as)
      ; If the file was previously saved (or opened), just re-save it,
      ; otherwise do a Save As
      If Not $_ID.ActiveWorkbook.Path
        $_ = $_ID.ActiveWorkbook.SaveAs($_File)
      Else
        $_ = $_ID.ActiveWorkbook.Save
      EndIf
      Exit @ERROR

    Case $_FN = 2			; SaveAs
      $_ = $_ID.ActiveWorkbook.SaveAs($_File)
      Exit @ERROR

EndFunction


;;
;;======================================================================
;;
;;FUNCTION       xlQuit()
;;
;;ACTION         Terminates the Excel connection
;;
;;AUTHOR         Glenn Barnas
;;
;;SYNTAX         xlQuit(Object_ID)
;;
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer
;;
;;REMARKS        This should be called to properly exit the Excel application
;;
;;RETURNS        Nothing
;;
;;DEPENDENCIES   Excel
;;
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3
;;
;;EXAMPLES       
;
Function xlQuit($_ID)

  $_ID.Quit
  $_ID = 0

  Exit 0

EndFunction