How to SQL Query on Excel file irrespective of the version of MS Office?

I am building an application in VBSCRIPT the requirement of my application is to filter the data saved in an excel file and obtain the total record count and sum of a particular column. the excel file which i take as input is programmaticaly generated by another script. but when i run my script i get an error saying

" [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.

Line (35): "objAdRs.Open strSQLStatement, objAdCon, 1, 3". "

the requirement of my application is that it should be robust enough to accept input from excel writen in any format or version. i used following driver statement.

Set objAdCon = CreateObject("ADODB.Connection")
objAdCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"

and handle recordset by this lines

Set objAdRs = CreateObject("ADODB.Recordset")
objAdRs.CursorLocation=3
objAdRs.Open strSQLStatement, objAdCon, 1, 3

it works fine if i save as the file on my machine first..

and ya the application which creates the file uses foll statement to programmatic开发者_StackOverflow社区ally save and create the workbook. save :

objExcelMain.ActiveWorkbook.Save

to create :

set objOutputWorkbook = objOutputExcel.WorkBooks.Add
set objOutputWorksheets = objOutputWorkbook.WorkSheets

---

To address more than one type of data source, you should use a data abstraction layer, like Hibernate, wchich is available for .NET, by the way.

Here are the supported formats/databases http://community.jboss.org/wiki/SupportedDatabases Several versions of Excel are in it.

Should a new version of Excel be released, then you only have to find (write?) a new driver and plug it into Hibernate, instead of re-writing the client code to your data abstraction layer. By the same token, should you decide to store your data in another type of database, then you only need to change your configuration instead of re-writing a whole bunch of code.

Here's the NHibernate site: http://community.jboss.org/wiki/NHibernateforNET

Here's a tutorial for VS: http://community.jboss.org/wiki/usingnhibernatewithvisualstudionet

It may take some time to learn to work with NHibernate (if you haven't worked with it before), but it definitely pays off. It makes you help to do things the right way first,

Greetz, J.