update spreadsheet using ADO and Excel in VB6

Try using the following instead of using while loop:
cn.execute "insert into [looms$C1:C20] SELECT 66 FROM [looms$C1:C20] WHERE Field3 = 10"

I get "no destination field name in insert into statement (66)"

I am using the loop because I planned to modify it to find the last value of 10 in the spreadsheet and insertng a row after it.  Maybe I need to modify my select statement instead as well?.

You're field header name should be Field1.

As long as you don't use header names, the columns in your selection are simply enumerated. As you have only selected one column in your source statement, thus it must be Field1.

btw, you can simply access it by the column index, e.g. rs.Fields(0).Value = "..".


mfG
--> stefan <--

Try using following loop to see if the column exists:
For X = 0 To rc.Fields.Count - 1
Response.Write(rc.Fields.Item(X).Name & "<BR>")
Next

Thanks.

Now I get the following on the the update line:
Query cannot be updated because it containes no searchable columns to use as a key

Dim sfile As String
    Dim strQuery As String
    Dim rc As ADODB.Recordset
    Dim cn As ADODB.Connection
    sfile = "C:\Book1.xls"
    Set cn = New ADODB.Connection
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=T:\Production Control Fabric and Yarn\Scheduling\AFGHAN SCHEDULE (Current)\denise_AFGSCH070610.xls; ReadOnly=False;"
        .Open
    End With

strQuery = "Select * from [looms$C1:C20]"

    Set rc = New ADODB.Recordset
    rc.Open strQuery, cn, adOpenStatic, adLockOptimistic
    MsgBox rc.RecordCount
    MsgBox rc.Fields.Count

    While Not rc.EOF()
        If rc.Fields(0).Value = 10 Then
            rc.AddNew
            'rc("Field1") = 66
            rc.Fields(0).Value = 66
            rc.Update
        End If
        rc.MoveNext
    Wend
cn.Close
Set rc = Nothing

Select allOpen in new window

I didnt' abandon this question.  The experts abanonded me.   :(

hmm, I guess my original question was answered.   But the problem is not solved.  I am trying to "update spreadsheet using ADO and Excel in VB6.  I'll take a look at links.  Thanks.