#180892 - 2007-09-27 05:36 AM
Problem indexing Variant Arrays
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
|
I'm having an odd problem reading data from Excel. The example below illustrates the problem.
When reading a single cell, it returns an appropriate value. However, when reading a range of cells, it returns a Variant Array. This is where the problem appears. Actually, there are 2 problems...
1. You can look at the UBound of the array, but it isn't "right". If you read one row of cells, the UBound is 1, not 0. If you read a block of cells, UBound defines the number of rows as a 1-based value.
2. You can enumerate the array in a For/Each loop, but you cannot directly address an element of the array.
Both of these problems are illustrated by the code sample below. Both arrays are fully enumerated by For/Each loops, but the last line, which references element 0 of the array will throw an error.
I've tested this with every version since 4.50, including 4.60rc1, and have found the same results. Shawn has also confirmed these findings.
Glenn
Break On
'Kix Version: ' @Kix ?
; Instatiate the Excel object and open the file
; The spreadsheet contains the following simple data:
; A B C D
; E F G H
; I J K L
; M N O P
$Excel = CreateObject("Excel.Application")
$Workbook = $Excel.Workbooks.Open("c:\temp\test.xls")
'Single Cell:' ?
$Value = $Workbook.ActiveSheet.Range("B2").Value
'Elements: ' UBound($Value) ?
'Vartype=' VarTypeName($Value) ?
$Value ?
?
'Row of 4 Cells:' ?
$Value = $Workbook.ActiveSheet.Range("A1:D1").Value
'Elements: ' UBound($Value) ?
'Vartype=' VarTypeName($Value) ?
For Each $ in $Value
$ ?
Next
?
'Block of 4x4 Cells:' ?
$Value = $Workbook.ActiveSheet.Range("A1:D4").Value
; Close Excel now, since the last command will cause Kix to fail
; which will leave Excel running
$Excel.Quit
$Excel = 0
'Elements: ' UBound($Value) ?
'Vartype=' VarTypeName($Value) ?
For Each $ in $Value
$ ?
Next
$Value[0] ?
Exit 0
_________________________
Actually I am a Rocket Scientist! 
|
|
Top
|
|
|
|
Moderator: ShaneEP, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Ruud van Velsen, Mart
|
0 registered
and 1447 anonymous users online.
|
|
|
