Open Excel file using ADODB.Connection # [Solved]

[Juvigy]

I have Excel file that i open as a database with ADO.I dont get any errors and most of the fields work.But some fields show no data.

For example "6A" value of the cell is not shown , but "6" or "A" is shown.The "6A"value is empty/NuLL? in the array.Any idea what i am doing wrong?

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 
Global $s_Filename=FileGetShortName("C:\File.xls") 
Global $s_Tablename = "[January$]" 
Global $test [40][7]
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")
$objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 1 Asc" , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 
$i=0
Do 
    $test [$i][0]=$objRecordSet.Fields(1).value
    $test [$i][1]=$objRecordSet.Fields(2).value
    $test [$i][2]=$objRecordSet.Fields(3).value
    $test [$i][3]=$objRecordSet.Fields(4).value
    $test [$i][4]=$objRecordSet.Fields(5).value
    $test [$i][5]=$objRecordSet.Fields(6).value 
    $i=$i+1
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()
_ArrayDisplay($test)

Edited February 8, 2010 by Juvigy

[Juvigy]

bump

[picaxe]

This works for me (see here)

#include <Array.au3>

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename = FileGetShortName(@DesktopDir & "\test.xls")
Global $s_Tablename = "[All Members$]" ;"[January$]"
Global $iRows = 100, $iColumns = 14
Global $aArray[$iRows][$iColumns]

$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & $s_Filename & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";")
$objRecordSet.Open("Select * FROM" & $s_Tablename & "Order by 1 Asc", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

Local $i = 0, $k = UBound($aArray, 2) - 1
Do
    For $j = 0 To $k
        $aArray[$i][$j] = $objRecordSet.Fields($j).Value
    Next
    $i += 1
    If Mod($i, 100) Then ReDim $aArray[$i + 100][$iColumns]
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()
ReDim $aArray[$i][$iColumns]
_ArrayDisplay($aArray)

Edited February 6, 2010 by picaxe

[Juvigy]

I tried your code - had to modify it a little as it was giving me errors on the :

$aArray[$i][$j] = $objRecordSet.Fields($j).Value

line.

But still it doesnt work as expected for me.

The value "6A" now is shown as "6" instead of empty as before.

@Edit

Found a solution.It is described here:

http://support.microsoft.com/kb/194124

I chose not to go with the IMEX=1 workaround an reenter the values as text.

Edited February 8, 2010 by Juvigy