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