ADO Example Excel
Jump to navigation
Jump to search
Read XLS file and display the cells of a range
The following example
- Reads the cells of a range of an XLS file and displays two columns of each row
- The Excel worksheet has no header row
Example AutoIt script:
Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $sFilename = @ScriptDir & "\ADO_Example_Excel.xls"
Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"'
$oConnection.Open($sConnectionString) ; Open the connection
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields of the selected range A1:B2
$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
If Not @error Then
With $oRecordset
While Not .EOF ; repeat until End-Of-File (EOF) is reached
; Write the content of all fields to the console separated by | by processing the fields collection
ConsoleWrite("Process the fields collection: ")
For $oField In .Fields
ConsoleWrite($oField.Value & "|")
Next
ConsoleWrite(@CR)
; Write a second line by accessing all fields of the collection by name or item number
ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
.MoveNext ; Move To the Next record
WEnd
EndWith
; Clean Up Recordset
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the recordset object
EndIf
; Clean Up Connection
$oConnection.Close ; Close the connection
$oConnection = 0 ; Release the connection object
Input file: ADO_Example_Excel.xls
Result:
Process the fields collection: F1|F2| Process the fields by name/item number: F1|F2| Process the fields collection: F1.1|F2.1| Process the fields by name/item number: F1.1|F2.1|