Stanley Lim

Hi Pple, Here I come again to report another bug(I think)... :"> Please kindly ignore me if this has already been reported or it is intended to be implemented this way. For the function, _ExcelSheetUsedRangeGet(), I notice that the last element of the return array is always zero. Example, when I do a _ArrayDisplay() I'll get the following: [0] = D20461 [1] = R20461C4 [3] = 4 [4] = 0 Orginal Code: ;=============================================================================== ; ; Description: Return the last cell of the used range in the specified worksheet. ; Syntax: $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $vSheet - The sheet name or number to be checked. ; Requirement(s): None ; Return Value(s): On Success - Returns an array of used range values: ; $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank) ; $array[1] - The last cell used, in R1C1 format ; $array[2] - The last column used, as an integer ; $array[3] - The last row used, as an integer ; On Failure - Returns 0 (as non-array numeric value) and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Invalid sheet number ; @error=3 - Invalid sheet name ; Author(s): DaLiMan, SEO <locodarwin at yahoo dot com> ; Note(s): Upon return, $array[0] will equal numeric value 0 if the worksheet is blank ; ;=============================================================================== Func _ExcelSheetUsedRangeGet($oExcel, $vSheet) Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0 If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If IsNumber($vSheet) Then If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0) Else $aSheetList = _ExcelSheetList($oExcel) For $xx = 1 To $aSheetList[0] If $aSheetList[$xx] = $vSheet Then $fFound = 1 Next If NOT $fFound Then Return SetError(3, 0, 0) EndIf $oExcel.ActiveWorkbook.Sheets($vSheet).Select $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) $aSendBack[0] = StringReplace($aSendBack[0], "$", "") $sTemp = StringSplit($aSendBack[1], "C") $aSendBack[2] = Number($sTemp[2]) $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "\a", "")) If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0 Return $aSendBack EndFunc ;==>_ExcelSheetUsedRangeGet oÝ÷ Ø*+­ç-y×(u殶­s` £³ÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓУ°£²FW67&Föã¢&WGW&âFRÆ7B6VÆÂöbFRW6VB&ævRâFR7V6fVBv÷&·6VWBࣲ7çF¢b33c¶'&ÒôW6VÅ6VWEW6VE&ævTvWBb33c¶ôW6VÂÂb33c·e6VWB£²&ÖWFW"2¢b33c¶ôW6VÂÒâW6VÂö&¦V7B÷VæVB'&V6VFær6ÆÂFòôW6VÄ&öö´÷Vâ÷"ôW6VÄ&öö´æWr£²b33c·e6VWBÒFR6VWBæÖR÷"çVÖ&W"Fò&R6V6¶VBࣲ&WV&VÖVçB2¢æöæP£²&WGW&âfÇVR2¢öâ7V66W72Ò&WGW&ç2â'&öbW6VB&ævRfÇVW3 £°b33c¶'&³ÒÒFRÆ7B6VÆÂW6VBÂâf÷&ÖBb2&WGW&æVBÂv÷&·6VWB2&Ææ²£°b33c¶'&³ÒÒFRÆ7B6VÆÂW6VBÂâ#3f÷&Ö@£°b33c¶'&³%ÒÒFRÆ7B6öÇVÖâW6VBÂ2âçFVvW £°b33c¶'&³5ÒÒFRÆ7B&÷rW6VBÂ2âçFVvW £²öâfÇW&RÒ&WGW&ç22æöâÖ'&çVÖW&2fÇVRæB6WG2W'&÷"öâW'&÷'3 £²W'&÷#ÓÒ7V6fVBö&¦V7BFöW2æ÷BW7@£²W'&÷#Ó"ÒçfÆB6VWBçVÖ&W £²W'&÷#Ó2ÒçfÆB6VWBæÖP£²WF÷"2¢FÆÖâÂ4TòfÇC¶Æö6öF'vâBöòF÷B6öÒfwC°£²æ÷FR2¢Wöâ&WGW&âÂb33c¶'&³ÒvÆÂWVÂçVÖW&2fÇVRbFRv÷&·6VWB2&Ææ°£°£³ÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓФgVæ2ôW6VÅ6VWEW6VE&ævTvWBb33c¶ôW6VÂÂb33c·e6VWB¢Æö6Âb33c¶6VæD&6µ³EÒÂb33c·5FV×Âb33c¶6VWDÆ7BÂb33c¶df÷VæBÒ¢bäõB4ö&¢b33c¶ôW6VÂFVâ&WGW&â6WDW'&÷" b4çVÖ&W"b33c·e6VWBFVà bb33c¶ôW6VÂä7FfUv÷&¶&öö²å6VWG2ä6÷VçBfÇC²b33c·e6VWBFVâ&WGW&â6WDW'&÷"" VÇ6P b33c¶6VWDÆ7BÒôW6VÅ6VWDÆ7Bb33c¶ôW6V f÷"b33c·ÒFòb33c¶6VWDÆ7E³Ð bb33c¶6VWDÆ7E²b33c·ÒÒb33c·e6VWBFVâb33c¶df÷VæBÒ æW@ bäõBb33c¶df÷VæBFVâ&WGW&â6WDW'&÷"2 VæD` b33c¶ôW6VÂä7FfUv÷&¶&öö²å6VWG2b33c·e6VWBå6VÆV7@ b33c¶6VæD&6µ³ÒÒb33c¶ôW6VÂäÆ6Föâå6VÆV7Föâå7V6Ä6VÆÇ2b33c·Ä6VÆÅGTÆ7D6VÆÂäFG&W70¢b33c¶6VæD&6µ³ÒÒb33c¶ôW6VÂäÆ6Föâå6VÆV7Föâå7V6Ä6VÆÇ2b33c·Ä6VÆÅGTÆ7D6VÆÂäFG&W72G'VRÂG'VRÂb33c·Å#3 b33c¶6VæD&6µ³ÒÒ7G&æu&WÆ6Rb33c¶6VæD&6µ³ÒÂgV÷C²b33c²gV÷C²ÂgV÷C²gV÷C² b33c·5FV×Ò7G&æu7ÆBb33c¶6VæD&6µ³ÒÂgV÷C´2gV÷C² b33c¶6VæD&6µ³%ÒÒçVÖ&W"b33c·5FV׳%Ò b33c¶6VæD&6µ³5ÒÒçVÖ&W"7G&æu&VtW&WÆ6Rb33c¶6VæD&6µ³ÒÂgV÷CµµãÓÒgV÷C²ÂgV÷C²gV÷C² bb33c¶6VæD&6µ³ÒÒgV÷C´gV÷C²æBb33c¶ôW6VÂä7FfW6VWBå&ævRb33c¶6VæD&6µ³ÒåfÇVRÒgV÷C²gV÷C²FVâb33c¶6VæD&6µ³ÒÒ¢&WGW&âb33c¶6VæD&6°¤VæDgVæ0³ÓÒfwCµôW6VÅ6VWEW6VE&ævTvW@ oÝ÷ Ù8^)Þ!ÈZjëh×6$aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "[^0-9]", "")) Hope this helps. Thanks again for his excellent UDF! I simply love it!

@Locodarwin: Thanks for the clarification here. Personally I stumble upon this logic error when I'm debugging my script. Basically, I set my script to run and populate all the data in Excel but not to save it as I don't to overwrite the Excel file that is already on the harddisk. Thanks and keepup the excellent work!

Hi pple, Please kindly ignore me if this has already been reported... :"> I have found a bug in the _ExcelBookClose function. The bug will cause Excel message alerts to be displayed no matter what value is set to the parameter $fAlerts Orginal Code: ;=============================================================================== ; ; Description: Closes the active workbook and removes the specified Excel object. ; Syntax: _ExcelCloseDoc($oExcel, $fSave = 1, $fAlerts = 0) ; Parameter(s): $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $fSave - Flag for saving the file before closing (0=no save, 1=save) ; $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) ; Requirement(s): None ; Return Value(s): On Success - Returns 1 ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - File exists, overwrite flag not set ; Author(s): SEO <locodarwin at yahoo dot com> ; Note(s): None ; ;=============================================================================== Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $fSave > 1 Then $fSave = 1 If $fSave < 0 Then $fSave = 0 If $fAlerts > 1 Then $fAlerts = 1 If $fAlerts < 0 Then $fAlerts = 0 $oExcel.Application.DisplayAlerts = $fAlerts $oExcel.Application.ScreenUpdating = $fAlerts If $fSave Then $oExcel.ActiveWorkBook.Save EndIf $oExcel.Application.DisplayAlerts = True $oExcel.Application.ScreenUpdating = True $oExcel.Quit Return 1 EndFunc ;==>_ExcelBookClose oÝ÷ جƥ"+zj/yÛa{ib殶­s` b33c¶ôW6VÂäÆ6FöâäF7ÆÆW'G2ÒG'VP b33c¶ôW6VÂäÆ6Föâå67&VVåWFFærÒG'VP Cheers!

Hi guys, This is my small humble contribution to the world... :"> I have written 2 additional functions that will return the number of rows used in a given Excel Worksheet. Please feel free to add it into the ExcelCOM UDF library! ;=============================================================================== ; ; Description: Return the number of rows ibeing used in the specified worksheet. ; Syntax: _ExcelSheetNumUsedRowGet($oExcel, $sSheetName) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sSheetName - The sheet name to be checked. ; Requirement(s): None ; Return Value(s): On Success - Returns the number of rows being used in the specified worksheet. ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; Author(s): Stanley Lim <voided_lim@yahoo.com.sg> ; Note(s): None ; ;=============================================================================== Func _ExcelSheetNumUsedRowGet($oExcel, $iSheetNum) Dim $iRowCount If NOT IsObj($oExcel) Then $iRowCount = SetError(1, 0, 0) Else $oExcel.Application.DisplayAlerts = 0 $oExcel.Application.ScreenUpdating = 0 $iRowCount = $oExcel.ActiveWorkbook.Sheets($iSheetNum).UsedRange.Rows.Count $oExcel.Application.DisplayAlerts = 1 $oExcel.Application.ScreenUpdating = 1 EndIf Return $iRowCount EndFunc;==>_ExcelSheetNumUsedRowGet ;=============================================================================== ; ; Description: Return the number of rows ibeing used in the specified worksheet. ; Syntax: _ExcelSheetNameUsedRowGet($oExcel, $sSheetName) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sSheetName - The sheet name to be checked. ; Requirement(s): None ; Return Value(s): On Success - Returns the number of rows being used in the specified worksheet. ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; Author(s): Stanley Lim <voided_lim@yahoo.com.sg> ; Note(s): None ; ;=============================================================================== Func _ExcelSheetNameUsedRowGet($oExcel, $sSheetName) Dim $iRowCount If NOT IsObj($oExcel) Then $iRowCount = SetError(1, 0, 0) Else $oExcel.Application.DisplayAlerts = 0 $oExcel.Application.ScreenUpdating = 0 $iRowCount = $oExcel.ActiveWorkbook.Sheets($sSheetName).UsedRange.Rows.Count $oExcel.Application.DisplayAlerts = 1 $oExcel.Application.ScreenUpdating = 1 EndIf Return $iRowCount EndFunc;==>_ExcelSheetNameUsedRowGet