;; 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
