[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

649 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