_ExcelReadSheetToArray doesn't work with German Excel 2003

[Prog@…]

In _ExcelReadSheetToArray, the last-cell-string is Split by the character 'R' and 'C' for Row and Column. The problem is, Excel localizes this string, e.g. German: 'Z' (Zeile) instead of 'R' and 'S' (Spalte) instead of 'C'. So, the cell coordinates should be rad with a language-independant StringRegExp:

    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    
    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell,"\A[^0-9]*(\d+)[^0-9]*(\d+)\Z",3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

[locodarwin@…]

Changed the code to submitter's suggestion; nothing breaks, and seems to work as suggested. Recommend updating build with the following replacement function:

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArray
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $iStartRow - Row number to start reading, defaults to 1 (first row)
;                  $iStartColumn - Column number to start reading, defaults to 1 (first column)
;                  $iRowCnt - Count of rows to read, defaults to 0 (all)
;                  $iColCnt - Count of columns to read, defaults to 0 (all)
;                  $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.
; Return values .: Success      - Returns a 2D array with the specified cell contents by [$row][$col]
;                  Failure		- Returns 0 and sets @error on errors:
;                  @error=1     - Specified object does not exist
;                  @error=2     - Start parameter out of range
;                     @extended=0 - Row out of range
;                     @extended=1 - Column out of range
;                  @error=3 - Count parameter out of range
;                     @extended=0 - Row count out of range
;                     @extended=1 - Column count out of range
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike (added Column shift parameter to Start Array Column on 0) and PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1].
;                  Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                  cell data starts at [1][1] to match R1C1 numbers.
;                  By default the entire sheet is returned.
;                  If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........;
; Example .......; Yes
; ===============================================================================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = False)
	Local $avRET[1][2] = [[0, 0]] ; 2D return array

	; Test inputs
	If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
	If $iStartRow < 1 Then Return SetError(2, 0, 0)
	If $iStartColumn < 1 Then Return SetError(2, 1, 0)
	If $iRowCnt < 0 Then Return SetError(3, 0, 0)
	If $iColCnt < 0 Then Return SetError(3, 1, 0)

	; Get size of current sheet as R1C1 string
	;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
	Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    
    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell,"\A[^0-9]*(\d+)[^0-9]*(\d+)\Z",3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

	; Check input range is in bounds
	If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
	If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
	If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
	If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

	; Check for defaulted counts
	If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
	If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1

	; Size the return array
	ReDim $avRET[$iRowCnt + 1][$iColCnt + 1]
	$avRET[0][0] = $iRowCnt
	$avRET[0][1] = $iColCnt

	If $iColShift Then ;Added by litlmike
		; Read data to array
		For $r = 1 To $iRowCnt
			For $c = 1 To $iColCnt
				$avRET[$r][$c - 1] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
			Next
		Next
	Else ;Default for $iColShift
		; Read data to array
		For $r = 1 To $iRowCnt
			For $c = 1 To $iColCnt
				$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
			Next
		Next
	EndIf
	;Return data
	Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

[Gary]

Fixed in version: 3.3.1.0

[MrCreatoR <mscreator@…>]

Ironicly this fix caused another bug - the docs sais this:

Remarks: If the sheet is empty [0][0] and [0][1] both = 0.

but it's not quite true, here is an example that shows this:

#include <Array.au3>
#include <Excel.au3>

$oExcel = ObjCreate("Excel.Application")

If Not IsObj($oExcel) Then
    MsgBox(16, "Error: _Exel_Paste", "Couldn't Create Object Excel.Application")
EndIf

$oExcel = $oExcel.WorkBooks.Add
$oExcel.Application.Visible = True

For $xlWindow In $oExcel.Windows
    $xlWindow.Visible = True
Next

$oExcel.Activate
$aLastCell = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($aLastCell, 'Excel')

$oExcel.Saved = True
$oExcel.Close

To fix it there is need to replace in _ExcelReadSheetToArray UDF this:

	; Extract integer last row and col
	$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
	Local $iLastRow = $sLastCell[0]
	Local $iLastColumn = $sLastCell[1]
	
	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

with this:

	; Extract integer last row and col
	Local $aLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
	Local $iLastRow = $aLastCell[0]
	Local $iLastColumn = $aLastCell[1]
	
	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells(1, 1).Value = "" Then Return $avRET

[MrCreatoR <mscreator@…>]

One more fix for another bug when the $sLastCell variable includes double info (not sure why), in this form: R120C30:R120C32.

The replace block is now changed to this (regexp changed, and added @error checking)

; Extract integer last row and col
	Local $aLastCell = StringRegExp($sLastCell, "\A(?:.*:)?[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
	If @error Then Return SetError(4, 0, 0) ;should not happen
	
	Local $iLastRow = $aLastCell[0]
	Local $iLastColumn = $aLastCell[1]
	
	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells(1, 1).Value = "" Then Return $avRET