I want to update the data of my Excel sheet through an SQL query. I know that you can 'connect' in a sheet through ADODB. Connection and ADODB Retrieve (select) data from it in Rescoset. Although using the same process for an UPDATE query 'Operation should use an updateable query' error there is no other way to achieve this goal.
An example code Is that error generates:
sub-SQLUpdateExample () set as ADODB.Connection dim RS ADODB.Recordset as a stealer con = new ADODB.Connection con.Open "driver = {Microsoft Excel driver (* .xls)}; " & Amp; _ "Driverid = 790;" & Amp; _ "DBQ =" & amp; ThisWorkbook.FullName & amp; "," & Amp; _ "DefaultDir =" & amp; Set thisWorkbook.FullName = RA = new ADODB.Recordset set RS = con.Execute ("Update [Sheet1 $] set one = 10 ou p> 2") Set RS = nothing set thief = nothing End Sub The code is expected to be in a saved .xls workbook where the sheet contains 1 column header (at least) A and B along with a table.
It was not thought that all this would be done ...
Answer
Connection to Excel files has been set by default to read only.
You must add ReadOnly = False to your connection string.
More information can be found on
Comments
Post a Comment