vba 通过 SQL UPDATE 更新 Excel 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21854312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Excel data via SQL UPDATE
提问by xificurC
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?
我想通过 SQL 查询更新 Excel 工作表的数据。我知道您可以通过 ADODB.Connection '连接'到工作表，并在 ADODB.Recordset 中从中检索（SELECT）数据。但是，对 UPDATE 查询使用相同的过程会产生“操作必须使用可更新查询”错误。有没有其他方法可以实现这一目标？
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.
该代码期望在保存的 .xls 工作表中，其中 Sheet1 包括一个带有列标题（至少）a 和 b 的表格。
回答by marcw
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=Falseto your Connection String.
回答
Excel 文件的连接默认设置为只读。
您必须添加ReadOnly=False到您的连接字符串。
More information can be found at Microsoft Support
可以在Microsoft 支持中找到更多信息
