;;KGEN:NOPARSE 
;;HEADER:Lib-XL:1.2:Glenn Barnas:A collection of Microsoft Excel functions 
;;LIBRARY         Lib-XL - Excel Function Library 
;; 
;;ACTION          A collection of UDFs to interface with MS Excel 
;;                Extensive testing with MS Office XP, and Office 2K3 
;; 
;; Most functions in this library are dual-purpose. If you specify  
;; a data value, it will be written. If you do not specify data values, 
;; the current data or setting values will be returned. 
;; 
;; 
;;AUTHOR          Glenn Barnas 
;; 
;;VERSION         1.2a - 2020/08/02 
;; 
;;HISTORY         0.8  - 2005/11/08 - Beta release 
;;                1.0  - 2007/10/01 - Initial Release 
;;                1.1  - 2008/03/31 - Official release integrated with Erik Kaerholm's library 
;;                1.2  - 2020/06/07 - Enhancements: 
;;                                    - New Export() function for PDF/XPS output 
;;                                    - Add support for alternate file formats - CSV, HTM, etc. on SaveAs 
;;                                    - Add support for password protection on Open/SaveAs 
;;                   a - 2020/08/02   Missing var def in xlRangeSort, fix to xlRangeValue that failed to return 
;;                                    data when a single-wide column of data was requested. 
;;         
;;  
;;	xlInit()		Create the Excel object reference 
;;	xlQuit()		Destroy the instantiated object & shut down Excel 
;;	xlFile()		Open, Save, or SaveAs file functions 
;;      xlExport()              Export the Active Sheet to PDF or XPS document 
;;	xlBookCreate()		Create a new workbook in the active object 
;;	xlBookProperties()	Read / Set the workbook properties 
;; 
;;	xlSheetAdd()		Create a new worksheet, define properties 
;;	xlSheetName()		Read / Set the active worksheet name 
;;	xlSheetSelect()		Set the active worksheet by name or index # 
;;	xlSheetDelete()		Delete the specified sheet 
;;	xlSheetPrint()		Prints the specified sheet 
;;	xlSheetCount()	   (EK)	Returns the number of sheets in the workbook 
;; 
;;	xlRangeValue()		Read / Write a worksheet cell or range 
;;	xlRangeClear()		Clear the specified worksheet range 
;;	xlRangeDelete()		Delete the specified worksheet range 
;;	xlRangeFormat()		Read / Set style formatting attributes on a cell range 
;;	xlRangeFormatNum() (EK)	Read / Set number formatting attributes on a cell range 
;;	xlRangeFreeze()		Set / Unset display pane freezing at a specific cell 
;;	xlRangePrint()		Prints the specified worksheet range 
;;	xlRangeSearch()	   (EK)	Searches a range for a value 
;;	xlRangeSort()	   (EK)	Sorts the specified range 
;; 
;;	xlPageSetup()		Read / Set global format and printing options 
;;	xlWindow()		Read / Set the Excel application window visibility status 
;; 
;; The following are utility functions to aid in the manipulation of Excel settings 
;;	xlCol()			Translate between alpha and numeric column IDs 
;;	xlColRow()		Splits a cell reference (A1) into component reference parts (A, 1) 
;;	xlColor()		Translates between color names and index values 
;; 
;; The functions marked "(EK)" were originally developed by Erik Kaerholm and adapted 
;; to this library by Glenn Barnas. Erik's original library can be found at 
;; http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=86576 
;; 
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlInit() 
;; 
;;ACTION         Instantiates the MS Excel application 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlInit() 
;; 
;;PARAMETERS     None 
;; 
;;REMARKS        Launches Excel via COM 
;; 
;;RETURNS        Pointer to Excel Application Object 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $oXL = xlInit() 
;;               ; Once this object is instantiated, individual cells may be  
;;               ; referenced directly via $oXL.Cells(colID, rowID).Value 
;;               ; Cell and Row IDs are numeric 
;;               $CellData = $oXL.Cells(1, 4).Value	; read a single cell 
;;               $oXL.Cells(2, 4).Value = '=A4*1.3'	; put a formula into a single cell 
; 
Function xlInit()
 
  $xlInit = CreateObject('EXCEL.Application')
  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       xlQuit($xl) 
; 
Function xlQuit($_ID)
 
  $_ID.DisplayAlerts = 0
  $_ID.Quit
  $_ID = 0
 
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlFile() 
;; 
;;ACTION         Opens / Saves an Excel file 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlFile(Object_ID, Function, Filespec [, Format] [, PWord]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Function   - REQUIRED, 0=Open, 1=Save(as), 2=SaveAs 
;;		 Filespec   - REQUIRED, Filespec of file to open/save 
;;               Format     - OPTIONAL, ID for file save-as format. 
;;                              6=CSV, 44=HTML 
;;               PWord      - a 1-15 character password to use with open or save-as 
;; 
;;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. 
;;               Function value may be numeric or text. (eg: 1 or "save") 
;; 
;;RETURNS        Nothing 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES        
; 
Function xlFile($_ID, $_FN, $_File, OPTIONAL $_Fmt, $_Pword)
 
  Dim $_
 
  $_Fmt = IIf($_Fmt, $_Fmt, 51)
 
  Select
   Case $_FN = 0 Or $_FN = 'open'	; Open 
    If Exist($_File)			; if the file exists, open it 
      $_ = $_ID.WorkBooks.Open($_File, , , , $_Pword)
      $xlFile = Not @ERROR
      Exit @ERROR
    Else
      $xlFile = 0
      Exit 2				; otherwise complain that is isn't found 
    EndIf
 
   Case $_FN = 1 Or $_FN = 'save'	; 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, $_Fmt, $_Pword)
    Else
      $_ = $_ID.ActiveWorkbook.Save
    EndIf
    $xlFile = Not @ERROR
    Exit @ERROR
 
   Case $_FN = 2 Or $_FN = 'saveas'	; SaveAs 
    $_ = $_ID.ActiveWorkbook.SaveAs($_File, $_Fmt, $_Pword)
    $xlFile = Not @ERROR
    Exit @ERROR
  EndSelect
 
EndFunction
 
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlExport() 
;; 
;;ACTION         Exports an Excel Active Worksheet to PDF or XPS format 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlExport(Object_ID, [, Format]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;               Format     - OPTIONAL, ID or Term for export format. 
;;                              0 / PDF or 1 / XPS 
;; 
;;REMARKS         
;; 
;;RETURNS        Nothing 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES        
; 
Function xlExport($_ID, $_File, OPTIONAL $_Fmt)
 
  Dim $_
  
  ; Convert Text Format to Numeric 
  $_ = InStr('xxPDFXPS', $_Fmt)
  If $_
    $_Fmt = ($_ / 3) - 1
  EndIf
 
  ; Define default format of "PDF" 
  $_Fmt = IIf($_Fmt, $_Fmt, 0)
  ; ensure format is either 0/1 
  If $_Fmt < 0 Or $_Fmt > 1 Exit 87 EndIf
 
  ; Export to the desired format 
  $_ = $_ID.ActiveWorkbook.ExportAsFixedFormat($_Fmt, $_File)
  $xlExport = Not @ERROR
  Exit @ERROR
 
EndFunction
 
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlBookCreate() 
;; 
;;ACTION         Creates a new Excel Workbook 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlBookCreate(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 
;;		 defines the document properties. 
;; 
;;RETURNS        Workbook Object ID - can be used instead of application object 
;;		 if multiple workbooks are defined. 
;; 
;;DEPENDENCIES   Excel, xlBookProperties() 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $WBo = xlNewBook($oXL, 4, $aryProp) 
; 
Function xlBookCreate($_ID, OPTIONAL $_Sheets, OPTIONAL $_aProp)
 
  Dim $_
 
  $_Sheets = IIf(Val($_Sheets) = 0, 1, Val($_Sheets))
  $_ID.SheetsInNewWorkbook = $_Sheets
 
  ; add a new workbook 
  $xlBookCreate = $_ID.Workbooks.Add()
  If @ERROR Exit @ERROR EndIf
 
  ; set the workbook properties if defined 
  If VarType($_aProp) > 8191
    $_ = xlBookProperties($xlBookCreate, $_aProp)
  EndIf
 
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlBookProperties() 
;; 
;;ACTION         Read / Set Workbook properties 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlBookProperties(Object_ID [, Properties]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Properties - OPTIONAL, Array of workbook document properties 
;; 
;;REMARKS        Sets the document properties from an 8-element array 
;;		 0:	Title 
;;		 1:	Subject 
;;		 2:	Author 
;;		 3:	Keywords 
;;		 4:	Comments 
;; 
;;RETURNS        Workbook Object ID 
;; 
;;DEPENDENCIES   Excel, xlBookProperties() 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $WBo = xlNewBook($oXL, 4, $aryProp) 
; 
Function xlBookProperties($_ID, OPTIONAL $_aProp)
 
  Dim $BiDp
 
  $BiDp = ''
  If VarType($_aProp) > 8191	; write 
    ; Define optional properties from array 
    If $_aProp[0] $_ID.Title	= $_aProp[0] EndIf
    If $_aProp[1] $_ID.Subject	= $_aProp[1] EndIf
    If $_aProp[2] $_ID.Author	= $_aProp[2] EndIf
    If $_aProp[3] $_ID.Keywords	= $_aProp[3] EndIf
    If $_aProp[4] $_ID.Comments	= $_aProp[4] EndIf
  Else
    Dim $_aTmp[4]
    $_aTmp[0] = $_ID.Title
    $_aTmp[1] = $_ID.Subject
    $_aTmp[2] = $_ID.Author
    $_aTmp[3] = $_ID.Keywords
    $_aTmp[4] = $_ID.Comments
    $xlBookProperties = $_aTmp
  EndIf
 
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetAdd() 
;; 
;;ACTION         Adds a new worksheet to an Excel spreadsheet 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlSheetAdd(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 xlSheetAdd($_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
 
  $xlSheetAdd = $_ID.ActiveSheet.Name		; return the name of the new sheet 
  Exit $_Err
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetName() 
;; 
;;ACTION         Returns the name of the active sheet 
;;		 Sets the name of the active sheet 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlSheetName(Object_ID [, Name]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Name       - OPTIONAL, New sheet name 
;; 
;;REMARKS        If name is not specified, the name of the active sheet is returned. 
;;		 If the name is specified, that sheet is changed to the new name.  
;; 
;;RETURNS        Name of active sheet  
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $SheetName = xlSheetName($oXL)		; get current sheet name 
;;		 xlSheetName($0XL, 'NewSheetName')	; change it! 
; 
Function xlSheetName($_ID, OPTIONAL $_Name)
 
  Dim $_ASName
 
  ; Get the active sheet name 
  $_ASName = $_ID.ActiveSheet.Name
 
  ; If new name was not provided, return the Active Sheet Name 
  If $_Name = ''
    $xlSheetName = $_ASName
    Exit @ERROR
  EndIf
 
  ; Otherwise, Rename the active sheet 
  $xlSheetName = $_Name
  $_ID.Worksheets($_ASName).Name = $_Name
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetSelect() 
;; 
;;ACTION         Sets the active sheet 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlSheetSelect(Object_ID,  Sheet) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Sheet	    - REQUIRED, Sheet name or # to make active 
;; 
;;REMARKS        Used to select a worksheet 
;; 
;;RETURNS        1 if successful, 0 if not - sets ERROR macro 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       xlSheetSelect($0XL, "Sheet3")	; select the sheet 
; 
Function xlSheetSelect($_ID, $_Sheet)
 
  Dim $_
 
  $_ = $_ID.WorkSheets($_Sheet).Select
  $xlSheetSelect = Not @ERROR
  If @ERROR
    $_ = @ERROR
    $_ID.Err.Reset
    Exit $_
  EndIf
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetDelete() 
;; 
;;ACTION         Deletes the specified sheet 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlSheetDelete(Object_ID,  Sheet) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Sheet	    - REQUIRED, Sheet name or # to Delete 
;; 
;;REMARKS        Used to delete a worksheet 
;; 
;;RETURNS        1 if successful, 0 if not - sets ERROR macro 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       xlSheetDelete($0XL, "Sheet3")	; delete the sheet 
; 
Function xlSheetDelete($_ID, $_Sheet)
 
  Dim $_
 
  $_ = $_ID.WorkSheets($_Sheet).Delete
  $xlSheetDelete = Not @ERROR
  If @ERROR
    $_ = @ERROR
    $_ID.Err.Reset
    Exit $_
  EndIf
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetCount()  (originally xlNumSheets) 
;; 
;;ACTION         Return the number of sheets in teh current workbook 
;; 
;;AUTHOR         Glenn Barnas, based on code from Erik Kaerholm 
;; 
;;SYNTAX         xlSheetCount(Object_ID) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;; 
;;REMARKS         
;; 
;;RETURNS        Integer - count of sheets in workbook 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $NumSheets = xlSheetCount($oXL) 
; 
Function xlSheetCount($_ID)
 
  $xlSheetCount = $_ID.ActiveWorkbook.Sheets.Count
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlSheetPrint() / xlRangePrint 
;; 
;;ACTION         Prints the specified sheet or Range 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlSheetPrint(Object_ID [,  Sheet] [, Start] [, End] [, Copies] [, Printer]) 
;;SYNTAX         xlRangePrint(Object_ID, Range [,  Sheet] [, Start] [, End] [, Copies] [, Printer]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - REQUIRED (xlRangePrint only), Range to print 
;;		 Sheet	    - OPTIONAL, Sheet name or # to Print 
;;		 Start	    - OPTIONAL, Start page 
;;		 Emd	    - OPTIONAL, End page 
;;		 Copies	    - OPTIONAL, # of copies 
;;		 Printer    - OPTIONAL, Printer Name 
;; 
;;REMARKS        Prints the specified pages on the defined worksheet 
;;		 Prints the specified range of cells on the defined sheet 
;;		 xlRangePrint is a front-end for xlSheetPrint to permit range printing 
;; 
;;RETURNS        1 if successful, 0 if not - sets ERROR macro 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       xlSheetDelete($0XL, "Sheet3")	; delete the sheet 
; 
Function xlSheetPrint($_ID, OPTIONAL $_Sheet, $_SPage, $_EPage, $_Copies, $_Printer, $_Range)
 
  Dim $_
 
  $_Sheet  = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
  $_Copies = IIf(Val($_Copies) = 0, 1, Val($_Copies))
  $_SPage  = IIf(Val($_SPage)<1, 1, Val($_SPage))
  $_SPage  = IIf(Val($_SPage)>32767, 32767, Val($_SPage))
  $_EPage  = IIf(Val($_EPage)<1, 1, Val($_EPage))
  $_EPage  = IIf(Val($_EPage)>32767, 32767, Val($_EPage))
  $_Copies = IIf(Val($_Copies)<1, 1, Val($_Copies))
  $_Copies = IIf(Val($_Copies)>32767, 32767, Val($_Copies))
 
  If $_Range
    $_ = $_ID.WorkSheets($_Sheet).Range($_Range).PrintOut($_SPage, $_EPage, $_Copies, 0, $_Printer)
  Else
    $_ = $_ID.WorkSheets($_Sheet).PrintOut($_SPage, $_EPage, $_Copies, 0, $_Printer)
  EndIf
  Exit @ERROR
 
EndFunction
 
Function xlRangePrint($_ID, $_Range, OPTIONAL $_Sheet, $_SPage, $_EPage, $_Copies, $_Printer)
 
  $xlRangePrint = xlSheetPrint($_ID, $_Sheet, $_SPage, $_EPage, $_Copies, $_Printer, $_Range)
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeValue() 
;; 
;;ACTION         Reads / Writes a single cell or 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) 
;;			      When writing, only the first cell ID is necessary (A1) 
;;		 Value      - OPTIONAL, The value to place into the range 
;;			      Single cell - any string or numeric value 
;;			      Range - Array of Arrays ([Row Array][Col Array]) 
;;		 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. 
;; 
;;		 Single cell read/write is straightforward. Ranges require 
;;		 an Array of Arrays. When one or more ROWS of cells are 
;;		 to be written, an array of arrays is used, and the primary  
;;		 array holds arrays of column data for the specific row. 
;;		 IE: Array[ROW][COL] 
;; 
;;RETURNS        Value or Array containing the data from the defined cell range 
;;               Returns nothing on Write 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       READ: 
;;			$Val = xlRangeValue($XL, 'A1', , 'Sheet1') 
;;			$aData = xlRangeValue($XL, 'A1:F15', , 'Sheet1') 
;;		 WRITE: 
;;			; write a formula to cell A1 
;;			xlRangeValue($XL, 'A1', '=Sum(A2:a9)', 'Sheet1') 
;;			; write 2 rows of multiple cells 
;;			Dim $Data[1] 
;;			$aData[0] = 'a', 'b', 'c' 
;;			$aData[1] = 'd', 'e', 'f', 'g' 
;;			xlRangeValue($XL, 'A3', $aData, 'Sheet1') 
; 
Function xlRangeValue($_ID, $_Range, OPTIONAL $_Value, OPTIONAL $_Sheet)
 
  Dim $_, $_Rv 						; Temp vars 
  Dim $_X, $_Y, $_D					; index	vars 
  Dim $_aTmp						; temp array 
  Dim $_Range2, $_Range3[1]				; range arrays 
  Dim $_T1, $_T2
 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  If VarType($_Value) <> 0				; WRITE 
    If VarType($_Value) < 8192				; simple value 
      $_ID.WorkSheets($_Sheet).Range($_Range).Value = $_Value
    Else						; array 
      If UBound($_Value[0]) = -1 Exit 87 EndIf		; not array of arrays - exit! 
      ; Write the array of arrays one row at a time 
      $_Range = Split($_Range, ':')[0]			; get starting Col/Row 
      For $_D = 0 to UBound($_Value)			; enumerate rows 
        $_aTmp = $_Value[$_D]				; get row array 
        $_Range2 = xlColRow($_Range)			; get col/row values for starting cell 
        $_Range2[1] = Val($_Range2[1]) + $_D		; set ROW pointer A 
        $_Range3[1] = $_Range2[1] 			; set ROW pointer B 
        $_Range3[0] = xlCol(xlCol($_Range2[0]) + UBound($_aTmp))
        $_Rv = $_Range2[0] + $_Range2[1] + ':' + $_Range3[0] + $_Range3[1]
        ; write the range of cells for this row 
        $_ID.WorkSheets($_Sheet).Range($_Rv).Value = $_aTmp
      Next
    EndIf
  Else							; READ 
    If InStr($_Range, ':')				; read range 
      ; read the range one row at a time 
      $_Range2 = Split($_Range, ':')			; split range into start/end pairs 
      $_Range2[0] = xlColRow($_Range2[0])		; start range pair 
      $_Range2[1] = xlColRow($_Range2[1])		; end range pair 
      $_Range2[0][0] = xlCol($_Range2[0][0])		; start Col as # 
      $_Range2[1][0] = xlCol($_Range2[1][0])		; End Col as # 
      $_ = Val($_Range2[1][1]) - Val($_Range2[0][1])	; Get number of rows for array size 
      Dim $_aTmp[$_]					; define return array 
      $_Y = $_Range2[0][1]				; shortcut to row offset 
      For $_X = 0 to $_					; loop through # of rows 
        ; Build $_D into a CR:CR range 
        $_T1 = '' + xlCol($_Range2[0][0]) + CStr($_X + $_Y)
        $_T2 = '' + xlCol($_Range2[1][0]) + CStr($_X + $_Y)
        If $_T1 = $_T2                                  ; have a single column 
          ReDim $_[0]
          $_D = $_T1
          $_[0] = $_ID.WorkSheets($_Sheet).Range($_D).Value
        Else                                            ; have multiple columns 
          $_D = $_T1 + ':' + $_T2
          ; read the row of cells and convert to a normal array 
          $_ = Split(Join($_ID.WorkSheets($_Sheet).Range($_D).Value, Chr(31)), Chr(31))
        EndIf
        $_aTmp[$_X] = $_				; add this row array to the return array 
      Next	; row 
      $xlRangeValue = $_aTmp				; return array of arrays 
    Else						; read single cell 
      $xlRangeValue = $_ID.WorkSheets($_Sheet).Range($_Range).Value
    EndIf
  EndIf
 
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeClear() 
;; 
;;ACTION         Clears a range of cells - does not delete the cells 
;;		 See xlRangeDelete() to delete and shift the actual 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 
;;					Defaults to the active sheet 
;; 
;;REMARKS        Clears all content from the range of cells 
;; 
;;RETURNS        1 on Success, 0 on Failure 
;; 
;;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
 
  $xlRangeClear = Not @ERROR
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeDelete() 
;; 
;;ACTION         Deletes a range of cells 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlRangeDelete(Object_ID, Range [, Sheet] [, Direction]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - REQUIRED, A standard range specification (A1:C3) 
;;		 Sheet      - OPTIONAL, The worksheet name to reference 
;;					Defaults to the active sheet 
;;		 Direction  - OPTIONAL, Direction to fill deleted cells from 
;;                                      Default is up (0), alternate is left (1) 
;; 
;;REMARKS        Removes the range of cells, rows, or columns. 
;; 
;;RETURNS        1 on Success, 0 on Failure 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES        
; 
;;  
Function xlRangeDelete($_ID, $_Range, OPTIONAL $_Sheet, OPTIONAL $_DFlag)
 
  Dim $_
  Dim $_Dir
 
  ; get the current sheet name if not specified 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  ; allow "Left/Up" as DFlag values - default is Up (0) 
  $_DFlag = IIf(Left($_DFlag,1) = 'L', 1, Val($_DFlag))
 
  ; set the delete shift direction for cell deletes 
  $_Dir  = IIf($_DFlag, -4159, -4162)
 
  ; delete the the range of cells 
  $_ = $_ID.WorkSheets($_Sheet).Range($_Range).Delete($_Dir)
 
  $xlRangeDelete = Not @ERROR
  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        Nothing 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       xlRangeFormat($XL, 'A1', $aFmt, 'Sheet1') 
; 
Function xlRangeFormat($_ID, $_Range, $_Format, OPTIONAL $_Sheet)
 
  $_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 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeFormatNum() 
;; 
;;ACTION         Sets or returns the number format for a range of cells 
;; 
;;AUTHOR         Glenn Barnas, based on several UDFs from Erik Kaerholm 
;; 
;;SYNTAX         xlRangeFormatNum(Object_ID, Range, Format, Mask [, Sheet]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - REQUIRED, A standard range specification (A1:C3) 
;;		 Format     - REQUIRED, The format type, or "Query" to read 
;;		 Mask       - OPTIONAL, The format mask to apply 
;;					Each format has a default mask 
;;		 Sheet      - OPTIONAL, The worksheet name to read/write 
;; 
;;REMARKS        Defaults to the active sheet if not specified. Without specifying a  
;;		 format mask, can quickly apply default formats to cell ranges. Defaults 
;;		 are shown below, and can easily be customized. 
;; 
;;		 Format defaults are:	TYPE		MASK 
;;					Number		0.0000 
;;					Date		YYYY/MM/DD 
;;					Time		hh:mm:ss 
;;					DateTime	YYYY/MM/DD hh:mm:ss 
;;					Text		@ 
;;					Percent		0.00% 
;;					Currency	$#,##0.00_);[Red]($#,##0.00) 
;;					QUERY		Returns the current format mask 
;; 
;;		 Because each TYPE has its own optional format string, any TYPE can 
;;		 effectivly apply any type of format string - the types are mostly  
;;		 useful for applying default formatting. 
;; 
;;		 When using QUERY, only a single cell should be referenced, as  
;;		 the results of querying a range could be unpredictable. 
;; 
;;RETURNS        Nothing 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       ; Set A1 to 12-hour time format, no seconds 
;;		 xlRangeFormatNum($XL, 'A1', 'Time', 'h:mm AM/PM', 'Sheet1') 
;;		 ; set column C of the current sheet to percent, use default format mask 
;;		 xlRangeFormatNum($XL, 'C:C', 'Percent') 
; 
Function xlRangeFormatNum($_ID, $_Range, $_Format, OPTIONAL $_Mask, OPTIONAL $_Sheet)
 
  ; get the current sheet name, if not defined 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  Select
   Case Left($_Format, 1) = 'Q'			; QUERY 
    ; Query the current format string 
    $_ID.WorkSheets($_Sheet).Range($_Range).NumberFormat
    Exit @ERROR
 
   Case Left($_Format, 1) = 'N'			; number 
    ; Number - default to 4 decimals, no thousands 
    $_Mask = IIf($_Mask, $_Mask, '0.0000')
 
   Case Left($_Format, 1) = 'D'			; Date or DateTime 
    ; Date - default to yyyy/mm/dd format 
    If InStr($_Format, 'Time')
      $_Mask = IIf($_Mask, $_Mask, 'yyyy/mm/dd hh:mm:ss')
    Else
      $_Mask = IIf($_Mask, $_Mask, 'yyyy/mm/dd')
    EndIf
 
   Case Left($_Format, 2) = 'Ti'		; Time 
    ; Time - default to hh:mm:ss 
    $_Mask = IIf($_Mask, $_Mask, 'hh:mm:ss')
 
   Case Left($_Format, 2) = 'Te'		; Text 
    ; Text 
    $_Mask = IIf($_Mask, $_Mask, Chr(64))
 
   Case Left($_Format, 1) = 'P'			; Percent 
    ; Percent - default to two decimals 
    $_Mask = IIf($_Mask, $_Mask, '0.00%')
 
   Case Left($_Format, 1) = 'C'			; Currency 
    ; Default is 2 decimals, Dollars, Thousands separater, negatives in RED 
;KGEN:IGNORENEXT 
    $_Mask = IIf($_Mask, $_Mask, '$#,##0.00_);[Red]($#,##0.00)')
  EndSelect
 
  ; requested or default mask is now defined - apply the format mask to the range 
  $_ID.WorkSheets($_Sheet).Range($_Range).NumberFormat = $_Mask
 
  $xlRangeFormatNum = Not @ERROR
  Exit @ERROR
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeFreeze() 
;; 
;;ACTION         Sets format info for a range of cells 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlRangeFreeze(Object_ID, Range [, Sheet]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - OPTIONAL, A standard Cell specification (B2) 
;;		 Sheet      - OPTIONAL, The worksheet name to read/write 
;; 
;;REMARKS        Freezes the display pane at the referenced cell. If the range is not  
;;		 defined, the Pane is un-frozen 
;; 
;;RETURNS        1 if successful, 0 if not, sets exit code to Error 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       xlRangeFreeze($XL, 'C1', 'Sheet1') 
; 
Function xlRangeFreeze($_ID, OPTIONAL $_Range, OPTIONAL $_Sheet)
 
  Dim $_, $_Err
 
  ; get the current sheet name if not specified 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  ; make sure range is a single cell 
  If InStr($_Range, ':') $_Range = Split($_Range, ':')[0] EndIF
  If $_Range	; freeze 
    $_ = $_ID.WorkSheets($_Sheet).Range($_Range).Select
    $_ID.ActiveWindow.FreezePanes = -1
    $_Err = @ERROR
    ; return status 
    $xlRangeFreeze = $_ID.ActiveWindow.FreezePanes
  Else		; unfreeze 
    $_ = $_ID.WorkSheets($_Sheet).Range("A1").Select
    $_ID.ActiveWindow.FreezePanes = 0
    $_Err = @ERROR
    ; return status 
    $xlRangeFreeze = Not $_ID.ActiveWindow.FreezePanes
  EndIf
 
  Exit $_Err
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeSearch() 
;; 
;;ACTION         Searches a range of cells for a value 
;; 
;;AUTHOR         Glenn Barnas, based on code by Erik Kaerholm 
;; 
;;SYNTAX         xlRangeSearch(Object_ID, Range, Value [, Sheet]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - REQUIRED, A standard range specification (A1:C3) 
;;		 Value      - REQUIRED, A value to search for 
;;		 Sheet      - OPTIONAL, The worksheet name to reference 
;;					Defaults to the active sheet 
;; 
;;REMARKS        Searches the range of cells for a value 
;; 
;;RETURNS        Cell reference where found, or 0 if Not Found 
;;		 Only returns the first location where found 
;;		 Searches ranges Left to Right, then down 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES        
; 
;;  
Function xlRangeSearch($_ID, $_Range, $_Value, OPTIONAL $_Sheet)
 
  Dim $_, $_Z						; temp vars 
  Dim $_Range2						; Range Index 
  Dim $_X, $_Y, $_D					; Index vars 
 
  ; get the current sheet name if not specified 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  If InStr($_Range, ':')				; read range 
    ; read the range one row at a time 
    $_Range2 = Split($_Range, ':')			; split range into start/end pairs 
    $_Range2[0] = xlColRow($_Range2[0])			; start range pair 
    $_Range2[1] = xlColRow($_Range2[1])			; end range pair 
    $_Range2[0][0] = xlCol($_Range2[0][0])		; start Col as # 
    $_Range2[1][0] = xlCol($_Range2[1][0])		; End Col as # 
    $_ = Val($_Range2[1][1]) - Val($_Range2[0][1])	; Get number of rows for array size 
    $_Y = $_Range2[0][1]				; shortcut to row offset 
    For $_X = 0 to $_					; loop through # of rows 
      ; Build $_D into a CR:CR range 
      $_D = '' + xlCol($_Range2[0][0]) + CStr($_X + $_Y) + ':'
      $_D = $_D + xlCol($_Range2[1][0]) + CStr($_X + $_Y)
      ; read the row of cells and convert to a normal array 
      $_ = Split(Join($_ID.WorkSheets($_Sheet).Range($_D).Value, Chr(31)), Chr(31))
      $_Z = AScan($_, $_Value)
      If $_Z >= 0					; found it! 
        $xlRangeSearch = '' + xlCol($_Range2[0][0] + $_Z) + CStr($_X + $_Y)
        Exit 0
      EndIf
    Next	; row 
  Else
    Exit 87						; did not specify a range 
  EndIf
 
  $xlRangeSearch = 0
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlRangeSort() 
;; 
;;ACTION         Sorts a range of cells 
;; 
;;AUTHOR         Erik Kaerholm, adapted by Glenn Barnas 
;; 
;;SYNTAX         xlRangeSort(Object_ID, Range, C1 [, D1] [, C2] [, D2] [, C3] [, D3] [, HasHdr] [, Sheet]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Range      - REQUIRED, A standard range specification (A1:C3) 
;;		 C1         - REQUIRED, Column in the array to sort 
;;		 D1	    - OPTIONAL, Direction to sort - Ascending or Descending 
;;		 C2, C3	    - OPTIONAL, Additional columns to sort by 
;;		 D2, D3	    - OPTIONAL, Direction to sort additional columns by 
;;		 HasHdr     - OPTIONAL, Boolean indicating that first row of range is a header (default=none) 
;;		 Sheet      - OPTIONAL, The worksheet name to reference 
;;					Defaults to the active sheet 
;; 
;;REMARKS        Searches the range of cells for a value 
;; 
;;RETURNS        1 if successful, 0 if failure, sets error status on exit 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES       $ = xlRangeSort($oXL, 'A2:F30', 'B:B') 
;; 
; Sort syntax  
; Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) 
; 
Function xlRangeSort($_ID, $_Range, $_C1, OPTIONAL $_D1, $_C2, $_D2, $_C3, $_D3, $_HdrFlag, $_Sheet)
 
  Dim $_					; temp var 
  Dim $_ExC					; Execute Command 
  Dim $_ExRv                                    ; Return Value from Execute 
 
  ; get the current sheet name if not specified 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  ; Verify we have a range, otherwise exit with error 
  If Not InStr($_Range, ':') Exit 87 EndIf
 
  ; Validate the parameters 
  $_HdrFlag = IIF(Val($_HdrFlag) = 0 Or Val($_HdrFlag) = 1, Val($_HdrFlag), 0)
  $_D1 = xlSortDir($_D1)
  ; Set D2 and D3 only if C2 and C3 have data 
  If $_C2
    $_D2 = xlSortDir($_D2)
  Else
    $_D2 = ''
  EndIf
  If $_C3
    $_D3 = xlSortDir($_D3)
  Else
    $_D3 = ''
  EndIf
 
  ; Build up the execute string with the parameters that were supplied... 
  $_ExC = Chr(36) + '_ExRv = ' + Chr(36) + '_ID.WorkSheets("' 
  $_ExC = $_ExC + $_Sheet + '").Range("' + $_Range + '").Sort('
  $_ExC = $_ExC + Chr(36) + '_ID.WorkSheets("' + $_Sheet + '").Range("' + $_C1 + '"),' + $_D1 + ','
 
  ; Add parameters for second column 
  If $_C2
   $_ExC = $_ExC + Chr(36) + '_ID.WorkSheets("' + $_Sheet + '").Range("' + $_C2 + '"),,' + $_D2 + ','
  Else
    $_ExC = $_ExC + ',,,'
  EndIf
 
  ; Add parameters for third column 
  If $_C3
   $_ExC = $_ExC + Chr(36) + '_ID.WorkSheets("' + $_Sheet + '").Range("' + $_C3 + '"),' + $_D3 + ','
  Else
    $_ExC = $_ExC + ',,'
  EndIf
 
  ; Add the "Has Header" flag 
  $_ExC = $_ExC + $_HdrFlag + ')'
 
  ; Future enhancements could add parameters for case sensitivity and orientation... 
 
  ; execute the string 
  $_ = Execute($_ExC)
 
  ; return status and exit with the error code from the executeed sort 
  $xlRangeSort = Not @ERROR
  Exit @ERROR
 
EndFunction
 
 
; supporting function to check/set the sort directionality values 
; Accepts null, 1/2, and Ascending/Descending as parameters 
; Returns 1 or 2 for Ascending of Descending 
Function xlSortDir($_Dir)
 
  If Not $_Dir					; null value? set default Ascending (1) 
    $xlSortDir = 1
    Exit 0
  EndIf
 
  $_Dir = InStr('12 AD ', Left($_Dir, 1)) Mod 3	; return 1 or 2 for Ascending or Descending 
 
  ; If Dir is 0, bad parameter was given, either set default OR exit with error 
  ; My choice is to return a default value of Ascending 
  If $_Dir = 0					; invalid parameter 
    $_Dir = 1					; set default		<- PICK 
    ; Exit 87					; return failure	<- ONE! 
  EndIf
 
  $xlSortDir = $_Dir				; return the conditioned value   
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlPageSetup() 
;; 
;;ACTION         Sets/Queries page format and printing settings 
;; 
;;AUTHOR         Glenn Barnas, based on individual functions from Erik Kaerholm 
;; 
;;SYNTAX         xlPageSetup(Object_ID, Setting [, Data1] [,Data2] [, Sheet]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;;		 Setting    - REQUIRED, The setting identifier 
;;		 Data1      - OPTIONAL, Data or sub-value 
;;		 Data2      - OPTIONAL, Data when Data1 is a sub-value 
;;		 Sheet      - OPTIONAL, The worksheet name to read/write 
;; 
;;REMARKS        Defaults to the active sheet if not specified.  
;;		 Setting values are: 
;;		   Setting	Data1 Values	Data2 Values 
;;		   Orientation	P, L 
;;			Sets page orientation to Portriat or Landscape 
;;		   Margin	L,R,T,B,H,F	###[i|c] (inches or cm, default is inches) 
;;			Sets margin to value, Left/Right/Top/Bottom/Header/Footer 
;;			Value can be Inches or Cm. I or C alone in value returns 
;;			setting in that measurement. 
;;		   Header	L, C, R		String 
;;		   Footer	L, C, R		String 
;;			Define page header/footer, Left/Center/Right. Supports macros below 
;;		   Gridlines	on,off 
;;			Enable/disable printing of gridlines 
;;		   VCenter	on,off 
;;		   HCenter	on,off 
;;			Vertically/horizontally center output on page 
;;		   TitleRows	StartRow	EndRow (end defaults to start if null) 
;;			Define title rows to repeat on each page 
;; 
;; 		 Macro codes for use in Headers & Footers 
;; 		   && 		Write a single &-character 
;; 		   &"Fontname" 	Font name. Must be in double quotes! 
;; 		   &nn 		Font size. Must be 2 digits long - Eg. fontsize 8 is &08 
;; 		   &B  		Bold toggle 
;; 		   &I  		Italic toggle 
;; 		   &U  		Underline toggle 
;; 		   &D  		Date 
;; 		   &T  		Time 
;; 		   &F  		Document Name 
;; 		   &P  		Page Number 
;; 		   &N  		Number of pages 
;; 
;; Example ("Center footer: Page ? of ?? (Font Arial fontsize 8)): 
;; xlCenterFooter('&"Arial"&08Page &P of &N') 
;; 
;;RETURNS        Variant containing the data from the defined cell range 
;; 
;;DEPENDENCIES   Excel 
;; 
;;TESTED WITH    W2K, WXP, W2K3, Office XP, Office 2K3 
;; 
;;EXAMPLES        
; 
Function xlPageSetup($_ID, $_Setting, OPTIONAL $_D1, OPTIONAL $_D2, OPTIONAL $_Sheet)
 
  Dim $_					; temp var 
  Dim $_Uv, $_U					; Unit Values, Selection 
  Dim $_Mgn					; Margin name string 
  Dim $_Exp, $_ERv				; Expression to Execute, Return Val 
  
 
  $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
 
  Select
   Case $_Setting = 'Orientation'		; Page Orientation 
    If $_D1	; write 
      $_ = IIf($_D1 = 'L', 2, 1)		; set orientation value 
      $_ID.WorkSheets($_Sheet).PageSetup.Orientation = $_
    Else	; read 
      $_ = $_ID.WorkSheets($_Sheet).PageSetup.Orientation
      $xlPageSetup = SubStr('PL', $_, 1)
    EndIf
 
   Case $_Setting = 'Margin'			; Page Margin Settings 
    ; Default measurement format is listed first - edit as necessary for your locale 
    $_Uv = 'Inches', 'Centimeters' 
    $_U = IIf(Right($_D2, 1) = Left($_Uv[1], 1), 1, 0)
    ; If D2 contains only "I" or "C", clear it and do a READ operation 
    If InStr('~I~C~', '~' + UCase($_D2) + '~') $_D2 = '' EndIf
 
    $_ = InStr('LRTBHF', Left($_D1, 1))		; get the header ID 
    If $_ = 0 Exit 87 EndIf			; exit if not valid 
 
    ; get the proper margin name 
    $_Mgn = Trim(SubStr('Left  Right Top   BottomHeaderFooter', (($_ - 1) * 6) + 1, 6))
 
    ; build the execute expression 
;KGEN:IGNORENEXT 
    $_Exp = Chr(36) + '_ID.WorkSheets($_Sheet).PageSetup.' + $_Mgn + 'Margin'
    ; Execute the expression for write or read 
    If $_D2	; write 
      $_Exp = $_Exp + ' = ' + Chr(36) + '_ID.' + $_Uv[$_U] + 'ToPoints(' + $_D2 + ')'
      $_ = Execute($_Exp)
    Else	; read 
      $_Exp = Chr(36) + '_ERv = ' + $_Exp	; Add the result var 
      $_ = Execute($_Exp)
      $_ = $_ERv / 72				; convert resulting Points to Inches 
      If Left($_Uv[$_U], 1) = 'c'		; convert inches to CM if defined 
        $_ = $_ * 2.54
      EndIf
      $xlPageSetup = $_				; set the value to return 
    EndIf
 
   Case $_Setting = 'Gridlines'			; Gridlines on/off 
    If $_D1	; write 
      If Not InStr('On Off', $_D1) Exit 87 EndIf
      $_ = IIf($_D1 = 'Off', 0, -1)		; set on/off value 
      $_ID.WorkSheets($_Sheet).PageSetup.PrintGridLines = $_
    Else	; read 
      $_ = Abs($_ID.WorkSheets($_Sheet).PageSetup.PrintGridLines)
      $xlPageSetup = SubStr('OffOn ', $_ * 3 + 1, 3)
    EndIf
 
   Case $_Setting = 'VCenter'			; Center page vertically 
    If $_D1	; write 
      If Not InStr('On Off', $_D1) Exit 87 EndIf
      $_ = IIf($_D1 = 'Off', 0, -1)		; set on/off value 
      $_ID.WorkSheets($_Sheet).PageSetup.CenterVertically = $_
    Else	; read 
      $_ = Abs($_ID.WorkSheets($_Sheet).PageSetup.CenterVertically)
      $xlPageSetup = SubStr('OffOn ', $_ * 3 + 1, 3)
    EndIf
 
   Case $_Setting = 'HCenter'			; Center page horizontally 
    If $_D1	; write 
      If Not InStr('On Off', $_D1) Exit 87 EndIf
      $_ = IIf($_D1 = 'Off', 0, -1)		; set on/off value 
      $_ID.WorkSheets($_Sheet).PageSetup.CenterHorizontally = $_
    Else	; read 
      $_ = Abs($_ID.WorkSheets($_Sheet).PageSetup.CenterHorizontally)
      $xlPageSetup = SubStr('OffOn ', $_ * 3 + 1, 3)
    EndIf
 
   Case $_Setting = 'Header'			; page header 
    $_ = InStr('LCR', Left($_D1, 1))		; get the header ID 
    If $_ = 0 Exit 87 EndIf			; exit if not valid 
 
    ; get the proper header name 
    $_Mgn = Trim(SubStr('Left  CenterRight', (($_ - 1) * 6) + 1, 6))
;KGEN:IGNORENEXT 
    $_Exp = Chr(36) + '_ID.WorkSheets($_Sheet).PageSetup.' + $_Mgn + 'Header'
    If $_D2	; write 
      $_Exp = $_Exp + ' = "' + $_D2 + '"'
      $_ = Execute($_Exp)
    Else	; read 
      $_Exp = Chr(36) + '_ERv = ' + $_Exp	; Add the result var 
      $_ = Execute($_Exp)
      $xlPageSetup = $_ERv			; set the value to return 
    EndIf
 
   Case $_Setting = 'Footer'			; page footer 
    $_ = InStr('LCR', Left($_D1, 1))		; get the footer ID 
    If $_ = 0 Exit 87 EndIf			; exit if not valid 
 
    ; get the proper footer name 
    $_Mgn = Trim(SubStr('Left  CenterRight', (($_ - 1) * 6) + 1, 6))
;KGEN:IGNORENEXT 
    $_Exp = Chr(36) + '_ID.WorkSheets($_Sheet).PageSetup.' + $_Mgn + 'Footer'
    If $_D2	; write 
      $_Exp = $_Exp + ' = "' + $_D2 + '"'
      $_ = Execute($_Exp)
    Else	; read 
      $_Exp = Chr(36) + '_ERv = ' + $_Exp	; Add the result var 
      $_ = Execute($_Exp)
      $xlPageSetup = $_ERv			; set the value to return 
    EndIf
 
   Case $_Setting = 'TitleRows'		; Define title rows to repeat on each page 
    If $_D1	; write 
      If Not $_D2 $_D2 = $_D1 EndIf		; end row defaults to start if not defined 
      $_ = Chr(36) + $_D1 + ':' + Chr(36) + $_D2
      $_ID.WorkSheets($_Sheet).PageSetup.PrintTitleRows = $_
    Else	; read 
      $xlPageSetup = $_ID.WorkSheets($_Sheet).PageSetup.PrintTitleRows      
    EndIf
 
;   Case $_Setting = ''				; spare 
 
   Case $_Setting = 1				; bad setting value 
    Exit 87
 
  EndSelect
 
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlWindow() 
;; 
;;ACTION         Show/Hide the Excel application window 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlWindow(Object_ID, [state]) 
;; 
;;PARAMETERS     Object_ID  - REQUIRED, Excel Application Object pointer 
;; 
;;		 State	    - OPTIONAL, "show" or "hide" to control the window. If not 
;;			      defined, returns the current window state. 
;; 
;;REMARKS        Used to control the visibility of the Excel application window. 
;; 
;;RETURNS        State - 1 if Visible, 0 if hidden 
;; 
;;DEPENDENCIES   None 
;; 
;;TESTED WITH    W2K, WXP, W2K3 
;; 
;;EXAMPLES       If Not xlWindow($oXL)  
;;		   $ = xlWindow($oXL, 'show') 
;;		 EndIf 
; 
Function xlWindow($_ID, OPTIONAL $_State)
 
  Dim $_					; temp var 
 
  If $_State	; write 
    $_ = InStr('SH', Left($_State, 1))		; can be Show or Hide 
    If $_
      $_ID.Visible = $_ - 2			; change the state 
    Else
      Exit 87					; bad value - exit 
    EndIf
  EndIf
 
  $xlWindow = $_ID.Visible			; return current state 
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlCol() 
;; 
;;ACTION         Utility function to translate between numeric and alpha column IDs 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlCol(ColumnID) 
;; 
;;PARAMETERS     ColumnID  - REQUIRED, A column reference as a number or letter 
;; 
;;REMARKS        Used in column ID calculations. By translating "Z" to 26, then adding 1, 
;;		 returning 27 to the function will yeild "AA", the next column ID. With this, 
;;		 you can calculate the end column ID of a range 41 columns wide starting at 
;;		 column "Q", for example. 
;; 
;;RETURNS        Given a number, returns the alpha column ID 
;;		 Given an alpha column ID, returns a number 
;; 
;;DEPENDENCIES   None 
;; 
;;TESTED WITH    W2K, WXP, W2K3 
;; 
;;EXAMPLES       $ColID = "AZ" 
;;		 $NextCol = xlCol(xlCol($ColID) + 1) ; get the next column ID 
; 
Function xlCol($ColID)
 
  If $ColID = Int($ColID)	; got number, need alpha reference 
    $ColID = $ColID - 1
    If $ColID > 25 $xlCol = Chr(Int($ColID / 26)+64) EndIf
    $xlCol = $xlCol + Chr($ColID Mod 26+65)
  Else				; got alpha, need number 
    $ColID = UCase($ColID)
    If Len($ColID) = 1
      $xlCol = Asc($ColID) - 64
    Else
      $xlCol = (Asc(Left($ColID, 1)) - 64) * 26 + Asc(Right($ColID, 1)) - 64
   EndIf
  EndIf
 
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlColRow() 
;; 
;;ACTION         Utility function to separate a cell reference (A1) into col/row parts (A,1) 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlColRow(CellRef) 
;; 
;;PARAMETERS     CellRef  - REQUIRED, A cell reference (a1) 
;; 
;;REMARKS        Returns a 2-element array containing the column and row labels for the  
;;		 specified cell reference. Only cells can be specified, not ranges 
;; 
;;RETURNS        Array, 2 elements, Column ID, Row ID 
;; 
;;DEPENDENCIES   None 
;; 
;;TESTED WITH    W2K, WXP, W2K3 
;; 
;;EXAMPLES       $aRC = xlColRow("AR243") 
; 
Function xlColRow($CellRef)
 
  Dim $_Row, $_Col		; row/col collection vars 
  Dim $_I, $_C			; index counter, working char 
 
  ; insure we don't have a null string 
  If Len($CellRef) = 0 Exit 87 EndIf
  ; enumerate the characters in the cell reference 
  For $_I = 1 to Len($CellRef)
    $_C = UCase(SubStr($CellRef, $_I, 1))
    Select
      ; valid column ID character? 
      Case InStr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', $_C)
        $_Col = $_Col + $_C
      ; valid row ID character? 
      Case InStr('0123456789', $_C)
        $_Row = $_Row + CStr($_C)
      ; bad data! 
      Case 1
        Exit 87
    EndSelect
  Next
 
  ; validate data 
  If $_Row = '' Or $_Col = '' Exit 87 EndIf
 
  $xlColRow = $_Col, $_Row	; return the array 
  Exit 0
 
EndFunction
 
 
 
;; 
;;====================================================================== 
;; 
;;FUNCTION       xlColor() 
;; 
;;ACTION         Utility function to translate between numeric and alpha color IDs 
;; 
;;AUTHOR         Glenn Barnas 
;; 
;;SYNTAX         xlColor(Color [, IdxFlag]) 
;; 
;;PARAMETERS     Color   - REQUIRED, A color index number or name 
;; 
;;		 IdxFlag - OPTIONAL, Forces use of Color_## format for indexes 
;;				     above 8. 
;; 
;;REMARKS        Used to translate color names to index values and vice-versa 
;;		 Per MS, Excel only officially recognizes the first 8 color names, 
;;		 Black thru Turquoise. Based on my research, colors 17-32 do not 
;;		 have "official" names and are known simply by Color_##. The MS 
;;		 documentation uses Color_9 thru Color_56 for the remaining colors. 
;;		 The array uses 2 special values - 0 to represent "none" and 57 
;;		 to represent "Auto" - only the color names "None" and "Auto" are 
;;		 returned, since "Color_0" and "Color_57" are illegal. The proper  
;;		 index values of -4142 and -4105 are returned for these names. 
;; 
;;RETURNS        Given an index number, returns the color name 
;;		 Given a color name, returns the index number 
;; 
;;DEPENDENCIES   None 
;; 
;;TESTED WITH    W2K, WXP, W2K3 
;; 
;;EXAMPLES       $ColID = "AZ" 
;;		 $NextCol = xlCol(xlCol($ColID) + 1) ; get the next column ID 
; 
Function xlColor($_Color, OPTIONAL $_IdxFlag)
 
  Dim $_					; temp var 
  Dim $_aCIndex					; color index array 
 
  $_ = 'None,Black,White,Red,BrightGreen,Blue,Yellow,Pink,Turquoise,'
  $_ = $_ + 'DarkRed,Green,DarkBlue,DarkYellow,Violet,Teal,Gray-25%%,Gray-50%%,'
  $_ = $_ + 'Color_17,Color_18,Color_19,Color_20,Color_21,Color_22,Color_23,Color_24,'
  $_ = $_ + 'Color_25,Color_26,Color_27,Color_28,Color_29,Color_30,Color_31,Color_32,'
  $_ = $_ + 'SkyBlue,LightTurquoise,LightGreen,LightYellow,PaleBlue,Rose,Lavender,Tan,'
  $_ = $_ + 'LightBlue,Aqua,Lime,Gold,LightOrange,Orange,Blue-Gray,Gray-40%%,'
  $_ = $_ + 'DarkTeal,SeaGreen,DarkGreen,OliveGreen,Brown,Plum,Indigo,Gray-80%%,Auto'
  $_aCIndex = Split($_, ',')
 
  If $_Color = Int($_Color)			; got index, return name 
    If $_Color > 56 Exit 87 EndIf		; Index out of range - Error! 
    If $_Color = 0 Exit 87 EndIf		; Index out of range - Error! 
    If $_Color = -4142 $_Color = 0 EndIf	; Translate "None" index 
    If $_Color = -4105 $_Color = 57 EndIf	; Translate "Auto" index 
    If $_Color < 0 Exit 87 EndIf		; Index out of range - Error! 
    If $_IdxFlag And Val($_Color) > 8 And Val($_Color) < 57
      $xlColor = 'Color_' + CStr($_Color)
    Else
      $xlColor = $_aCIndex[$_Color]
    EndIf
  Else						; got name, return Index 
    $xlColor = AScan($_aCIndex, $_Color)
    If $xlColor = 0 $xlColor = -4142 EndIf	; Translate "None" index 
    If $xlColor = 57 $xlColor = -4105 EndIf	; Translate "Auto" index 
    ; Translate "Auto" to proper index using Color_## format, return ## if the 
    ; name wasn't found - Auto & first 8 names are allowed  
    If ($_IdxFlag and $xlColor = -1) Or $xlColor = -1
      $xlColor = Split($_Color, '_')[1]
      If $xlColor < 0 Or $xlColor > 56 Exit 87 EndIf
    EndIf
  EndIf
 
  Exit 0
  
EndFunction