Difference between revisions of "ADO Example Excel"

From AutoIt Wiki
Jump to navigation Jump to search
m (Example didn't work with $iOptions = 2 (adCmdTable))
m (Read XLS file and display the cells of a range: Example refactoring)
Line 8: Line 8:
 
Global Const $iLockType = 1 ; adLockReadOnly
 
Global Const $iLockType = 1 ; adLockReadOnly
 
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table
 
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table
Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
+
 
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
+
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
 
Global $sFilename = @ScriptDir & "\ADO_Example_Excel.xls"
 
Global $sFilename = @ScriptDir & "\ADO_Example_Excel.xls"
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"'
+
Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"'
$oADOConnection.Open($sADOConnectionString) ; Open the connection
+
$oConnection.Open($sConnectionString) ; Open the connection
Global $sADOSQL = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields
+
 
$oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
+
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
With $oADORecordset
+
Global $sSQL_Query = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields
    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
+
$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
        ConsoleWrite("Process the fields collection:          ")
+
If Not @error Then
        For $oField In .Fields
+
With $oRecordset
            ConsoleWrite($oField.Value & "|")
+
While Not .EOF ; repeat until End-Of-File (EOF) is reached
        Next
+
 
        ConsoleWrite(@CR)
+
; Write the content of all fields to the console separated by | by processing the fields collection
        ; Write a second line by accessing all fields of the collection by name or item number
+
ConsoleWrite("Process the fields collection:          ")
        ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
+
For $oField In .Fields
        .MoveNext ; Move To the Next record
+
ConsoleWrite($oField.Value & "|")
    WEnd
+
Next
EndWith
+
ConsoleWrite(@CR)
$oADORecordset.Close ; Close the recordset
+
 
$oADORecordset = 0 ; Release the recordset object
+
; Write a second line by accessing all fields of the collection by name or item number
$oADOConnection.Close ; Close the connection
+
ConsoleWrite("Process the fields by name/item number: " & .Fields("F1").Value & "|" & .Fields(1).Value & "|" & @CR)
$oADOConnection = 0 ; Release the connection object
+
.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
 
</syntaxhighlight>
 
</syntaxhighlight>
 
Input file: [[Media:ADO_Example_Excel.xls|ADO_Example_Excel.xls]]
 
Input file: [[Media:ADO_Example_Excel.xls|ADO_Example_Excel.xls]]

Revision as of 22:39, 2 March 2016

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

$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|