ADO Example Excel - Revision history

Water: Enhanced comment

2016-03-28T08:54:19Z

Enhanced comment

← Older revision		Revision as of 08:54, 28 March 2016
Line 15:		Line 15:

	Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object		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		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		$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

MLipok: /* Read XLS file and display the cells of a range */ Example refactoring

2016-03-02T22:39:53Z

Read XLS file and display the cells of a range: Example refactoring

← Older revision		Revision as of 22:39, 2 March 2016
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]]

Rt01: Example didn't work with $iOptions = 2 (adCmdTable)

2015-08-15T12:40:37Z

Example didn't work with $iOptions = 2 (adCmdTable)

← Older revision		Revision as of 12:40, 15 August 2015
Line 7:		Line 7:
	Global Const $iCursorType = 0 ; adOpenForwardOnly		Global Const $iCursorType = 0 ; adOpenForwardOnly
	Global Const $iLockType = 1 ; adLockReadOnly		Global Const $iLockType = 1 ; adLockReadOnly
	Global Const $iOptions = 2 ; ~~adCmdTable~~		Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table
	Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object		Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object

Mlipok at 21:53, 11 February 2014

2014-02-11T21:53:35Z

← Older revision		Revision as of 21:53, 11 February 2014
Line 29:		Line 29:
	EndWith		EndWith
	$oADORecordset.Close ; Close the recordset		$oADORecordset.Close ; Close the recordset
	$oADORecordset = 0 ; Release the ~~connection~~ object		$oADORecordset = 0 ; Release the recordset object
	$oADOConnection.Close ; Close the connection		$oADOConnection.Close ; Close the connection
	$oADOConnection = 0 ; Release the connection object		$oADOConnection = 0 ; Release the connection object

Rt01 at 11:43, 12 January 2014

2014-01-12T11:43:59Z

← Older revision		Revision as of 11:43, 12 January 2014
Line 5:		Line 5:
	Example AutoIt script:		Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
			Global Const $iCursorType = 0 ; adOpenForwardOnly
			Global Const $iLockType = 1 ; adLockReadOnly
			Global Const $iOptions = 2 ; adCmdTable
	Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object		Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Line 11:		Line 14:
	$oADOConnection.Open($sADOConnectionString) ; Open the connection		$oADOConnection.Open($sADOConnectionString) ; Open the connection
	Global $sADOSQL = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields		Global $sADOSQL = "Select F1,F2 FROM [Sheet1$A1:B2]" ; Select all records and all fields
	$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query		$oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
	With $oADORecordset		With $oADORecordset
	While Not .EOF ; repeat until End-Of-File (EOF) is reached		While Not .EOF ; repeat until End-Of-File (EOF) is reached

Rt01: /* Read XLS file and display the cells of a range */

2014-01-10T16:07:56Z

Read XLS file and display the cells of a range

← Older revision		Revision as of 16:07, 10 January 2014
Line 30:		Line 30:
	$oADOConnection = 0 ; Release the connection object		$oADOConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file [[~~File~~:ADO_Example_Excel.xls]]		Input file: [[Media:ADO_Example_Excel.xls\|ADO_Example_Excel.xls]]

	Result:		Result:

Rt01: /* Read XLS file and display the cells of a range */

2014-01-10T15:57:15Z

Read XLS file and display the cells of a range

← Older revision		Revision as of 15:57, 10 January 2014
Line 30:		Line 30:
	$oADOConnection = 0 ; Release the connection object		$oADOConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file "File:~~ADO_Example_Excel.xls":~~		Input file [[File:ADO_Example_Excel.xls]]
	[[ADO_Example_Excel.xls]]

	Result:		Result:

Rt01 at 10:33, 9 January 2014

2014-01-09T10:33:21Z

← Older revision		Revision as of 10:33, 9 January 2014
Line 38:		Line 38:
	Process the fields collection: F1.1\|F2.1\|		Process the fields collection: F1.1\|F2.1\|
	Process the fields by name/item number: F1.1\|F2.1\|		Process the fields by name/item number: F1.1\|F2.1\|

			[[Category:ADO]]

Rt01 at 11:49, 8 January 2014

2014-01-08T11:49:32Z

← Older revision		Revision as of 11:49, 8 January 2014
Line 31:		Line 31:
	</syntaxhighlight>		</syntaxhighlight>
	Input file "File:ADO_Example_Excel.xls":		Input file "File:ADO_Example_Excel.xls":
	[[~~File:~~ADO_Example_Excel.xls]]		[[ADO_Example_Excel.xls]]

	Result:		Result:

Rt01 at 11:46, 8 January 2014

2014-01-08T11:46:45Z

← Older revision		Revision as of 11:46, 8 January 2014
Line 30:		Line 30:
	$oADOConnection = 0 ; Release the connection object		$oADOConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file "ADO_Example_Excel.xls":		Input file "File:ADO_Example_Excel.xls":
	[[File:ADO_Example_Excel.xls]]		[[File:ADO_Example_Excel.xls]]