drapdv Posted May 4, 2011 Share Posted May 4, 2011 Ran a time trial of this vs. the original on a 10,000 row, 10 column worksheet. Time on the original was 292.087, time on this one was 58.125 (and I put a progress bar on this one). It uses _ExcelSheetUsedRangeGet which was itself slightly modified so that you do not have to specify a sheet name (although you still can if you want), in which case it will just look at the active sheet. I did not put any real error-checking on this, and all credit for _ExcelSheetUsedRangeGet goes to the original authors. Hope this helps some people who have larger Excel sheets to work through. An important note: Other Excel-to-array functions request the number of rows and columns as the 4th and 5th variables. On this one, don't put the count, just put the last row or column you want to have pulled into the array. expandcollapse popupFunc _ExcelReadSheetToArrayFast($oExcel, $firstRow=1, $firstCol=1, $lastRow=0, $lastCol=0, $vSheet="") $usedRange = _ExcelSheetUsedRangeGet($oExcel, $vSheet) If IsArray($usedRange) Then If $lastCol = 0 Then $lastCol = $usedRange[2] If $lastRow = 0 Then $lastRow = $usedRange[3] Else Return 0 EndIf Dim $aFixedArray[$lastRow - $firstRow + 2][$lastCol - $firstCol + 2] ProgressOn(" Progress", " Reading Excel Sheet...") For $i = $firstRow To $lastRow ProgressSet($i/($lastRow/100), "Running "&$i&" of "&$lastRow - $firstRow) $aTempArray = $oExcel.Activesheet.Range($oExcel.Cells($i, $firstCol), $oExcel.Cells($i, $lastCol)).Value $b = $i - $firstRow + 1 For $a = 0 To ($lastCol - $firstCol) $c = $a + 1 $aFixedArray[$b][$c] = $aTempArray[$a][0] Next Next ProgressOff() Return $aFixedArray EndFunc ;==>_ExcelReadSheetToArrayFast ;=============================================================================== ; ; 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, MRDev, 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 Not $vSheet = "" Then If IsNumber($vSheet) Then If $oExcel.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.Sheets($vSheet).Select Else $oExcel.ActiveSheet.Select EndIf $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 = StringRegExp($aSendBack[1], "[RZ]([^CS]*)[CS](.*)",3) $aSendBack[2] = Number($sTemp[1]) $aSendBack[3] = Number($sTemp[0]) If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0 Return $aSendBack EndFunc ;==>_ExcelSheetUsedRangeGet Link to comment Share on other sites More sharing options...
Mat Posted May 4, 2011 Share Posted May 4, 2011 Have you seen as well? It looks at first glance like the solutions are similar (UsedRange comes up in the other one too). AutoIt Project Listing Link to comment Share on other sites More sharing options...
drapdv Posted May 5, 2011 Author Share Posted May 5, 2011 Have you seen as well? It looks at first glance like the solutions are similar (UsedRange comes up in the other one too).Hi Mat,No, I hadn't seen it, and no, the functions are actually pretty different. And to be honest, his is A LOT faster. Like <1s vs 53s, but it was not really written as a function. So I revamped it, and hopefully people can use it in place of _ExcelSheetReadToArray.There are some times when you get a data mismatch error with that other function, and it can't be obfuscated, but it is, in most instances, infinitely better than anything else out there.Thank you for pointing it out to me, I use this all the time and that just saved me a ton of time!David Link to comment Share on other sites More sharing options...
sdynk02 Posted September 15, 2016 Share Posted September 15, 2016 (edited) Hello Drapdv, 1st of all, thanks for your Improvement code, but can you give the example about your important note. i've tried many ways to call it but all failed. Hope to see your reply soon. By the way, i have still stucked at the error : The requested action with this object has failed.: Thanks Edited September 15, 2016 by sdynk02 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now