Hi All,

I want to query an excel sheet and treat it as a database. So far, I have been able to connect and query it just fine. However, there seems to be a problem that is hindering my progress - I need to iterate through the excel sheet and add all the numerical values for specific columns. So I wrote code to do this. It works for some rows, but for some rows it can't see any data (when there are actually numbers in this column).

So I experimented, and determined that the columns that the ADO recordset is able to capture have data at the very top rows, while the columns that appear to be 100% blank (they're not actually) don't. I added a number at the top for these columns manually, and it solved the problem. I however, need this to be an automatic process, so I wrote some code to place a random number at the top row in the fields that are blank - it doesen't work as the recordset shows that it is the only populated field. Further, the size of the excel sheet doubles - which I think is associated to the problem.

Does anyone know how to resolve this problem? Better yet, does anyone know of a way to automatically export the excel data into MS Access? Thanks!

Here is my code that I use to iterate through the database:

<%
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Mode = adModeRead
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("Current_Month_Jun.xls") & ";" & "Extended Properties=Excel 8.0;"
objConn.Open
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.CursorType = 3
objRec.CursorLocation = 3
sql_1 = "SELECT * FROM [vwGCSMReporting$]"

' Open the global ADO recordset.
objRec.Open sql_1,objConn

While Not objRec.EOF
Response.Write(objRec("S1Q15"))
Response.Write("<br>")
objRec.MoveNext
Wend

%>

Why don't you use SUM() in your SQL statement? Not sure if it would help, but try using:

SELECT SUM(S1Q15) AS TotalAmt FROM [vwGCSReporting$]

Response.Write objRec("TotalAmt")