Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Question Unanswered: OLE DB and MS Excel

    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;"
    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


  2. #2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts