Updating an Excel spreadsheet using ADO

Results 1 to 3 of 3

Thread: Updating an Excel spreadsheet using ADO

  1. #1
    Keith Baker Guest

    Default Updating an Excel spreadsheet using ADO

    I am successfully accessing an Excel Spreadsheet in VBS and retreiving the data contained in the cells by doing the following:<BR><BR>Set cnnExcel = CreateObject("ADODB.Connection")<BR>cnnExcel.Open "DBQ="& SourceFile &";DRIVER={Microsoft Excel Driver (*.xls)}"<BR>Set rstExcel = CreateObject("ADODB.Recordset")<BR>rstExcel.Open "SELECT * FROM [Sheet1$];", cnnExcel<BR><BR>I am then using rstExcel("Columnname") to access the various cells I need values from and rstExcel.MoveNext to loop through the rows until I reach the end of the file using rstExcel.EOF<BR><BR>I now have a requirement to update some of the cells. Is it possible to use SQL UPDATE, DELETE and INSERT with the Microsoft Excel Driver. If it is have you a VBS example of doing this, or if this method can not be used, is there another way of achieving this.<BR><BR>Keith Baker<BR>k.baker1@ntlworld.com

  2. #2
    Join Date
    Dec 1969

    Default RE: Updating an Excel spreadsheet using ADO

    The url on how to do this is in yesterday&#039s asp q&a. There are quite a few restrictions and you are cautioned about doing this with multiple users as Excel is not equipped to handle more than one at a time.

  3. #3
    Keith Baker Guest

    Default RE: Updating an Excel spreadsheet using ADO

    I am assuming the referance is to Eddie Campbell&#039s reponse to the question "How can I save a recordset to an Excel Spreadsheet". The URL talks about using response.ContentType = "application/vnd.ms-excel to write data to a new Excel file.<BR><BR>I have a different situation. I have an existing spreadsheet open with a Select and the the pointer on a record. I am checking information for uniqueness via an external application. If not unique, I am suffixing the value with 1 and rechecking, increment and recheck until unique. I then want to update the value in the spreadsheet to be the new unique value and do a MoveNext to repeat the process on the next row. <BR><BR>As I already have the file open with the pointer at the correct row, it would be nice to use SQL to do the update if possible as the method sugested in the URL will involve a lot of extra work.<BR><BR>TIA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts