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

Posted on 2004-10-29
Last Modified: 2012-06-27
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
Question by:Mike Eghtebas
    LVL 33

    Author Comment

    by:Mike Eghtebas
    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.

    LVL 5

    Accepted Solution


    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.
    LVL 33

    Author Comment

    by:Mike Eghtebas
    Thank you for your contribution.  I gave up using xls.  I decided to use txt file instead as some of good experts suggested.


    LVL 1

    Expert Comment

    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"


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now