Database Search

TechHome

• MicroSoft Jet OLEDB Sort Numbers in a Text Field
• MySql Example
• udfSQLExecute GetString
• use ADO to Count Data in Excel

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Use ADO to Count Data in Excel

 Keywords:  ADO Count Data Excel XLS 

; use ADO to count data in Excel
wrksht = 'Sheet1'
colname = 'City'
lookfor = 'New York'
cXLS  = 'D:\temp\Data\Book1.xls'
If ! FileExist( cXLS ) Then Exit

adStateOpen=1
adOpenForwardOnly=0
adLockReadOnly=1
adCmdText=1

objConnection = CreateObject("ADODB.Connection")
objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" : cXLS : ";Extended Properties=Excel 8.0;"

sqlstr = 'SELECT COUNT(*) AS resultat FROM [':wrksht:'$] WHERE [':colname:']="':lookfor:'"'
objRecordSet.Open(sqlstr, objConnection, adOpenForwardOnly, adLockReadOnly)

result = objRecordSet.fields("resultat").value
Pause('Result',result)

objRecordSet.Close
objConnection.Close
objConnection = 0
objRecordSet = 0
Exit

Article ID:   W18068

Filename:   use ADO to Count Data in Excel .txt

File Created: 2011:01:06:11:18:36

Last Updated: 2011:01:06:11:18:36