Update Excel data via SQL UPDATE

Question

I'd like to update my Excel sheet's data via an SQL query. I know you can 'connect' to a sheet via ADODB.Connection and retrieve (SELECT) data from it in a ADODB.Recordset. However using the same process for an UPDATE query produces an 'Operation must use an updateable query' error. Is there any other way to achieve this?

An example code that produces the error:

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$] SET a = 10 WHERE b > 2")

    Set rs = Nothing
    Set con = Nothing
End Sub

The code expects to be in a saved .xls worksheet where Sheet1 includes a table with column headers (at least) a and b.

afaik Excel files are opened as read only. You would have to add ReadOnly=False; to your Connection String. Maybe you will find something of value using this link — marcw
@marcw - thanks mate, that was rather simple but I couldn't find it on the web. If you post your comment as an answer I will accept it. — xificurC

marcw marcw · Accepted Answer · 2014-02-18T13:49:21

Didn't think that would be all to make it work...

Answer
Connections to Excel files are set to ReadOnly as default.
You have to add ReadOnly=False to your Connection String.

More information can be found at Microsoft Support

Update Excel data via SQL UPDATE

1 Answers