Thread: OLE DB and MS Excel
07-12-04, 18:37 #1Registered User
- Join Date
- Jul 2004
Unanswered: OLE DB and MS Excel
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;"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.CursorType = 3
objRec.CursorLocation = 3
sql_1 = "SELECT * FROM [vwGCSMReporting$]"
' Open the global ADO recordset.
While Not objRec.EOF
07-13-04, 02:00 #2Useless...
- Join Date
- Jul 2003
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")That which does not kill me postpones the inevitable.