ADO Recordset problem with EXCEL 2011

[Broadcastic]

I'm trying to do fast fill via ADO into excel file, for 2 columns and 30 rows.

for some reason I get <'> in front of my dates in 1st column and nothing in second

What am I doing wrong?

expandcollapse popup

#Include <Date.au3>
#Include <Array.au3>

Const $adSchemaTables = 20
Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 ;  =&H0001
Global $s_Filename=FileGetShortName("TEMPLATE2011.xls");
Global $s_Tablename = "[Sheet1$]"

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$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;"";")
;          "Extended Properties=""Excel 8.0;HDR=Yes;"";")



$objRecordSet.Open ("Select F1,F2 FROM [Sheet1$A1:B30]", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)
$ArrRows=$objRecordSet.GetRows();

;$objRecordSet.MoveFirst
For $i=1 to 30
    $F1=$objRecordSet.Fields(0)
    $F2=$objRecordSet.Fields(1)
    $F1.value=_DateAdd( 'd',$i, _NowCalcDate())
    $F2.value.AsInteger=int(round(Random(1,17)))
    $objRecordSet.MoveNext
Next;





$objConnection.Close
$objConnection = ""
$objRecordSet = ""

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  ConsoleWrite("We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

Errors I get

err.description is: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 31

err.source is: ADODB.Field

err.helpfile is: C:\WINDOWS\HELP\ADO270.CHM

err.helpcontext is: 1240645We intercepted a COM Error !

err.description is: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 32

err.source is: ADODB.Field

err.helpfile is: C:\WINDOWS\HELP\ADO270.CHM

err.helpcontext is: 1240645We intercepted a COM Error !

err.description is: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 33

err.source is: ADODB.Recordset

err.helpfile is: C:\WINDOWS\HELP\ADO270.CHM

err.helpcontext is: 1240645+>18:08:28 AutoIT3.exe ended.rc:0

[Broadcastic]

got workaround done, same ADO, only via CSV/text

...
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&@ScriptDir&";" & _
            "Extended Properties=""text;HDR=No;FMT=CSVDelimited""")
           

$objRecordSet.Open (StringFormat("Select * FROM %s",$s_Filename), $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)
$oSheet.Cells(1,1).CopyFromRecordset($objRecordSet)
...

it takes 5 sec vs. 2 minutes using _ExcelWriteArray

That's an improvement!

btw is there a SQLite ODBC driver? so I can pump directly

[Zedna]

Here is very detailed description of Excel x ADO in Delphi

Accessing and managing MS Excel sheets with Delphi

http://delphi.about.com/od/database/l/aa090903a.htm