Improvement to _ExcelReadSheetToArray

[drapdv]

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 popup

Func _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

[Mat]

Have you seen as well? It looks at first glance like the solutions are similar (UsedRange comes up in the other one too).

[drapdv]

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

[sdynk02]

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