Getting Rowcount from Excel Database

Keywords: 	 rowcount

---

Question:

I'm writing a WinBatch script to search for an item in several EXCEL speadsheets without having to open them. How can I know their size ( # of columns and Rows), and do do we have any way to search for a value besides:

 
cellval = DDERequest (channel2, "R3C2")
Check the value and then change the cell number to get an another one.

Thank for any suggestion.

Answer:

If the spreadsheets have databases as assigned 'ranges', you can use OLE and read the Rows in as a RecordSet, then use RowCount(), and perform a simple loop to get the Column Names.

;illustrates getting rowcount from Excel Database
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=e:\wbdemo\test.xls; Extended Properties=Excel 5.0;"

DB  = ObjectOpen("ADODB.Connection")

DB.Open(cConn)

RS  = DB.Execute("SELECT COUNT(*) FROM RAO AS CNT")

RS.MoveFirst()  ;may not even be necessary
fld = RS.Fields(0)
v   = fld.Value

message("Number of Rows, Excluding Headings",v-1)

DB.Close()
ObjectClose(DB)
exit

---

Article ID:   W14697

Filename:   Getting Rowcount from Excel Database.txt