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

Posted on 2004-10-29
Medium Priority
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
  • 2
LVL 34

Author Comment

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


Accepted Solution

RogueSolutions earned 1500 total points
ID: 12453873

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 34

Author Comment

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



Expert Comment

ID: 14933998
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"


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

578 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