Update Excel data via SQL UPDATE
excel, sql, vba
Solution
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
Problem
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.