Jump to content
Sign in to follow this  

Improvement to _ExcelReadSheetToArray

Recommended Posts

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.

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]
        Return 0

    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]

    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)
            $aSheetList = _ExcelSheetList($oExcel)
            For $xx = 1 To $aSheetList[0]
                If $aSheetList[$xx] = $vSheet Then $fFound = 1
            If NOT $fFound Then Return SetError(3, 0, 0)

    $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

Share this post

Link to post
Share on other sites

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!


Share this post

Link to post
Share on other sites

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.:


Edited by sdynk02

Share this post

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...