Help to write in closed xls file using ADODB... vb6

With the help from some good experts I have follwing code to read from a closed excel file.  Now, I need to write data in that excel file (book1.xls) as well.  I am using:

Sub chkBox1_Click()
   ' in book1.xls I have SHEET1 and few coulumn headers wirh ShowYN amongst them
   cn.Execute "Update [SHEET1$] Set ShowYN = " & Me!chkBox1    '<-- when this code is being executed,
                                                                                                '      cn is not nothing
End Sub

'--in a module I have:
Public cn As ADODB.Connection
Public rs As Object 'ADODB.Recordset   ',-- this works when I am reading from book1.xls
Code that works fine when I am reading data from book1.xls:

Set cn = New ADODB.Connection
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & "DBQ=" & "c:\book1.xls" & ";"
Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM [SHEET1$]", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    While Not rs.EOF
        MsgBox rs(0)
Set rs = Nothing
Set cn = Nothing
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
RogueSolutionsConnect With a Mentor Commented:

Have never tried updating an Excel file via ADO but one thing that struck me regarding your Update command is that it doesn't specify a record )no WHERE clause) - are you intending to update the whole column to whatever value Me!chkBoox1 is?

Remember in VB checkboxes don't equate to True / False like they do in VBA - you have vbChecked, vbUnchecked and vbGreyed (not sure of the last one)

Don't see why you get a corrupted file though - in theory it should work

You might be better to add the Microsoft Excel reference to your project and use more standard methods to open and update your workbook.

If you are writing the file via ADO and someone else opens it it's bound to corrupt the file.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
FYI: When I use this code:

Sub chkBox1_Click()
      cn.Execute "Update [SHEET1$] Set ShowYN = " & Me!chkBox1    
End Sub

it curropts book1.xls; I no longer can open it again.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you for your contribution.  I gave up using xls.  I decided to use txt file instead as some of good experts suggested.


When I run following query in VB 6, the record in the table meeting criteria 'lockv is null', all the records are updated with value of first record only. Please suggest me what is wrong with the code.

cn.Execute "update tab2 set valu =" & (rs.Fields("pric") + (rs.Fields("pric") * v3)) & " where lockv is NULL"

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.